SAP Knowledge Base Article - Public

2830016 - How to sort dimension values in SAP Analytics Cloud (SAC) filter dynamically in order of measure from HANA

Symptom

  • The dimension values will be shown in order of alphabet in filter by default
    => How to sort dimension values in filter dynamically in order of measure?
  • Filtering data by rank can focus a chart on a specified number of data points with the highest or lowest values
    => How to filter data by rank on page level or story level to show data of Top N dimension values only?

Environment

  • SAP Analytics Cloud
  • Live Data connection to SAP HANA

Resolution

  1. Create a View to get the order of measure as [Rank] dimension with key column(s) together from one table in HANA.
    => You can refer to SQL Query for Ranking in SAP HANA SQLScript Reference.
    => Please ensure that the [Rank] dimension is unique in the view.
  2. Create a Calculation View to join the original table and the generated view based on the key column(s).
    => The [Rank] column will be included into the result with other columns from original table together.
  3. Set Label Column property of the [Rank] column to the column you want to show in order of measure, like [A]. 
    => Note: if you have any problem for procedures above, please reach your HANA expert or SAP HANA support.
  4. In SAC, create a model to the calculation view through Live HANA connection and then create a story based on the model.
  5. Add a story/page filter on the [Rank] dimension and ensure that Show Description is selected.
    => Now, the filter appears with values from dimension [A] but in the order of measure based on ID of [Rank] dimension. For example, all products in the order of revenue from highest to lowest.
    => If the measure values in the HANA table are changed, the order of dimension values will be also changed automatically in the SAC filter.
  6. Add an Input Parameter "TopN" in the HANA View and then add filter as ""RANK"<=$$TopN$$".
    => One variable will be generated in the SAC model and then you will be prompted to enter one specified number to show data of Top N dimension values in story.
    => For example, data of top 5 employees with highest salary, data of top 10 products with highest revenue.

See Also

Your feedback is important to help us improve our knowledge base.

Keywords

SAP Cloud for Planning, sc4p, c4p, cforp, cloudforplanning, Cloud for Analytics, Cloud4Analytics, CloudforAnalytics, Cloud 4 Planning, BOC, SAPBusinessObjectsCloud, BusinessObjectsCloud, BOBJcloud, BOCloud., SAC, SAP AC, Cloud-Analytics, CloudAnalytics, SAPCloudAnalytics,Error, Issue, System, Data, User, Unable, Access, Connection, Sac, Connector, Live, Acquisition, Up, Set, setup, Model, BW, Connect, Story, Tenant, Import, Failed, Using, Working, SAML, SSO, Live ,HANA, filter, order, sort, values, list, dynamically, measure, dimension, top, rank , KBA , LOD-ANA-BI , Business Intelligence Functionality, Analytic Models , How To

Product

SAP Analytics Cloud 1.0