Technical Articles

Joyal Laj

August 24, 2020 2 minute read

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

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.

24 Comments

You must be Logged on to comment or reply to a post.

Srikanth Nalla

November 6, 2020 at 3:53 pm

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.

Jyothirmayee A

November 9, 2020 at 3:39 pm

Hi Srikanth,

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

Thanks,

Jothi

Srikanth Nalla

November 9, 2020 at 4:05 pm

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

Jyothirmayee A

November 9, 2020 at 4:23 pm

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

Srikanth Nalla

November 9, 2020 at 4:32 pm

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.

Jyothirmayee A

November 9, 2020 at 5:02 pm

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

Srikanth Nalla

November 9, 2020 at 5:43 pm

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.

Zsofia Sipos

November 27, 2020 at 9:53 am

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

Ivan Camac

December 1, 2021 at 2:56 am

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.

Konda Mohini

November 20, 2020 at 10:06 am

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?

Zsofia Sipos

November 27, 2020 at 9:32 am

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

Konda Mohini

November 30, 2020 at 2:14 am

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?

Zsofia Sipos

November 30, 2020 at 8:36 am

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

k deshmukh

February 15, 2021 at 7:15 am

Nice info. Thank you Sofi.

Maren Junghanß

November 26, 2020 at 2:24 pm

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.

Noel Munday

November 27, 2020 at 6:43 am

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.

Noel Munday

November 27, 2020 at 6:59 am

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.

Jef Baeyens

May 16, 2021 at 9:24 pm

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?

Maren Junghanß

November 27, 2020 at 10:20 am

Hi Noel,

many thanks. Now it works.

Regards, Maren

Noel Munday

November 27, 2020 at 11:01 am

Well done! You're welcome.

Anita Dhale

March 3, 2021 at 10:54 am

Thanks Joyal!

Alexander Blasl

July 16, 2021 at 1:54 pm

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%");

Arsen Georgyan

January 14, 2023 at 11:18 pm

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

Thayumanavan Swamynathan

August 1, 2023 at 5:28 am

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

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

Technical Articles

List the members of a dimension in a dropdown and filter table according to this dimension member – SAP Analytics Cloud

Scripting in SAP Analytics Cloud

Assigned Tags