SAP Design Studio and Conditions

Posted by Xavier Hacking

I got an email this week from a SAP BI developer about an issue with SAP BusinessObjects Design Studio. He is trying to filter a set of data in Top N categories, for example to get the 10 regions with the highest sales numbers (Top 10).

Unfortunately, in Design Studio we can only create filters on characteristics/dimensions (Vendor, Customer, Region) and not on key figures/measures (Sales numbers). In BEx Analyzer and SAP BusinessObjects Analysis the Conditions options does in fact provide this exact functionality, see the screenshot below.

DScond1

These Conditions can be defined in BEx Analyzer and Analysis, but can also be pre-defined in the BEx Query Designer. Luckily there is a way to use these pre-defined conditions in Design Studio and even change their condition value!

First we’ll have to go to BEx Query Designer and create a Condition in the query. Choose a key figure and an operator.

DScond2

Now instead of entering a fixed condition value, we’ll create an input variable. This is a value we can provide and change from within the Design Studio application! Make sure you choose the Processing By type Manual Input/Default Value. If you’re done, save the BEx Query.

DScond3

Next we go to the Design Studio application and add this BEx Query as a Data Source. In the example I assigned this Data Source to a Crosstab component. I also added a Dropdown Box component so we can make a selection from a list, to change the value of the condition. I created three selection items for this Dropdown Box (Properties view • Display • Items). Note that there is no value entered for the All selection item.

DScond4

Now it is time for some coding. Go to the Script Editor for the On Select property of the Dropdown Box component. We are going to use the following code:

APPLICATION.setVariableValueExt("ZIV_TOPN",
 DROPDOWN_1.getSelectedValue());

This code sets a new value for the variable ZIV_TOPN, with a value that is selected in the Dropdown Box component DROPDOWN_1. This variable value is used throughout the whole Application and applies to all Data Sources.

DScond5

When we execute the application, initially all records will be shown. When a selection is made in the Dropdown box, the Crosstab refreshes and shows only the selected number of top values. With the All selection item, in fact no real selection is made and all results are shown.

DScond6

By the way, you can also use the Prompt Dialog to enter (and change) the value of the Input Variable, but I thought this integrated solution with a Dropbox would be a nicer example.

So if anybody has a better solution/workaround or figures out how to use all the classic Condition features, please let me know!

HackingSAP.com - Jun 8, 2013 | Knowledge sharing, SAP BusinessObjects Design Studio
Tagged | , , ,

4 comments

  1. David Allison
    July 30, 2013

    Good post Xavier, spurred me on to checking out what if analysis in design studio using the same principles. Create a bex formula, add in a new input variable and then assign to a component (wish there was a slider component). Simple approach to a what if e.g. price increased by 10% how would this change revenue.

    twitter: david__allison

    Reply
  2. Lance
    July 24, 2014

    good post. unfortunately only works if using a formula variable. we have many bex conditions that do not use formula variables and we currently turn them on and off in Web Application Designer using dropdown boxes, similar to what you have demonstrated here.

    it would be great if the standard functionality of activating and de-activating an existing bex condition was available in Design studio, either through a component or at least in the API so that it can be easily triggered using the kind of script you have shown us here. this is currently holding us back from using design studio.

    Thanks for posting.

    Cheers,

    Lance

    Reply
  3. srikanth
    October 14, 2014

    Great Post Xavier .Thanks for the information.

    What if i need Top 10 for Two Measure say one measure is for SALES and Another is For Revenue.I also have two Queries defined in Bex for the same.How can we achieve this on selection on List box with measures as Sales and Revenue.

    Thanks and Regards,
    Srikanth

    Reply
  4. Anonymous
    February 6, 2018

    Love this! Who knew?! I’ve been in the industry for over 15 years and I had no idea. I’m tucking this nugget away for sure!

    Reply

Leave a Reply