DropDown filter limiation in SAP BO design studio

[Former Member]

Hi,

I'm facing an issue while designing my application in design studio 1.1

The scenario is that I've to restrict the memberlist of one drop down box based on the other drop down value.

For ex. if dropdown_1 is for Route_No and dropdown_2 is for all BMTC bus. Based on the Route_No selected in dropdown_1, memberlist of

dropdown_2 should be restricted.

I have tried filtering the datasource and then using getMemberlist method of data source. But it seems, getmemberList method works on the initial view

of data source and not on the intermediate filter.

Any suggestion??

Does it actually a limiltation to have this behavior??

Thanks and Regards

Deepti Chourey

[Former Member]

Hi All,

The cascading filters works perfectly fine for Hana Data sources in Design Studio 1.3. I have tested this in my DS 1.3 and it works.

Thanks

Sambit

[Former Member]

Hi Sambit,

Can you please elaborate on how you implemented the cascading filters on HANA views.

Thanks.

[Former Member]

Please follow this scn tutorial : http://scn.sap.com/docs/DOC-33798.

In this example it is shown for BW data sources, but It works perfectly fine with Hana Datasources.

[Former Member]

Hi Sambit,

The above tutorial works good when you are using a BW data source. But since my data source is a hana view, its not working. I tried the exact way how it is explained in the tutorial but doesn't work.

I even tried the workaround posted in this thread http://scn.sap.com/community/businessobjects-design-studio/blog/2014/05/29/how-to-create-multiple-dr...

That doesn't work too

[former_member199763]

Cascading works for Hana Data sources  if we,

Enable 'Only Values With Posted Data' in Edit Initial View on the Dimension where Cascading needs to effected.

• Drag Dimension to either Row or Column in Edit Initial View
• Right Click on the Dimension and select --> Members for Filtering --> Only Values With Posted Data

[Former Member]

Hi Ramkumar,

This solved my issue. Thanks a lot man.

[vaibhav_rathore]

Hi,

Else you can do it by not posting the STARTUP script for the second dropdown and you can do it same by calling it in on ON SELECT EVENT of dropdown_1 as below:

DS_1.setFilter("ROUTE_NO",DROPDOWN_1.getSelectedValue());

DROPDOWN_2.setItems(DS_2.getMemberList("BUSES", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 20,"ALL"));

Regards

Vaibhav

[Former Member]

Hi,

Are you using HANA as a source of your application? We are experiencing similar problems with cascading filters using HANA.

Regards,

Sjoerd

[Former Member]

Hi Sjoerd,

I'm using HANA as source.According to my knowledge,Cascading filters with BW as source can be implemented.

[former_member131154]

Hi,

I can confirm that cascading filters like the mentioned scenario with the dropdown boxes definitely work with BW as source. I used that in 1.0 and 1.1 already.

Regards

Dirk

[Former Member]

With BW: yes. With HANA (non-BW): Not right now, seems to be a bug.

[bharathsap]

Hello All,

Cascading filters is not currently supported for HANA datasources. Works only for BW sources. This is a limitation and not a bug.

This is already in our future plans to implement it. Most probably in the next release

Regards,

Bharath

[Former Member]

Hi,

Does the Cascading filters is supported for HANA data-sources for Design Studio 1.3.

If not is there any development request or bug registered for the same?

Thanks

Sambit

[MustafaBensan]

Hi Sambit,

As per Bharath's comment,  cascading filters are not currently supported for HANA datasources but the functionality is planned for a future release of Design Studio.

Regards,

Mustafa.

[Former Member]

Hi Bharath,

Do you know the reason for why cascading is not supported using HANA views?

Do you know if there Is there any workaround method to achieve this ?

[former_member45129]

Hello Bharath,

We are facing the similar issue with Teradata as Datasource for achieving the Cascading Filters with dropdowns.

Is the option provided in Design Studio 1.4 ?

I have a query : Once we filtered the Datasource using dropdown1 how come the dropdown2 is showing all the values.

Basically how its works ?

Please provide your inputs

Thanks,

Selva

[bharathsap]

Hi Selvarasan,

You can refer this tutorial to learn how cascading filter works http://scn.sap.com/docs/DOC-33798.

To be honest, I never had a chance to test this functionality with Terradata.

But, in general cascading filter support is available for UNV datasource as well.

If you follow the steps mentioned in the tutorial and not able to use cascading filter functionality, then let us know. We can have a look and get back to you.

