List the members of a dimension in a dropdown and filter table according to this dimension member – SAP Analytics Cloud
Suppose we want to list the members of a dimension in the click of a dropdown in SAP Analytics Cloud (SAC). Is it possible to do that in SAC? If yes, How can we do this?
The answer is yes we can do this. How? We will see this in this blog post.
The second query is to filter a table according to the dimension member selected.
We can fill in the members of a dimension inside a dropdown and filter a table according to the selected dimension member using scripting in SAP Analytics Cloud.
Scripting in SAP Analytics Cloud
Scripting in SAP Analytics Cloud is possible only in Analytic Application in SAP Analytics Cloud. Scripting in SAP Analytics Cloud is a vast topic. Here we are only covering the scripting required for the topics mentioned in the blog post.
Step 1: Creating an Analytic Application : Click Create >> Click Analytic Application
Step 2 : Add the Widgets Drop Down and Table to the canvas
Step 3: While hovering above canvas, Click on fx to write script on initialization of the application so that we can load all members of dimension in the dropdown on initialization of the application.
Let’s see the Sample Code:
After saving this code and clicking the button Run Analytic Application, Members of the particular dimension will be listed in the Dropdown. Here I have listed all members in the Dimension Bike.
Here we haven’t used any styling. You can add it as your requirement.
Our next requirement is to filter the table according to the selected member in the dropdown. i.e here, if we select Bike A in dropdown, the table should be filtered with Bike Type Bike A.
Step 4: While hovering above Dropdown_1, Click on fx to write script onSelect().
Let’s see the Sample Code:
Now click on Run Analytic Application, you will find all our requirements satisfied.
Scripting in SAP Analytics Cloud is a vast topic. Here we have only covered the scripting required for the topics mentioned in the blog post. Hope all of you got a better understanding about the content mentioned in blog post. Please feel free to shoot out any mistakes in the blog post through your valuable comments.
Hello Joyal, thanks for the blog. I have a question, currently in my drop down I have to display SalesOrg values. There are more than 25 sales org in the master data. But there is only transaction available for 12 Sales Org.
When i try to assign the values from the query using the above logic, it displays all the 25 sales org but i want it to display only 12 sales org which has transaction.
Currently i have removed each of those unnecessary sales org by doing removeitem. But wondering if there is any other method to only read the Values that are in query but not the entire master data.
Storyboard does that very well actually, it only displays what's in the transaction data.
Did you tried uncheck "Unbooked Values" of Sales Org in Designer?. That should only show posted values.
Hello Jyothi, would you be able to tell me where exactly will i find this "Unbooked Values" checkmark.
If you go to Designer table /chart properties and select the Dimension (Rows/Cols) and click on "...", you should see Unbooked option. Let me know if that works..
Hello Jyothy, not sure if my initial question was clear.
I am trying to assign values of Sales org to drop down but not to a chart. I am aware of the unbooked data option in chart, when i saw your reply i thought there is a similar option in drop down too and somehow i couldn't find it.
Hope i am clear now in what i am trying to achieve.
What is your dropdown based on?. Model variable/Story filters or Scripted.
I did used filters within story for dropdown and the option to show unbooked/booked as per selection worked.
Not sure of script yet. I'm still exploring. Please do share if you come across a solution. It will be helpful.
Neither, i am developing an Analytics Application designer dashboard. Not a storyboard. In onInitialization i wrote similar code which joyal mentioned in this article to populate members into the drop down.
Could you help me please, how did you remove the items without transaction? I have the same problem. In SAP Lumira was an option "Only values with Posted Data" in the data source dimension settings.
Thanks for your help,
If you have not resolved this already, I believe your answer lies in the optional parameters for getMembers. By default, getMembers retrieve just the first 200 values and it does this based on a master data read. You can override both these defaults with additional parameters.
will return the first 200 members for "Dimension_name", with the read occurring from Master Data.
The above however, will return up to 20,000 members (which will impact performance) and the read will happen from posted values in the provider.
Are there any limitations on the addition of dimension list of values to dropdown?
Currently, I have a dimension which holds around 1500 list of values.
When I try to add to dropdown, it is just showing up only 200 rows.
Could you please help me out?
It is probably because the ChartName.getDataSource().getMembers("DimensionName") function has a 2nd property maximum number, which is optional. This property has a default value as 200, if you want more item, you should fill this property with 1500 like ChartName.getDataSource().getMembers("DimensionName", 1500) .
It worked, but now it's giving performance issues. It's taking almost 2 mins for loading 2500 results to the dropdown.
Is there any solution for this?
Maybe if you can filter with a different dimension, and load this dimension filter regarding the other. But yes, it can build slowly if it has more elements.
Nice info. Thank you Sofi.
we get an similar issue if we try to generate new records in a dimension. We would like to get the new number automatically, but after 200 is finished.
It seams it is a bug in SAC.
No I don't think it is a bug.
The default number of records to read is 200 but you can increase this number. There is an optional read parameter not used in the above example and if you leave the option out you get the first 200 records.
Have a look at the API reference guide for DATASOURCE for the detail explanation.
If you are doing this on a planning model then take a look at using the PlanningModel methods. The second parameter for the .getMembers() is also optional but it has a wider range of parameters which means you can use offsets to read your member data. This is useful, for example, if you had potentially unlimited number of master data records but only want to read a small number at a time for your drop-down box, say 50 at a time. Then you can read the first 50 and give the user a "next" button to read the next 50. Using the "offset" parameter you can remember that the user has already loaded the first 50 members into the drop-down box so your next .getMembers() call must go from offset 50 and so on. You can increase the number of records you read at each time but the default is 200 if nothing is specified.
API Reference for PlanningModel.
Hi Noel, typically users don't like to click through lists of members. Why is it still not possible yet to limit members based on properties as part of getmembers() API or to perform a fuzzy search to retrieve matching members?
many thanks. Now it works.
Well done! You're welcome.
Hi Joyal Laj ,
can you add Members in a dropdown the start with certain letters (A%). Like with a where/like clause?