Totals

[Former Member]

Hi,

I am trying to create the following query but am either overthinking the solution or just being completely inept.

Basically it is as follows:

Rows containing Company code and the Document currency.

Columns containing Vendor and then Key Figure (Amount).

There is a month year variable used on the query so results will vary widely depending on what the user selects.

The user is happy with report but would like a total for each Vendor and for each currency.

Obviously a normal total put on this now results in a * when currencies are mixed.

What is the best solution to achieve this?

At the moment I am thinking of coding this in VBA so that the calculation is done via the SAPBEX code at runtime. Im sure I could do this but im not exactly an expert at using the SAPBEX code and it would probably take a fair bit of time to figure out.

Am I overthinking this? Im sure I am missing a dead easy way of doing this.

Thanks

Mark

[former_member211907 · Accepted solution]

First, I'm rather surprised you use vendor in columns.. your customer must only have a small handful of vendors. Vendor, Material, Customer are the sort of master data objects that often have thousands to millions of different values.

Second, most users only need data in a single currency so that it can be compared properly. The current conversion features are very useful for this sort of thing, as is the general idea of adding an organizational consolidating currency version of each value key figure to your infoprovider.

As you noted, the subtotal of a non-converted currency key figure produces irrelevant data. I would create an additional value key figure for subtotaling at the vendor only level that uses a properly designed currency translation (Also called 'conversion' in Bex Analyzer 7). The user can then display or hide that translated value as required.

An alternative solution would be to use cells, or if you are using Bex Analyzer 7, to create a formula result cell in your report that displays just the subtotal at the vendor level.

[Former Member · Accepted solution]

Hi,

Thanks very much for your reply. Yes it is a little odd for the Vendor to be shown as a column but they have only a few that they want to see in this report so it isn't a problem.

My first thought was to show as a translated currency but the user has specified that he would like to see in document currency for each currency so that bit is out of my hands.

So looks like I will have a look at the other options. With regards to the cells options is this using specific cells or can it be used dynamically depending on what results are returned?

Thanks again for your help.

Mark

[Former Member · Accepted solution]

Hi Mark,

You will not be able to get Totals for different currencies at the end of the report in the result row. Break up the result rows accordingly with similar document currencies or show up the totals w.r.t. Vendors, Currencies.

Regards,

Geeta