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
- 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. - 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. - 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. - In SAC, create a model to the calculation view through Live HANA connection and then create a story based on the model.
- 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. - 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
- 2569847 - Where can you find user assistance (help) for SAP Analytics Cloud to use, configure and operate it more effectively?
- Have a question? Ask it here on the SAP Community. Or reply and share your knowledge!
- 2487011 - What information do I need to provide when opening an case for SAP Analytics Cloud?
- SAP Analytics Cloud > Learning > Guided Playlists
- SAP Analytics Cloud > Learning > Guided Playlists > Getting Support
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 , sac kba hana live query , LOD-ANA-BI , Business Intelligence Functionality, Analytic Models , How To