· 

Analysis for Office Filter multiple hierarchy nodes

This week I discussed with a colleague. He ask if there is any way to filter multiple entries in a hierarchy because there is no Paste or Upload function in the hierarchy filter dialog.

Filter Hierarchy Nodes by select manually
Filter Hierarchy Nodes by select manually

So he selects the nodes he needed manually. This could be very frustrating and time intense if you have a lot of nodes you want to select. But there is a smarter way to filter multiple hierarchy nodes either via Paste or Upload. How it works I show you in this post.

 

First add your dimension into the rows.

Hierarchy displayed in the crosstab rows
Hierarchy displayed in the crosstab rows

Make a right click on the dimension and select the option Use only hierarchy Product Hierarchy for Filter. The term Product Hierarchy comes from your dimension. In my case the Product Hierarchy.

Only use hierarchy for filter
Only use hierarchy for filter

And when you now select Filter By Range, the following filter dialog will be shown, where you get a Paste or Upload function.

Filter with Paste & Upload function
Filter with Paste & Upload function

Now you can paste or upload your values and click OK. Your data will be filtered. When you now remove the the option Use only hierarchy for filter, the hierarchy wil be displayed again.

Hierarchy after filter is applied
Hierarchy after filter is applied

If you have any questions or problems, just leave a comment or write me an email.

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

SAP Analysis for Office - The Comprehensive Guide
The book SAP Analysis for Office - The Comprehensive Guide by Tobias Meyer is a pdf book about SAP Analysis for Office. It is based on Analysis for Office 2.8 and contains 346 Pages.
45,00 €
SAP Analysis for Office - The Comprehensive Guide
SAP Analysis for Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis for Office. It is based on Analysis for Office 2.7 and contains 299 Pages.
37,00 €

Write a comment

Comments: 4
  • #1

    Maximilian Klinger (Monday, 09 December 2019 23:27)

    I always use the copy from clipboard and concatenate in Excel the single nodes or accounts i want like =concat(A1:A99,";") afterwards needs press of F9 for formula conversion of fields in Excel that gets generated. Voila i get all nodes in form of 12345;0815;4711 and can quickly paste in Hana Query Editor, AFO selection screens/ filters or where i need them.

  • #2

    Tobias (Tuesday, 10 December 2019 08:32)

    Hi Maximilian,

    yea you are right, there are a hundert ways to solve problem. I only showed the "buildin" of Analysis Office without any extra formula or something else.

  • #3

    Gurpreet (Sunday, 01 November 2020 16:37)

    When using this feature, it works fine for the normal refresh functionality, however when i go to prompts and refresh through there, it looses the filter. Is there a setting I need to apply to make sure I don't loose the filter?

  • #4

    Tobias (Monday, 02 November 2020 11:01)

    Hi,

    when you deactivate the "Only Use Hierarchy for filter" and than use the prompt all values will be the same. Can you try this?