Analysis Office Filter Data by Member

A feature of Bex Analyzer was the upload of selections, so you haven't to type the selections all the time. This feature wasn't available for Analysis Office until version 2.2 SP3. So I looked deeper in this function and want to share my knowledge.

BEx Analyzer Upload Selection
BEx Analyzer Upload Selection

Since I think Analysis Office 2.1 it is also possible to filter your data by paste your selection from a clipboard. In the settings of Analysis Office you can define the number of members which are displayed in the filter dialog. If a dimension has more members than you defined in the user settings, a filter dialog for mass data is opened. In this particular dialog you only see the selected members, but you can add members by using the input help.

 

Hint: You can also define the number of members by using the admin parameter FetchMemberLimit and you can also disabled the mass data dialog by using the parameter EnableMassDataSelector.

 

In the prompt dialog you have to select the "Add Lines Using Filter By Member"-Button.

Analysis Office Prompt Filter by Member
Analysis Office Prompt Filter by Member

Note: You only see this button, if your variable is either a selection option variable or allows several single values.

 

After you click on this button, a new dialog will popup, where you can filter by members of this dimension. If the number of members are more than the defined number in the user settings, you see the filter dialog for mass data.

Analysis Office Select Member
Analysis Office Select Member

You are now able to paste your selection to the filter dialog by using the Paste from Clipboard icon at the left bottom of the filter dialog.

 

Note: The Members have to be displayed as key in the list.

 

If you want to insert a lot of members that you want to use for filtering, you can use Paste from File. The Paste from File icon is next to the Paste from Clipboard icon if you use an Analysis Office version which is higher than 2.2 SP3.

 

If you click on the button a windows open dialog appears and you can select the file which you want to upload.

Analysis Office Select Textfile for Upload
Analysis Office Select Textfile for Upload

After you click "OK" the members are added to the filter.

Analysis Office Uploaded Members by Textfile
Analysis Office Uploaded Members by Textfile

By click OK, the filter is applied.

Analysis Office Selected Filter Values
Analysis Office Selected Filter Values

By click OK, the filter is applied to the prompt dialog.

Analysis Office Prompt with Values
Analysis Office Prompt with Values

Now you can run your query and wait until you see your result. Another possibility is to filter your data by VBA code.

Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
29,99 € 1
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 € 1

Write a comment

Comments: 4
  • #1

    Ivan (Wednesday, 27 July 2016 08:55)

    Hi Tobias, great entry as always. Quick question: In Analyzer we had the option of User Settings for a filter selection, so you can for example read values in master data instead of values for navigation. That was very helpful when the filter took so long to bring the values to the user. I found that option in Analysis, but what I didn't find was the checkbox Always use these settings that was in Analyzer, so the system saves your preferences. Is there a similar option in Analysis? Thanks!!

  • #2

    Tobias (Thursday, 28 July 2016 09:14)

    Hi Ivan,

    thanks. The only thing I know is that you can set the setting AllowChangingAccessMode = True that you are able to switch between posted and master data.

    https://abload.de/thumb/masterdatavsposteddatq2spd.png

    But you can define in your query that you want to read always from master data. I hope this help you, if not feel free to ask.

    Tobias

  • #3

    Tobias (Thursday, 28 July 2016 09:15)

    Here is the screenshot a little bit bigger

    https://abload.de/img/masterdatavsposteddatq2spd.png

  • #4

    Ivan (Friday, 29 July 2016 10:05)

    Thanks for the reply Tobias. I knew that option, what I discovered is that if you save your workbook locally then the system saves your settings for that access to master data. Thanks for your help. Keep up the good work!