Share This Page

ShareShareShareShare

Share This Page

ShareShareShareShare

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:

Structure of Dimension

  • The dimension, in the table view is:

members of Dimension

  • And the dimension hierarchy is:

dimension hierarchy

  • 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.

Product Expansion

  • 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

Sort Order

  • We select the Product Member Selector.

Product Member Selector

  • We then select member sorting and grouping

select member sorting

  • Enable member sorting and select the property (SortKey) which we want to sort by.

member sorting

  • This gives us a report with the products sorted as required.

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.

image

About the Author

Martin Lloyd
Principal Consultant – Packaged Solutions

Martin is a Chartered Accountant and a SAP Certified Consultant with over 20 years’ experience of design and implementation of Enterprise Performance Management, including SAP BPC.

Other Stories by the Author

Let's Talk About Your Needs

Thank you for your submission. We'll be in touch.