SAP Knowledge Base Article - Public

2844684 - The sort order of the color dimension is broken after apply sort order for the time dimension in the X axis in stacked bar/column chart in SAP Analytics Cloud (SAC)

Symptom

In the stacked bar/column chart with a time dimension year on the X Axis within a story, if apply the sort order Earliest to Latest for the year, the custom order defined for the color dimension in the chart will be broken, and even for the default sort order, sort descending and sort ascending, same issue also happens.

Environment

  • SAP Analytics Cloud

Reproducing the Issue

  1. Create story in SAC and insert a Stacked Bar/Column chart.
  2. Add one dimension and measure to the chart.
  3. Add one dimension to the color.
  4. Select the chart and click sort options, then select the dimension used for the color and click Add Custom Order...
  5. Define the custom order, like changing the dimension order to a different one than the default alphabetical order, then apply the order to the chart.
  6. Click the sort options and select the dimension in the X axis, set the order to Earliest to Latest.
  7. Notice that the custom order defined for the color dimension is broken.

Cause

This behaviour is a product limitation. The chart can only honor what the query returns. The unbooked members of a dimension may not return in a query so they may locates behind the booked members even they are set to show before the booked members.

Resolution

An enhancement request has been created and you can vote for it: https://influence.sap.com/sap/ino/#/idea/239693/?section=sectionDetails

Some possible workarounds:

  1. Enabling the 'unbooked data' option from the ... menu for the corresponding dimensions in your chart in the designer panel.
  2. Use a calculation to replace any null values with 0 so that a value is included in the resultset returned from the back-end.   One example of a such a calculation (non-BW datasources) is:

    IF (ISNULL (["ProfitAndLoss":Gross Margin]), 0 , ["ProfitAndLoss":Gross Margin] )

Note: In both cases above, the Top N (Auto) may be triggered, so you may need to manually override the Top N to a higher value, or filter the chart further to avoid it.

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 , KBA , LOD-ANA-DES , Story Design & Visualizations , Bug Filed

Product

SAP Analytics Cloud 1.0 ; SAP BusinessObjects Roambi, cloud edition 71