Dependent Dropdown in Analysis for office

[shahid]

I am looking for an expert advice regarding Analysis for office/BI query.

We wanted to implement dependent dropdown/cascading filters.

I have created an ADSO/Query which I am displaying in Excel using “Analysis”.

Once the table/query is displayed, I have two columns for input. I want to control the second column based on first.

Please find the screenshots.

I tried finding more information from SCN blogs, but somehow I felt may be I am missing somehting.

Can you please suggest me way forward or alternate approaches or may be I am missing something?

Please let me know I can provide you more information.

https://archive.sap.com/discussions/thread/3855817

https://answers.sap.com/questions/136675/analysis-for-office-22-f4-value-help-not-working-c.html?chi...

There is a Product type and Product in the table.

I have defined these fields/info objects as compounding.

Both these fields are defined as “use char as key figure” at ADSO.

Both these fields are defined as “Input ready” at query.

When I select product type, the products should be filtered and should only show the products that are applicable for that product type

4.png

[gregor_dieckmann · Accepted solution]

Hi Shahid,

characteristics as key figures are special since the characteristic in the query is exposed as a key figure and is not filtered in the query. AO calls the F4 help based on master data. Observe that this is quite different to the usual usage of characteristics: when you set a filter this filters the whole query result.

But in your use case you want to pick the value of the compounding parent in a key figure column to restrict the F4 help of another key figure value. Since the key figures might even be restricted by other characteristics it is even not obvious where to find the value of the compounding parent. To my knowledge this kind of logic is not implemented in AO.

You may use VBA APIs SAPSetFilter and SAPCallMemberSelector to achieve this. The idea then is to use another data source where you set the filter of the compounding parent to restrict the value help of the compounding child, the data source e.g. may the InfoProvider based on the characteristic.

Regards,

Gregor

[shahid · Accepted solution]

Thank you Gregor.