SAP Knowledge Base Article - Public

2111196 - Search on hierarchy disabled in Analysis for Office

Symptom

When filtering by member on a hierarchy, the search box is grayed out (disabled). Therefore, you are not able to use the search functionality.

However, in transaction RSRT (backend) the F4 help is enabled and it is working as expected.

Environment

SAP BusinessObjects Analysis, edition for Microsoft Office, BW/4, BW 7.X

Reproducing the Issue

#1 - Run a query in Analysis for Office
#2 - Filter by Member on a hierarchy
#3 - The search box is grayed out (disabled) and it is not possible to search for members

Cause

Usually a hierarchy is considered "large" when it exceeds the size of 50000 nodes.
For performance reasons Analysis for Office (respectively the BI Consumer Services called BICS) turns off the search for a hierarchy if it is a "large" hierarchy.

Resolution

It is possible to change a threshold value which defines when a hierarchy is considered as large.

  • In BW/4HANA systems:

    Maintain the value in field "Hierarchy is large" in transaction SPRO -> SAP Reference IMG -> SAP BW/4HANA -> Analysis -> Settings for Performance of Analytic Manager.
    Maintain the value in field "Limit for Search in Hierarchies" in transaction SPRO -> SAP Reference IMG -> SAP BW/4HANA -> Analysis -> Settings for Value Help (this will change the entry RSMDHIERSEARCH_LIMIT in table RSADMINC).

  • In other BW systems:

    Maintain the value in parameter RSR_HIER_CARD_THRESHOLD in RSADMIN, as described in SAP Note 1256547.

How to Know the Size of the Hierarchy

  1. Run RSH1 transaction
  2. On "With Hierarchy Basic Char" input the InfoObject name of the hierarchy (check notes bellow for steps on how to find the correct hierarchy name using BW Modeling Tools)
  3. Find the hierarchy and double click on it
  4. Click on the "Header Data" icon ("Hat" icon, first one)
  5. Copy the "Hierarchy ID" and "Date From" (if exists) and close dialog
  6. Go to SE16 transaction and check table RSRhiedir_olap
  7. Enter the "Hierarchy ID" on field HIEID to find the relevant hierarchy
  8. Sum the values of fields CARDFIX and CARDREST
  9. If the sum of these values is greater than the value defined in the thresholds mentioned above, the hierarchy is considered large and Analysis for Office will disable the search for this hierarchy.

How to find the hierarchy technical name on BW Modeling Tools

  1. Open BWMT and select the BW system
  2. Press "ctrl" + "f" on the keyboard to search
  3. On "search string" input the query name > Click on Search
  4. Click on the query and go to Filter Tab
  5. Find the hierarchy description on "Filter: Fixed Values"
  6. When you click on it, the technical name of the hierarchy will be displayed besides "Reference Characteristic"

Hierarchies based on CDS views are always treated as large regardless of their actual size, and consequently the search field is always disabled.
For this case the parameter RSR_HIER_CARD_THRESHOLD is ineffective.

On this case apply the SAP Note 2879713 - Wrong information on transient hierarchy cardinality

Keywords

grayed out, greyed out, search, hierarchy, hierarchies, disabled, F4-search, hierarchy search, F4, hierarchy is too large, value help , KBA , BI-RA-AO-XLA , Excel Addin , How To

Product

SAP Analysis for Microsoft Office all versions