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.
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.
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.
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.
After you click "OK" the members are added to the filter.
By click OK, the filter is applied.
By click OK, the filter is applied to the prompt dialog.
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:
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.
Write a comment
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!!
Tobias (Thursday, 28 July 2016 09:14)
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.
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 (Thursday, 28 July 2016 09:15)
Here is the screenshot a little bit bigger
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!
Pascal (Monday, 26 November 2018 11:10)
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?
Tobias (Monday, 26 November 2018 20:38)
the members are each in a new line. It can be a txt-file or a csv-file. I hope this will help you.
Amir (Monday, 20 July 2020 11:26)
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”?
Tobias (Monday, 20 July 2020 15:33)
did you try the Contains Pattern feature of your Prompt?
Amir (Wednesday, 22 July 2020 05:25)
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?
Tobias (Friday, 24 July 2020 08:38)
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.
Amir (Saturday, 25 July 2020 08:35)
Thank you for your help. Maybe this feature becomes available in the next versions.