Skip to Content

Introduction

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.

 

Step-by-Step Analysis

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.

Figure 1 : Basic Query 

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.

Figure 2 : Condition (Top 2) 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 3 : Condition Active

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.

Figure 4 : Config

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.

Possible Solution(s)

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.

An alternate solution which we have implemented at a few clients, is to manipulate the resulting HTML data using Javascript. This is possible, albeit not the neatest solution. There are a number of negatives to this solution:

  • Increased page size, therefore slower page rendering
  • There is a lot of Javascript required in order to achieve the results, and hence additional data needs to be downloaded each time.
  • 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
  • The updated results are only applicable in the web output of the query. If the query is downloaded to PDF or Excel, then the Javascript is not executed, and hence the results rows are different to what is displayed in the web
  • The javascript functions we’ve used have been fine tuned over time, making them as dynamic as possible. However, if HTML structures change, then the functions will fail. This can, and has, happen when patches are applied to the system. If the patch means the HTML table generated for the analysis item has a new ID, then the javascript will fail.

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.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply