reyemsaibot

SAP BI Blog about SAP BW/4HANA, Analysis for Office and SAP HANA - by Tobias Meyer

staging.tobiasmeyer.net

Analysis Office Filter Data by Member

11. Juli 201611 Kommentare

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 for 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 for Office 2.1 it is also possible to filter your data by paste your selection from
a clipboard. In the settings of Analysis for 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 for Office Prompt Filter by Member
Analysis for 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 for Office Select Member
Analysis for 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 for 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 for Office Select Textfile for Upload
Analysis for Office Select Textfile for Upload

After you click „OK“ the members are added to the filter.

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

By click OK, the filter is applied.

Analysis for Office Selected Filter Values
Analysis for Office Selected Filter Values

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

Analysis for Office Prompt with Values
Analysis for 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.

These posts might also be interesting:

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.

Kommentare

11 Kommentare zu „Analysis Office Filter Data by Member“

  1. Avatar von Ivan
    Ivan

    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!!

    Antworten
  2. Avatar von Tobias
    Tobias

    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

    Antworten
  3. Avatar von Tobias
    Tobias

    Here is the screenshot a little bit bigger https://abload.de/img/masterdatavsposteddatq2spd.png

    Antworten
  4. Avatar von Ivan
    Ivan

    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!

    Antworten
  5. Avatar von Pascal
    Pascal

    what format should the file for the filter options have? in the picture I can see a .txt file. are the members comma separated? or should there be one line (newline) per member?

    Antworten
  6. Avatar von Tobias
    Tobias

    Hi Pascal, the members are each in a new line. It can be a txt-file or a csv-file. I hope this will help you. Best regards, Tobias

    Antworten
  7. Avatar von Amir
    Amir

    Hi Tobias, I created a dashboard in Excel by using the SAP Analysis for Office. This dashboard is used by end-users that don’t know anything about Analysis for Office. They want to filter data by Material Number, but the “Filter” option in the “Analysis Design” tab doesn’t meet their need. Because sometimes they want to filter data by using the asterisk (*). I know that we can use an alternative way, and it is using the „Filter by Member“ or „Background Filter“, but the problem with these two solutions is that the first one needs crosstab to right-click on its dimensions and choose “Filter by Member” option or select the “Filter by Member” from Analysis tab, and in the second one the end-user must go to “Design Panel” and use the “Background Filter”. Note that my dashboard doesn’t have any crosstab, indeed the crosstab is in a hidden tab in excel and the end-users just see the charts. When they filter the data by Material Number, they see the charts that related to that Material Numbers. Is there any way to filter data by wildcard using the “Filter” option in the “Analysis Design”? Best Regards, Amir

    Antworten
  8. Avatar von Tobias
    Tobias

    Hi Amir, did you try the Contains Pattern feature of your Prompt?

    Antworten
  9. Avatar von Amir
    Amir

    Thank you for your answer. I didn’t define any variable for the Material Number in my Data Source. So, I can’t use the Prompt option. You are right, if there is no way to filter data by wildcard using the “Filter” option in the “Analysis Design”, Contains Pattern feature of the Prompt is the only way for me to filter the Material Number by wildcard. But my question still remains, is there a way like using the VBA API to filter data by the wildcard?

    Antworten
  10. Avatar von Tobias
    Tobias

    Hi Amir, sorry I didn’t see that you want to use the VBA API. I just tried it with SAPSetFilter and SAPSetVariable but with both options did not work. When you look into the SAP Online Help for the syntax of values, you see A* but with a short note.

    Antworten
  11. Avatar von Amir
    Amir

    Hi Tobias, Thank you for your help. Maybe this feature becomes available in the next versions.

    Antworten

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Weitere Beiträge

reyemsaibot.com auf WordPress-Staging