Displaying an arbitrary number in the rows of a table

Sometimes you are asked to display a quota target or some other non-aggregated number in a table.  A member of the Dimensions community passed on a strategy that uses the Rot function to place a number in a table.

This example uses the gender question in the museum example from the DDL.  The following example shows the numbers 600, 340 and 260 as made-up quota targets.  It uses a derived element and the Rot function to display the targets in a row of the table.

gender{'Actual' base(), Quota 'Quota' derived('CLong(Rot({0, 600, 340, 260}, 1))'), Male [IsHidden=True], Female [IsHidden=True]} As GenderQuota '' * gender

A derived element is an element that is calculated from other elements using an arithmetic expression as opposed to the logical expression used in an expression element.  In this case we are using the Rot function which rotates the list of elements provided in the first parameter.  The second parameter specifies the number of items to return.  We are returning just one item.  So the Rot function is returning each of the elements in turn starting with the element in position 1 (if you start indexing the list from 0).  CLong then turns the category into a numeric value.

So you can setup a row to contain arbitrary numbers by using the derived element expression CLong(Rot({0, Number 1, Number 2, …, Number N}) where N is the number of columns required.

And here is the resulting table.

Example table showing quota targets

If you want to use this strategy to display quota targets, you may want to read these directly from the quota object model. This example shows hard coded numbers.

Leave a Comment

%d bloggers like this: