It has long been established that applying conditions yields confusing results in the results row of a query. It is documented in SAP Help that active conditions only limit the number of rows that are displayed in the query output. To this end, the result cells of a column should not take the active condition into account. This is according to the documentation.
However, I can show that the results cells can be manipulated to show the result taking the active condition in account, but that only applies to some cells.
Where this then leads to confusion, is that certain result row cells will be correct according to the displayed data, while others are not correct. This leads to a lot of confusion for end users.
Consider Figure 1 below. This shows a very simple query output, showing sales figures for 2 months. The 3rd column is the variance between these 2, defined in the query designer as Col 1 % Col 2.
As can be seen from the above screenshot, there are 4 sites returned, and the result level cells are calculated correctly. In this case the 2 Sales Value columns are summed, while the Percentage Variance column uses the result cells to perform it’s calculation.
A condition is activated on the query, and the condition is configured to restrict the display to the top 2 sites, based on Sales Value. Figure 2 shows the query output once the condition is active.
In this mode, the condition has been correctly applied. However all the cells in the result row are incorrect. In the case of Sales Value May 2009, the total should actually be 29,730,935
This is the default behaviour, as if nothing more was configured in the query, and this corresponds to the behaviour outlined in the SAP Help documentation.
If, however, the query properties are altered slightly, then the output in Figure 3 is achieved.
Figure 4 shows the configuration change that was implemented in order to get the result cells for Sales Value May 2009 and Sales Value May 2008 to be correct.
However, Percentage Variance still showing the report level result, not the result of the new total rows. The correct result should be 39.29
In this scenario, the Sales Value columns are showing the correct total, in that the total is the sum of the displayed rows, but the Percentage Variance column is still determined on the entire report output, so makes no sense in this context.
No matter what configuration is applied within the query designer, it seems impossible to get the Percentage Variance column to agree to the displayed data. It does not make sense to display a result to the users, where the total columns do not agree to the displayed data.
In version 3.5, it was possible to the user Table Interface Class to manipulate data in an ABAP class prior to the data being rendered. This functionality allowed us to manipulate the data and perform the calculations manually, thereby ensuring that the data presented to the user was always correct and made logical sense. However, that functionality has been removed in version 7.
- Increased page size, therefore slower page rendering
- At certain clients we have tried to use the XML data provider information item, which itself is quite large and adds to the page size
I don’t have a clean solution, and would love to hear from someone who has undertaken a similar exercise and come up with a clean, neat, simple, scalable solution.
I’ll be logging an OSS call with this information as well, so if something comes from that, this blog will be updated.