Interval selection via BICS connection
Is it possible to use interval selection on an object (i.e. month) using the standard SAP BICS connection? One of the solutions is to use the component “List Builder”. In this scenario you can only add individual months to your selection. End-users need to make simple selections with a minimum of mouse-clicks.
Another possible option is to use the “Range Slider” option in a chart. Adjusting the slider will cause i.e. the sales to increase or decrease, which is visualized in the chart. A disadvantage of this option is that it only works for only one chart. In this case we need the functionality of an interval selection which applies to N charts.
Together with my colleague Jeffrey La Grand we found a solution. We designed a simple SAP BEX Query between the dashboard and the SAP BW system. We didn’t use any variables because it restricts the complete dashboard to the selected variables. By executing the dashboard, the standard portal screen with variable selections appears. We think users need to select their variables values in the dashboard itself.
Below you’ll find our solution. Via the dropdown menu’s you are able to select the interval of months.
With this solution it’s possible to restrict the data (via BICS connection) between two months.
You can download the source files. I can’t show the SWF-file, because there is no connection to the SAP BW system.
Update 4/4/2011:
I investigated more time on the Interval Selection since Ingo Hilgefort post a comment on my website. Ingo had any comment on certain points in my article. One of the comments was, the use of the internal key of the date. I changed the internal key to the normal key for performance reasons. Now there is no need to change the date format by a formula in MS Excel.
A second comment of Ingo was to use the SAP BEx variables. In cooperation with my colleague Xavier Hacking I spend some time to find a good solution to use the variables in the right way to use.
The solution is very simple!
When the date-from or date-to has been selected in the dropdown menu, the values will be written to cell B2 and B3. Cell B5 contains the following formula: “=CONCATENATE(B2;” – “;B3)”. This is necessary to post the value to the not mandatory Date variable (see next screenshot). The “–” character need to be used for the interval value!
Bind cell B5 to the Date interval variable.
When we will launch the dashboard, the selected interval value will be posted to the SAP BEx variable. The result is that all graphics (that are binded to the transaction data) will be updated to the selected months. In my opinion this scenario is a standard scenario that can be implemented by a lot of customers.
For those who want’s to download the new XLF file, click here.
Hello Pieter,
in the blog you mentioned that a variable in the underlying BEx query would limit the complete data set and would prompt automatically, which is not 100% accurate.
First of all the usage of BEx variables is recommended as the variable is offering more capabilities – for example being able to limit the list of values based on user authorizations.
A variable in the BEx query will only prompt the user using the BEx variable dialog when the prompt is mandatory and the value has not been filled in the Xcelsius dashboard.
In addition the variable will only limit the data from the one connection it applied to and not the data from the complete dashboard.
And last but not least the variable used in the BEx query and the corresponding list of values which then also is part of the connection will guarantee the best performance because in case you are sending a value like shown “200802” to the underlying BW system it needs to be translated to the technical name, which the variable has automatically as part of the provided values.
so in short – the recommended approach is to use the variables as part of the BEx query.
regards
Ingo Hilgefort
Hi Ingo,
Thanks for your response, I agree with your comment. But I have a question about the BEx variables. In my query I’m using a non-mandatory interval BEx variable on the object 0CALMONTH.
My dashboard has two connections to BW, one connection for getting the months and one for the transactional data. How can I fill the variable in the Data Manager with an interval value? I tried to select two cells but it doesn’t work. What’s the best practice for this scenario?
Regards,
Pieter
Hi,
there is no need – assuming you use the BI CS connection – to use two connection as the connection provides you input and output areas. the output area also includes access to the list of values and the input area includes the variables and the filter.
Ingo
Hi guys…
Can you help us with an issue we’re facing?
We have a Dashboard using BICS Connection, our queries have mandatory variables and we’re “filling” those values trough the dashboard, but the prompt still appearing and our user doesn’t want it. The big issue is that this only happes in our Productive environment in QAS doesn’t appears this prompt.
Any suggestion?
Hi Liz,
This is a very strange issue. Are the QAS and Production on the same SP level?
I recommend to create a message by SAP support.
Another option will maybe work: try to use the optional variables? Did the prompt still pop up?
Regards,
Pieter