A way to achieve Key figure sorting and hiding repeated values for only the first column using a query view.
Objective: Using Query View to sort / order by the KEY FIGURE values and hide repeated text for one column while displaying the repeated values for the rest of the columns. This document intends to explain one of the ways in which this could be achieved. The considered query did not have any free characteristics, all the characteristics were readily available in the ROWS.
Requirement: There were two requirements to be answered:
1. 1. Key figure “Date From” was to be sorted in ascending order. Key figure properties do not offer sorting in BI 7.0 Query Designer.
2. 2. Display the characteristics “Report Date” against each of the rows irrespective of the repetition, whereas the characteristic “Number” in the report was not to display repeated values.
1. Sorting Key figures:
Key figure properties do not have any option to sort the key figure values in BI 7.0.
1. 2. Hiding repeated values for only one characteristic and displaying for others:
By default the query property to hide repeated key values would be selected. This setting is for the entire query and not specific to any particular characteristics. This setting makes sure that any two or more consequitive rows having the same values would be replaced by the display of value for the first row and a blank cell for the remaining till a new value is encountered. Also the characteristics property doesn’t hold any option to hide repeated values, so this setting is not characteristic specific.
Active BW data flow along with existing data and queries with key figures of data type “Time”, or any key figure data type where sorting would make sense.
BEx and WAD settings:
From the report output it can be found that the Characteristics ‘Number’ and ‘Report date’ are not displaying the repeated values. Also key figures ‘Time From’, ‘Time to’ and ‘Hours’ are being displayed randomly without any order. Now if the requirement is to suppress the repeated values for ‘Number’, whereas display the repeated values for the ‘Report date’ and to sort the key figure ‘TIME FROM’ in ascending order then we have to carry out the below steps.
1. Run the report in BEx (T-Code RRMX)
2. Sort the key figure TIME FROM in ascending order by clicking on the icon as highlighted in the below screenshot:
Now the data is sorted in ascending order for time.
3. Sort the ‘Number’ column in the same way as ‘Time’ was sorted:
This is the exact desired output which is expected in the report.
4. Save this as a VIEW:
5. In WAD, click on the table and choose the view icon and point to the created view and save the Web Template:
6. Run the portal link again:
Data is now in the required format. Column which required the suppression of repeated key values is suppressed, rest is displayed without suppression and the key figure is sorted in ascending order.