We had a request to show the summary of Net Value Billing by Brand (Prod. Hierarchy) in a descending order, if required users can drill down to Product (Material) level.
Data which was coming out from SAP BW system is based on Product (Material). So we had to create a report layout with Prod. Hierarchy and Material based on the requirement.
We added a Break into Prod. Hierarchy
Removed extra row and break header
Added aggregation as Sum with context (=Sum([Net Value Billing]) In ([Prod.hierarchy AB&NS]))
and report header.
Note: If we just use the Sum as aggregation it will be summed based on Product, when we use Fold option the values will be wrong.
Applied Fold option to fold the Material and to show only Prod. Hierarchy
Now when we applied sort as descending order in the Net Value Billing, values are not getting sorted as descending through Measures. Where as it is getting sort by Prod. Hierarchy.
To achieve this we have to take out the “Apply implicit sort to values” option from Break properties by selecting break column.
Now when you apply sort on measure, it will work as expected.