BICS Query with Hierarchy, Block Footer

[Former Member]

Hi All,

I'm using Webi in BI4.0 SP2 (no patches) connecting directly to a BEx query (no universe). I have a geography dimension which is a hierarchy. I'm using this hierarchy in a Webi block, so a user can expand the hierarchy, and for any row that isn't a leaf member (so any parent level of the hierarchy) it's showing a total of some sort. Like this:

Australia $400

  NSW $100

  VIC $100

  SA $100

  QLD $100

But I want to see the totals below the leaf rows (like a traditional totals row).

E.g. I have 'Australia' in my hierarchy. If I expand that I see NSW, VIC, SA, QLD - each with values.

I want to see this on the page:

NSW $100

VIC $100

SA $100

QLD $100

Australia $400

Things I've tried:

• Using a universe so I can group (or section) on the L01 dimension that's created. This works just like I like it, but I then lose the custom sorting defined in the hierarchy. I can custom sort, but there's hundreds of things so this is awful.
• I don't have any elements of the query available as separate dimensions (e.g. 'country' is not a dimension) so I can't simply add a section or break on another dimension.
• I've looked at the 'set' functions which can return depth and parent for a row/member in the hierarchy, but I can't work out to a variable to show the name of the parent member next to a member (I could then section/break on this).
• I don't have access to modify the BEx query.

[former_member188911 · Accepted solution]

Hi David,

this is a recurring question that apparently has no immediate solution, regardless of whether you define the query to show results at the bottom Web Intelligence, as well as many other BI products will display the total on the top.

I would suggest to create an idea at https://ideas.sap.com and advertise it here, so that you can get votes for it, I will definitely do cast my vote for it.

Another suggestion is to move to the latest available patch level, SP02 plain is not supported

Best regards,

Simone