In this blog, I have reviewed the importance of Sort Order in presenting dimension members in EPM input forms and reports. Having discussed the value of managing the presentation order, I will discuss how we can influence it. The technique used only applies to the BPC standard as it relies on the functionality in the EPM add-in.
Why is Sort Order important?
- In EPM reports and input forms, we largely take for granted that the rows and columns are displayed.
- We may have time periods in the columns and expect them to be displayed in a calendar order.
- We may have accounts in the rows and expect to see them displayed in a predetermined order to make sense to the viewer of the financial statements.
- It may not be as obvious, but users will have preferences as to how members of other dimensions are ordered. In the examples considered below, I have concentrated on the PRODUCT dimension.
- The product dimension members have two properties which influence the preferred sort order.
- NEWPROD – Flag as to whether the product has been introduced in the current year. Value is Y.
- PRODTYPE – Grouping of products into common types. Value is TYPE01 to TYPE04.
- The dimension configuration is:
- The dimension, in the table view is:
- And the dimension hierarchy is:
- A simple report with products in the rows is shown below. It can be seen that the products are shown in the order of the hierarchy.
- In this case, the requirement is to sort the members first by product type and then by placing the new products after the existing ones.
Where can Sort Order be managed?
- The default sort order, as can be seen, is the hierarchical order. This will be impacted by the addition of new members, over which we have little control.
- We can, however, explicitly sort the dimension members for display.
- From the report editor
- We select the Product Member Selector.
- We then select member sorting and grouping
- Enable member sorting and select the property (SortKey) which we want to sort by.
- This gives us a report with the products sorted as required.
- Creating a Sort Order driver
- The sorting discussed above is required by product type and then new product.
- To achieve this, I have constructed the sort key with two segments
- The product type is mapped into a three digit code with a leading 1
- The new product is mapped into 1 for new product < > Y and 2 for new product = Y
- A sort key of 9999 was manually assigned for no product. Therefore, this will appear at the end of the list.
- The SortKey property can be updated manually or as part of an automated dimension maintenance procedure.
- For dimension members, which are manually maintained, the SortKey would also be manually maintained.
- For dimensions, which are generated from BW, we would also want to automate the population of the SortKey property.
- Assuming the following BW fields:
- ZTYPE (product type)
- ZNEWPROD (new product)
- The relevant lines for the transformation file to update the PRODUCT dimension would be:
- PRODTYPE = ZTYPE
- NEWPROD = ZNEWPROD
- SORTKEY = *STR(1) + ZTYPE(5,2) + IF(ZNEWPROD=*STR(Y) THEN *STR(2);*STR(1)
In this blog, I have reviewed the importance of sort order in presenting dimension members and how to influence it in the EPM add-in. The example was based on two sort order components, but this can be increased if required.