Bex Report - WorkBook Filter - characteristic value & hierarchy

[Former Member]

Dear all,

I have a complex query with hardcode columns, rows and lots of cell specific definitions (with selections).

This query is to be applied several times in a workbook. Each insertion will have different Profit Center filters and therefore each one will correspond to a “fund”.

My issue is that when using Excel Filter for Profit Centre I can select either Profit Centers OR Profit Center Hierarchy nodes (depends if the profit centre has hierarchy active or not on the properties) but not both at the same time. On my scenario, each fund will be a combination of single profit centers and profit center hierarchy nodes…

The only way to achieve this is by making the profit centre selection (filter) at query level where I can pick both Profit Centers AND Profit Center Hierarchy nodes. In this way I will need to have a query per fund. This will have a huge impact on maintenance because all changes will need to be replicated on 10 queries (10 funds). The usage of Column and Row structures do not solve my issue because I use lots of cell specific definitions. How can I do this?

Additional question: how can I filter inside a filter selection? E.g. I want to get all the P* profit centers included on a specific Profit centre hierarchy node. I don’t want to select each individual P* profit centre because I want my query to pick each new P* created under that hierarchy node. How can I do this?

Thanks in advance

[Former Member]

Create another InfoObject for Profit Center (e.g., ZPROFITCTR) with reference to your original Profit Center InfoObject (0PROFIT_CTR?). Add this new InfoObject to your InfoProvider (or as a navigational attribute of 0PROFIT_CTR), then add it to the query. Use 0PROFIT_CTR for filtering the hierarchy nodes, and the new InfoObject for filtering the profit center values.

If I understand your second question, adding a variable to the query for the ZPROFITCTR InfoObject would allow you to select profit center values with wildcards.

Hope this helps...

Bob

[Former Member]

Hello Bob,

This is a good solution from tehoritical perspective. However, it means change the data model and that makes me think twice. My perpective is that your datamodel should not be dependent of workarounds for specific report.

We may need to go for it if there is no other option an after we balance the datamodel change vs new report maintenance.

Additional options are wellcome!