Regards,

Bharath

[former_member45129]

Bharath,

Thanks for your reply.

We tried exactly same like tutorial. But facing the issue.

The data source is filtering correctly but dropdown are not Filtering at all.

Will you please let me know is there any limitation with Universe/ NON SAP Backend ?

Please see below link for reference.

http://scn.sap.com/community/businessobjects-design-studio/blog/2014/05/29/how-to-create-multiple-dr...

Thanks,

Selva

[bharathsap]

Hi Selva,

I tried this with SQL server Universe and the cascading worked like charm.

I don't have Terradata db. So I can't check it. But, I don't think there is any limitation mentioned in the release note. So, I expect this to work.

Could you share a representative scripts that you use for the dropdown --> On select event?

I can check if there is anything missing.

Regards,

Bharath

[former_member45129]

Hi Bharath,

Please see below script.

Note :

Dropdown1 - REG

Dropdown2 - ZONE

Dropdown3 - DIST

DS_1 - DataSource

->On startup Loading the datasource and settting the Lov's

REG.setItems(DS_1.getMemberList("_msctYbHhEeSFQuv7joOFog", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50));

->on select of Dropdown1

DS_1.setFilter("_msctYbHhEeSFQuv7joOFog", REG.getSelectedValue());

ZONE.setItems(DS_1.getMemberList("_DFZPcaebEeS9c5MXajofSA", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT,50));

->On select of Dropdown2

DS_1.setFilter("_DFZPcaebEeS9c5MXajofSA", ZONE.getSelectedValue());

DIST.setItems(DS_1.getMemberList("_tcijkaeNEeS9c5MXajofSA", MemberPresentation.INTERNAL_KEY, MemberDisplay.TEXT, 50));

Thanks,

Selva

[bharathsap]

Hi Selva,

On the first glance your script looks ok.

Could you confirm if you have configured the members to display posted values on filtering in edit initial view?

This is very important. If the member filters are showing fact table/master data values then cascading won't work.

The setting should be as shown in the attached image for all the dimensions involved in cascading.

Regards,

Bharath

[former_member45129]

Hi Bharath,

Thanks for your reply.

I am not able to see that option(Members for filtering) itself

Please see attached file for reference.

Thanks,

Selva

[bharathsap]

Hi Selva,

Which version of Design Studio are you working with?

Terradata support came with 1.4 release.

Regards,

Bharath

[former_member45129]

Hi Bharath,

We are using Design Studio 1.4 release(32 Bit version)

But am not able to see the option

Thanks,

Selva

[bharathsap]

Hi Selva,

Is your universe single source (UNX)?

Multisource universe is not supported currently.

If it is single source, then I'm running out of ideas.

Regards,

Bharath

[former_member45129]

Hi Bharath,

Its single source universe only

Regards,

Selva

[vaibhav_rathore]

Hi Bharath,

You are not able to see the "Members Of Filtering" option because it is visible only on universes based on OLAP connection or HANA views.

I have been using OLAP unx in DS and HANA views on which that option is visible.

In case of universes based on non OLAP connections you can refer the link:

hope this helps

Regards

Vaibhav

[Former Member]

That's certainly the issue. Thanks Bharath.

[Former Member]

Hi Selva,

   Did you achieve cascading filters by using multiple dropdown boxes using universe as data source?

[Former Member]

Hi Swathi

We have achieved cascading filters by using multiple drop down boxes using universe as a data source.

We had one scenario like below. We have 3 drop downs (Division, Region and Zone).

Based on the selection of Division, the corresponding Region should get filtered. And then on the selection of Region, Zone should get filtered.

To achieve the above scenario, first we need to create a concatenated object of Division, Region and Zone with some symbols in universe. We have created like this (Division$$Region$$$Zone). Create one data source using that concatenated object in Design Studio.

Follow the script as follows:

On Startup for Dropdown 1:

DROPDOWN_REG.removeAllItems();

if(!DS_1.isInitialized())

{     
DS_1.loadDataSource();

var vHir = DS_1.getMembers("_nneOcLh0EeSzHt_IHGbPhQ", 5000);

DROPDOWN_REG.addItem("Please Select Region","Please Select Region",0);

var reg_idx =1 ;

vHir.forEach(function(element, index) {           

DROPDOWN_REG.addItem(element.text.substring(0,element.text.indexOf('$$')),
element.text.substring(0,element.text.indexOf('$$')),reg_idx);   
reg_idx = reg_idx +1 ;

}); 

}

