Object Replacement

The values chosen in the Category Objects can now be referenced using {{Object Name}} . So the two examples above would be referenced as {{Year End}} and {{As At Period}}, these are known as session variables.

Object replacement can be used in three main areas;

• Chart and column titles

• SQL

• Filters

Note that you can also reference category objects that do still filter, for example {{Customer Name}} , however they will still also filter the chart(s) in question. If you don’t select an object from the category object then {{ }} will return ‘All’ by default although this can be altered using the ‘Select All Text’ option when setting up the category object.  In some scenarios allowing the user to select ‘All’ won’t make any sense (for example a category that is designed to give you an overview of a single customer), in which case unclick the ‘Select All’ option as per Year End and As At Period in our example.

Chart and Column titles

In Data Connections you can create objects that reference the {{Object Name}} values in their titles. {{Year End}} will be replaced by the Year End you select from the Year End category object and {{As At Period}} will likewise be replaced by whatever you select in that category object. The column heading for the object shown below could, for example, become ‘Inv Sales 2018 P7’, if 2018 and period 7 were selected from the category objects.

Open

You can use exactly the same logic in Chart/Data Table names, titles and subtitles. So ….

Open

….. means that selecting these category objects

Open

…. will result in the chart title and subtitle shown below

Open

SQL

Our category objects {{Year End}} and {{As At Period}} can also be referenced in the SQL select line of objects configured in the Data Connection. For example ;

Open

This is saying that if the financial year on any given row is the same as {{Year End}} and the period number is <= {{As At Period}} then return LineTotalValueNadjBase (Invoice Value), otherwise return zero. This means that even if the SQL WHERE clause returned every single invoice line the company had ever done this object will still only return non-zero values for the Year End selected, otherwise it will return zero.

In our example we have called this object Invoice Sales O/R {{Year End}} TD.  You would only need to change;

<= {{As At Period}}

to

= {{As At Period}}

to create an object you could call

Invoice Sales O/R {{Year End}} Period {{As At Period}}

By expanding this logic you could easily create the following measure objects;

Open

Filters

You can also reference category objects in chart filters. Although we have built the logic for <= {{As At Period}} into our Invoice O/R Sales {{Year End}} TD object SQL we could have achieved the same result by setting the chart filter as follows:

Open

Putting It All Together

Using all this knowledge we can now build the chart that was originally requested.

Open

If we change the ‘As At Period’ category object from Period 10 to Period 1 we can see that the YTD and PTD figures become the same for both 2018 and the Prior Year, as you would expect.

Open

Whatever year/period combinations we choose the values and column headings will change as appropriate. If we were to look at the underlying SQL however the WHERE clauses would remain exactly the same; only the logic within the CASE statements on the SELECT line will have changed.