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.
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!
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.