What is a Custom Hierarchy?

A Custom Hierarchy is a table created to assign custom attributes to existing stores, products, planograms, styles, or suppliers. For instance, if you need to run a report on all stores for certain sales representatives, you can create a custom hierarchy to easily associate a store location to a sales representative's name with a column of store numbers and a column of names. You can also use custom hierarchies to give new attributes to products; for instance, if you have clothing lines which you want to categorize as 'Spring Collection' or 'Vintage Wear', or certain suppliers you want to identify by the names of their primary contacts, rather than supplier IDs. You can then use your custom hierarchy in the CIP report filters, and to summarize and display the report according to your needs.

Create a Custom Hierarchy

1. Download and install the 1010data Excel Add-In: https://www.1010data.com/support/technical-interfaces/excel-add-in/

2. Download the Custom Hierarchy Upload Template

    You can do this by opening any CIP report. For more information, see: How do I open reports?

    Under Product Filter, select Custom Product Hierarchy. You will see a link to Download Custom Hierarchy Template - Click the link to download the Excel file

3. Open the Custom Hierarchy Template, navigate to Add-Ins tab > 1010data > 1010data Login

4. Enter your login details and click Secure Login

5. Once logged in, press Ctrl+Q to update the path for your new table. You receive a notification that the query has completed successfully

6. Select the Hierarchy Type in field B11 which best suits the table you wish to create. The available hierarchy types are based on your company and account level

When you have chosen a hierarchy type, the first field under Column Names will automatically populate, showing the column which will link your table to an existing column in one of the ‘Master’ tables, e.g.: Store Master or Product Master. This 'lookup' column cannot be changed

7. Input your table information (rows 2-8), starting with Table Title. You can hover your mouse over any cell with a red corner arrow for an explanation

Note: Switch to the 1010u Sample Hierarchy worksheet for example data

In this example, a table is created for Sales Reps

 

  • Table Title: The “friendly name” of the table
  • Table Name: The name in the path to the table; it cannot contain any spaces or special characters and should start with a lowercase character (you can use an underscore _ as a space, as in the example below)
  • Short Description: Text which will show up next to the table title for more information
  • Permissions: Any other 1010data user IDs which will have access to this table, separated by commas (no spaces)
  •     By default, the ID you've used to log into the Excel Add-In will already have access
  • Replace or Append? If the table already exists, Replace will replace any of the data with the data you enter below. Append will add your new data to the end of the table. You can leave it blank if you are uploading a new Hierarchy Upload Sheet (it will fail if it finds an existing table)
  • Merge? Can be left blank

8. Start creating your own columns and filling in data

  • Column Names: The names of the columns as referenced by 1010data
    • As with Table Name above, column names must not contain any special characters or spaces
    • Note: The first column name should already be populated, so this must not be changed
  • Column Types: The data type the column contains
    • a: Alphanumeric, column data is treated as text (when in doubt, use this option) 
    • i: Integer, this column will always be treated like a number
  • Column Formats: (Optional) Changes the format by which the data is displayed
    • Examples:
    • type:date shows integer column type in date format
    • type:currency shows integer column type with currency symbol

     A list of display formats can be found in the 1010 Documentation Center: (Link opens in new tab)

  • Column Headings: Display name of the column, can contain spaces and special characters
  • Table Data: Your custom data

In the example above, the sales_reps table has been populated with the first column corresponding to the loc_idnt (FRC) based on the sales representative’s location

9. When you’ve double-checked all of your data and are ready to upload, select 1010data > Upload Data > Upload Active U-Sheet

The results of the upload will show in cell B9 and display the path to the new table

Use a Custom Hierarchy

1. Log into your 1010data account via web browser (you may have to end your existing session if you are still logged in via the Excel Add-In)

2. Open a report and select the Store Filter (or Product Filter, if your custom hierarchy is specific to products)

3. Select Custom Store Hierarchy

4. From the Select Table dropdown, select your new custom hierarchy

You can now use Column/Value Selection to filter selections based on the columns in your custom hierarchy. Your custom columns will be appended by (C)

The new attributes are also available under Summarization