SAP BI 4 - BEx Query Hiearchy - Excluded Members and total not adjusted

[Former Member]

Hi,

I have a hierarchy which I am using with a SAP BW BICs connection.

I have add an input control to exclude certain members if needed but the total doesnt seem to adjust. For example,

UK = 150

     - England = 50

     - Wales    = 50

     - Scotland = 50

If I exclude Wales I still see 150 for UK. UK is not an actual member it is a node in the hiearchy so not sure why it isnt being re-calculated?

UK = 150

     - England = 50

     - Scotland = 50

Cheers

[sateesh_kumar1 · Accepted solution]

Hi Leo ,

It's a product behavior .

2185759 - In Web Intelligence, selecting a value in Input Control does not correctly update aggregate totals in report

queries containing a measure associated to a hierarchical object return all the different aggregations possible in the hierarchy. Indeed, OLAP sources always return hierarchical data with preaggregated values at all node levels. This is by design.

This explains why the aggregations of hierarchical data in Web Intelligence are not calculated locally but are the values from the data source. In consequence, they are not impacted by local filters or member selection.

[sateesh_kumar1 · Accepted solution]

Try using Set functions with Aggregated functions .

Ex : =Sum([Net Revenue - Actual];[Country].Children)