Cascading filter in SAP Design Studio/Lumira Designer 2.0 with universe source using crosstabs
Cascading filters are important for a guided dashboard as it provide better usability and user experience to the dashboard. We have a known limitation with universe source to achieve it in Design Studio. Vijay Bhaskar P and Former Member have already published a useful workaround by keeping optional prompt in data source and setting application filter for each selection user makes. Find below links to mentioned blogs.
https://blogs.sap.com/2014/09/30/create-cascading-dropdowns-filters-using-universe-as-datasource/
Recently we had a similar requirement and I tried above mentioned option. But for some reason, We were not getting the expected performance for the user selections. So as an alternative, We made a selection screen using crosstab component instead of usual selector components.
How to use crosstab instead of normal selection components ?
Below example uses dummy data to show this functionality. define smaller data sources to populate selection crosstabs. These data sources can be filtered by setFilter() function just like in a normal scenario with values selected in crosstab. Provide setFilter() function in the hierarchical order as required in the “on click” event of each crosstab. Based on requirement crosstab property can be set to “single” selection or “multi” selection.
Below example has 4 crosstabs and 4 smaller queries feeding each crosstabs.
User experience
crosstabs can be presented as a list box by modifying css properties of standard classes. So it is hidden from the user that we are using a crosstab as selector. Additionally, it provides number of rows while scrolling through the values which will be useful from a user perspective.
Let’s see it in the sap website theme here.
I am not sure if Lumira Designer 2.0 has a fix to overcome the limitation of cascading filters with unx source. If someone has an update on it, please post it as a comment.
I welcome your feedback, suggestions and alternative ideas on this ?
Regards,
Nikhil Joy
Hi Nikhil,
Can you clarify how the use of a Crosstab actually fixes the cascading filter issue for UNX universes? It is not very obvious from the example you've provided.
In Lumira 2.0, you can achieve cascading filters for universes by first creating an acquired data set based on the universe in Discovery and then creating an Offline Data Source in Designer based on the acquired data set. As long as you set the Members for Filtering option of each filter dimension to "Only Values with Posted Data" in the data source Initial View, the cascading filters should work as expected using setFilter() or data binding, without the need for any prompts.
Regards,
Mustafa.
Hello Mustafa,
Thank you for your response. Unlike BEx data souces, when we use UNX universe and try to achieve cascading prompt, re-populating second selection list after a setFilter() operation on second data source was not working. As a workaround, we can give APPLICATION level filter from first selection and re-populate second selection component (explained in 2 links I have mentioned).
In my example, I am not using any selection component or setItems() function. I am directly mapping my smaller data sources to 4 crosstabs and use setFilter() function on these data sources in the hierarchical order (Region->Country->City->Product in this example). So basically I avoid re-populating selection components for each selection. Response was better in my use case with this option.
I hope I could explain it in a better way.
Let me know your feedback.
Regards,
Nikhil Joy
Hi Nikhil,
Okay, that makes sense now. You are taking advantage of the result set filtering associated with a Crosstab data source which maintains cascading filters, unlike the approach of applying data binding or setFilter() via getMembers() which does not work for universes.
You can also try the Lumira Designer 2.0 approach based on an offline data source as I have described above to overcome the current limitation exhibited in DS 1.6.
Regards,
Mustafa.
Hi Mustafa,
you are right.
I will definitely try your suggestion in Lumira Designer 2.0 as soon as I get the set up ready.
Regards,
Nikhil Joy
Hello Mustafa,
Seems like you have dealt with the similar issues as we currently are in .
We are in a similar issue from few days,Let me explain you the whole requirement.
We have HANA Calculation view as the source for this Dash board.Few thing s i would like to bring to you point wise are given below.
This report has to populate the data based on the user ID .For testing we want 7 filters .
1.UserID : User name
2. User Designation ( Like AM – Account manager,District manager (DM) like wise)
3.WW Region ( ASIA PACIFIC ,NA – North america and so on ……
4.Country ( Countries which fall under each WW region)
5.Region ( US EAST,US WEST adn so on based on the Country)
6.AREA ( Newjersy , Chicago and so on…. Based on the above Region )
7. Teritory ( This is the lowest level of the data )
Based on the Above selection flow then the Product would be aggregated and show in the Dashboard.
Now the issue is while trying to use the Drop down at some level that is not picking up the results .
like if i select the country as US the next DD shows all the countries.
and the second issue with using the Drop down is that we are not allowed to select the multiple locations.
Can you please give some useful suggestion and a Sample script for the filters which we can implement for out requirement.
thanks in advance .
Chandra.
Hi Chandra,
Since this is a very specific and detailed question, as per the SCN Rules of Engagement, you should post this as a separate question for better community visibility and feedback.
That being said, the cascading filter limitation described in this blog only applies to universe data sources. Cascading filters should work out-of-the-box for HANA Views. When you post your new question, you should describe in detail the script coding and any data binding you have implemented for the cascading filter functionality.
Regards,
Mustafa.
Hi Mustafa,
I have one scenario where I have to merge(union) two datasets. Please have a look at this link ---- https://answers.sap.com/questions/505983/merging-two-crosstabs-in-lumira-20-designer.html
I haven't got any solution to this. Please help.
Thanks in advance
Hi Manoj,
As per the Rules of Engagement, please post a new question with your specific problem rather than posting it as a comment to an unrelated blog.
Regarding the link you have referenced, it seems you have already accepted the provided answer. If you have a new problem then this should be posted as a separate question.
Thanks,
Mustafa.
Hello Nikhil,
Seems like you have dealt with the similar issues as we currently are in .
We are in a similar issue from few days,Let me explain you the whole requirement.
We have HANA Calculation view as the source for this Dash board.Few thing s i would like to bring to you point wise are given below.
This report has to populate the data based on the user ID .For testing we want 7 filters .
1.UserID : User name
2. User Designation ( Like AM - Account manager,District manager (DM) like wise)
3.WW Region ( ASIA PACIFIC ,NA - North america and so on ......
4.Country ( Countries which fall under each WW region)
5.Region ( US EAST,US WEST adn so on based on the Country)
6.AREA ( Newjersy , Chicago and so on.... Based on the above Region )
7. Teritory ( This is the lowest level of the data )
Based on the Above selection flow then the Product would be aggregated and show in the Dashboard.
Now the issue is while trying to use the Drop down at some level that is not picking up the results .
like if i select the country as US the next DD shows all the countries.
and the second issue with using the Drop down is that we are not allowed to select the multiple locations.
Can you please give some useful suggestion and a Sample script for the filters which we can implement for out requirement.
thanks in advance .
Chandra.
Hello Chandra,
As Mustafa mentioned, you can post a new question so that you will get more response.
Regarding your query, if you are using universe from HANA calc view, it is a known limitation and you can try the option mentioned in the links I have given in the blog. Else you can use crosstabs as a selection component like I have shown in the example with css to format the crosstabs to look like a selector component.
Regards,
Nikhil Joy
Thanks Nikhil and Mustafa ,
I will post this as a new question so that it will be addressed by more experts.
regards,
Chandra.
Hi Nikhil, this is one way to achieve cascading among filters.
Create a datasource, populate it with label( dimension) and a measure(any measure)
Now on click event of filter( of higher hierarchy ex- country filter) write down below script
CC_CHKBOX.removeAllItems();
var xx=DS.getMembers("dimension_id", 1000);-----here you can use any datasource
var val="v";
var count=0;
var data="";
xx.forEach(function(element, index)
{
data=COSTCENTER_CHKBOX.getDataAsString("measure_id", {"dimension_id":element.internalKey}); ---------here you have to use DS as in above image
if(data !="" )
{
CC_CHKBOX.addItem(val, element.text,count);
val=val+"v";
count=count+1;
}
else if(data == ""){ }
});
CC_CHKBOX.setSelectedValues("");
Hope this may work for you as it is working perfectly for me.