Skip to Content
Technical Articles
Author's profile photo Joyal Laj

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

Creating%20an%20Analytic%20Application

Step 2 : Add the Widgets Drop Down and Table to the canvas

Analytic%20Application%20Initial%20Screen

Analytic Application Layout

Analytic%20Application%20Canvas%20After%20adding%20Table%20and%20Dropdown

Analytic Application Canvas After adding Table and Dropdown

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:

Sample%20Code

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.

Now let’s add the dimensions in the Table and Label to the Dropdown.

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:

Sample%20Code

Sample Code

Now click on Run Analytic Application, you will find all our requirements satisfied.

Conclusion:

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.

Assigned Tags

      24 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Srikanth Nalla
      Srikanth Nalla

      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.

      Author's profile photo Jyothirmayee A
      Jyothirmayee A

      Hi Srikanth,

      Did you tried uncheck "Unbooked Values" of Sales Org in Designer?. That should only show posted values.

      Thanks,

      Jothi

      Author's profile photo Srikanth Nalla
      Srikanth Nalla

      Hello Jyothi, would you be able to tell me where exactly will i find this "Unbooked Values" checkmark.

      Author's profile photo Jyothirmayee A
      Jyothirmayee A

      Hi Srikanth,

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

      Thanks,

      Jothi

       

      Author's profile photo Srikanth Nalla
      Srikanth Nalla

      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.

      Author's profile photo Jyothirmayee A
      Jyothirmayee A

      Got it!,

      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.

      Thanks,

      Jothi

      Author's profile photo Srikanth Nalla
      Srikanth Nalla

      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.

       

      Author's profile photo Zsofia Sipos
      Zsofia Sipos

      Hi,

       

      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,

      Sofi

      Author's profile photo Ivan Camac
      Ivan Camac

      Hi Srikanth,

      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.

      For example:

      Table_1.getDataSource().getMembers("Dimension_name");

      will return the first 200 members for "Dimension_name", with the read occurring from Master Data.

      Table_1.getDataSource().getMembers("Dimension_name",{
      	limit:20000,
      	accessMode:MemberAccessMode.BookedValues});

      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.

      Cheers, Ivan.

      Author's profile photo Konda Mohini
      Konda Mohini

      Hi Joyal,

      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?

      Author's profile photo Zsofia Sipos
      Zsofia Sipos

      Hi,

       

      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) .

       

      Regards,

      Sofi

      Author's profile photo Konda Mohini
      Konda Mohini

      Thanks Sofi.

       

      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?

      Author's profile photo Zsofia Sipos
      Zsofia Sipos

      Hi,

       

      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.

      Sofi

      Author's profile photo k deshmukh
      k deshmukh

      Nice info. Thank you Sofi.

      Author's profile photo Maren Junghanß
      Maren Junghanß

      Hi Joyal,

      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.

      Author's profile photo Noel Munday
      Noel Munday

      Hi,

      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.

      getMembers(dimension: string | DimensionInfo, maximumNumber?: integer): MemberInfo[]

      Have a look at the API reference guide for DATASOURCE for the detail explanation.

      Regards,

      Noel.

      Author's profile photo Noel Munday
      Noel Munday

      Hi,

      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.

      getMembers(dimensionId: string, options?: PlanningModelMemberOptions JSON): PlanningModelMember[]

      API Reference for PlanningModel.

      Regards,

      Noel.

      Author's profile photo Jef Baeyens
      Jef Baeyens

      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?

      Author's profile photo Maren Junghanß
      Maren Junghanß

      Hi Noel,

       

      many thanks. Now it works.

      Regards, Maren

      Author's profile photo Noel Munday
      Noel Munday

      Well done! You're welcome.

      Author's profile photo Anita Dhale
      Anita Dhale

      Thanks Joyal!

      Author's profile photo Alexander Blasl
      Alexander Blasl

      Hi Joyal Laj ,

      can you add Members in a dropdown the start with certain letters (A%). Like with a where/like clause?

      Chart_1.getDataSource().getMembers("Dimension", "A%");

      br

      Author's profile photo Arsen Georgyan
      Arsen Georgyan

      Hello,

       

      I want to know , wenn you add with method Table.getDataSource().getMembers("Dimension", Filterlimit);  all Members in Dropdown,  is it possible to choose more as on elements in Dropdown for change the result?? If someone can help, please write how it is possible with javascriptcode??

       

      Thanks,

      Arsen Georgyan

      Author's profile photo Thayumanavan Swamynathan
      Thayumanavan Swamynathan

      Can you Please kindly help me to sort out this issue.

      scenario is I have two input filter and dropdown

      Input Controls are below

      1. Customer Region : has the following members ACT,NQ,NSW,NT,QLD,SA,TAS,VIC,WA

      2. Business Region : has the following members AUNSW,AUNT,AUQLD,AUSA,AUVIC,AUWA,NZ_LOCAL,#(unassigned)

      DropDown

      image.png

      I have written the sac script when user click on JH States it should show Business Region input control and at the same time it create a color dimension. Same scenario if the user click on customer region

      Please find the script which I have used it on dropdown - Onclick

      if(Dropdown_5.getSelectedKey() === 'JHStates')

      {

      InputControl_9.setVisible(true);

      InputControl_6.setVisible(false);
      Chart_18.removeDimension("ZCPFREGN",Feed.Color); ZCPFREGN - Customer Region

      Chart_18.addDimension("ZSDH2",Feed.Color); ZSDH2 - JH Sates

      }

      if(Dropdown_5.getSelectedKey() === 'CustomerRegion' )

      {

      InputControl_6.setVisible(true);

      InputControl_9.setVisible(false);

      Chart_18.removeDimension("ZSDH2",Feed.Color);

      Chart_18.addDimension("ZCPFREGN",Feed.Color);

      }

      Once I change the dropdown data is displaying for only 1 year with the below warning

      "Your data selection has too many values. Auto limit has been applied to your chart to display the values in the order you set. use filters to reduce the number of displayed values".

      Thanks,
      Thayu