Object Configuration

The ‘Objects’ tab for a selected table will display a summary of the columns within it.

Clicking the ‘AZ’ button, at the top-right, will sort the objects alphabetically which can be useful for quickly locating the one you want. When a table has been sorted, the sort will be saved with the data connection.

For each column we are only seeing a summary of the settings, however clicking on the ‘Show Details’ icon (below) will show the full configuration options for the column you have chosen.

(Object) Name: - the name you have assigned to this object, which must be unique within this data connection.

Object Type: - all objects must be either Measures or Dimensions. As we have seen, charts usually require a Measure and a Dimension; the thing you are measuring (e.g., Sales Value or Number of Orders) and the thing you want to split it by (Account Manager or Month for example), which we call a Dimension. If in doubt; if there is no mathematical calculation (Sum, Average, Max, Min etc.) required then it is a Dimension. However, a number can also be a Dimension if you don’t want to perform a mathematical function on it.

Data Type: Can be Number, Character or Date. See also ‘Number Options’ below.

Cumulation: Only applies to Measures. The default is ‘None’.

If you choose Cumulative, then this Measure will show the Cumulative Sum of the Measure at each Dimension Point. If you wanted to show Cumulative Sales v Cumulative Target for example, then the object would need to be set to cumulative.

If you choose De-cumulative then this Measure will show the De-Cumulative Sum of the Measure at each Dimension Point.

Select: This is the SQL that will be used to retrieve this object. Note that it is using the table (view) alias nr_sales as we saw on the table summary. This can be very simple e.g., one database column or more complicated. However, pretty much any SQL that would be valid in a ‘SELECT’ clause against this table can be entered here.

Test Button: Pressing this will test the object and bring back a sample of the data if the SQL is valid. In the following example we can see for month 1 there are 14617 rows of data and for month 2, 12533 etc.

Switching to the ‘SQL’ tab will show the SQL executed to retrieve this sample:

If you’ve added formatting options, such as a thousand separator, prefix or suffix, to a data object the relevant formatting will be displayed when you test the object using the ‘Display Sample data’ icon.

Order By: Optional. This field is particularly useful when dealing with Month names, by default they will be sorted alphabetically so April will always appear first. The Order By field allows you to specify that they should be sorted chronologically.

Where: Optional. This field is used to specify a global filter that will always be applied to every chart or table where this object has been used. As mentioned previously, you can also filter at chart level but if you needed a filter to apply to all charts/ tables that use this object you would need to specify the filter on every chart/table. If, however, you specified the filter in the ‘Where’ clause it would automatically apply whenever the object was used in a chart or table.

Index Field: Optional. This field is used to pass back to the database the ID for your indexed field of the object itself. This is very useful for date fields, the date field might have been formatted to look a certain way e.g., 14^th Dec 2020, but you need to pass back the raw date so that it uses an index. Another example might be where you had a category object using a ‘Customer’ object and you selected ‘Acme Inc’ (which had a SLCustomerAccountID of 57) then the SQL generated by the dashboard would be:

[SLCustomerAccount].[SLCustomerAccountID] = 57

rather than:

[SLCustomerAccount].[CustomerAccountName] = ‘Acme Inc’

This would give a potentially significant performance advantage if SLCustomerAccountID is indexed, but CustomerAccountName isn’t.

This ‘swap’ only occurs on category objects.

Treat index field as SQL: Early vector databases could deliver a performance benefit using hash indexes and this would enable that functionality to be accessed. Rarely required.

Index Field Type: This determines how the index defined in ‘Index Field’ is formatted, for example enclosing in quotes if it is a date type.

Category: The category this object is owned by. By default, this will be ‘Home’ which is usually fine. However, you may, for example, have a non-display category called ‘HR’, if this object was ‘Salary’ and was owned by the ‘HR’ category then only people who had been allocated ‘HR’ would be able to use this object.

Label: This is how the object will be displayed in any charts/tables. If this field is not populated, then ‘Object Name’ will be used instead. We can see therefore that it wouldn’t be unusual for the database object to be called, for example, ‘UserField5’, the piDashboard Object Name might be ‘Order Header Account Manager’ and the label might be ‘Account Manager’.

Help Text: When text is entered here, it will be visible when users hover over an object on the Data screen in the Edit Chart menu.

Can Use In Temp Filters: If you don’t wish this object to be used in Chart Filters then unlick this option. You might do this if the field wasn’t indexed and the table was particularly large for example. If this box is unticked, the related object will not appear in the Temp Filters section of the dashboard and can therefore not be selected as a chart filter. Please refer to the section ‘Temporary filters’ in Chart Filters for more information.

Can Use in Temp Filter List of Values: This will enable users to specify whether the list of values is displayed as a drop down list in Filter areas of the dashboard. If this option is unticked, the list of values for the selected object will not be available as a drop down list – users will instead be able to type free text into the filter field to find a value. This can be used to prevent large lists appearing in drop-downs on the Filter screens.