On Select of Region Dropdown:

var vRegionSelected = DROPDOWN_REG.getSelectedValue();

DROPDOWN_ZONE.removeAllItems();

DROPDOWN_ZONE.addItem("Please Select Zone","Please Select Zone",0);

var vHir1 = DS_1.getMembers("_nneOcLh0EeSzHt_IHGbPhQ", 5000);

var zon_idx =1 ;

vHir1.forEach(function(element, index) {

if(element.text.indexOf(vRegionSelected)>=0)

{         
DROPDOWN_ZONE.addItem(element.text.substring(element.text.indexOf('$$')+2
,element.text.indexOf('$$$')), element.text.substring(element.text.indexOf('$$')+2,element.text.indexOf('$$$')),zon_idx);          
}       
zon_idx = zon_idx +1 ;

});

On Select of Region Dropdown:

var vZoneSelected = DROPDOWN_ZONE.getSelectedValue();

DROPDOWN_DIST.removeAllItems();

DROPDOWN_DIST.addItem("Please Select District","Please Select District",0);

var vHir2 = DS_1.getMembers("_nneOcLh0EeSzHt_IHGbPhQ", 5000);

var dis_idx =1 ;
vHir2.forEach(function(element, index) {

if(element.text.indexOf(vZoneSelected)>=0)         
{           
DROPDOWN_DIST.addItem(element.text.substring(element.text.indexOf('$$$')+3 ),
element.text.substring(element.text.indexOf('$$$')+3 ),dis_idx);         
}       
dis_idx = dis_idx +1 ;

});

Hope it will help:)

Regards,

Amala.S

[Former Member]

Hi Amala,

    Is it like the first set of code should be put in ON STARTUP of application, second set of code for ON SELECT of dropdown1 and third set of code for ON SELECT of dropdown 2?

Also what is the level you have used? Is it Region -> Zone -> Dist?

Regards,

Swathi

[Former Member]

Hi Swati

Yes...We need to write the code as below

First set of code -- On Starup

Second Set of Code -- On select of dropdown 1

Third Set of Code -- On Select of Dropdown 2

Level --> Division, Region, Zone.

Division would be the dropdown 1

Region would be the dropdown 2

Zone would be the dropdown 3.

Regards,

Amala.S

[Former Member]

Hi Amala,

    I would like to know whether this is the order?

   DROPDOWN_REG = Dropdown_1 = REGION

   DROPDOWN_ZONE = DROPDOWN_2 = ZONE

   DROPDOWN_DIST = DROPDOWN_3 = DIST

Regards,

Swathi

[Former Member]

Yes Swathi...Sorry

Have updated wrongly in my previous post.

Level --> Region, Zone, District

DROPDOWN_REG --> Dropdown 1

DROPDOWN_ZONE --> Dropdown 2

DROPDOWN_DIST --> Dropdown 3

Regards,

Amala.S

[Former Member]

Hi Amala,

    Thank you so much. Cascading works for the dropdown.

I have one more question. I would like to see the effect in a crosstab. Can you please share the code to see the effect of dropdown on the crosstab?

Regards,

Swathi Rai B

[Former Member]

Hi Swathi

On the selection of drop down pass the selected values to the queries and map the query result to Cross tab.

Regards,

Amala.S

[Former Member]

Hi Bharath,

Was going through this post and my problem is exactly the same. I have 2 datasources DS_FORECAST and DS_CHAR. I fill the DropDown_1 by Category value. When DropDown_1 is selected by the user in the onclick of DropDown_1 i write the below code but the cascading doesn't work.

PRODH1 --> Category which we are selecting in 1st dropdown.

Z_CASEUPC --> Is the Material which we want to restrict in DropDown_2 based on 1st DD_1 selection.

DS_CHAR.clearFilter("0PRODH1");

if (DROPDOWN_CAT.getSelectedValue() != "ALL")

{

  DS_CHAR.setFilter("0PRODH1",DROPDOWN_CAT.getSelectedValue());

  LISTBOX_1.removeAllItems();

  LISTBOX_1.setItems(DS_CHAR.getMemberList("Z_CASEUPC",MemberPresentation.EXTERNAL_KEY,MemberDisplay.TEXT_KEY,100));

}

I am doing this in DS 1.4 and its connected to a BW query. Please suggest how to resolve this based on your previous experience ?