Dynamic Day Selection For a Component

[Former Member]

Author(s): Anil Kumar Puranam

Company: Axiall

Created on: 14th August, 2015

Authors Bio:

Anil Kumar Puranam is working as a Senior BW/BI Developer. He has more than 10 years of SAP BW/BI/HANA experience.

Business Scenario:

     In BI implementations, We often see that Business is interested to see very recent data as the default view before they jump into other details or going to see aggregated data in DEsign Studio applications . I recently got into similar sittuation where there is a requirement to show the previous day's Sales analysis as the inital display in the cross tab in one of the tab page of the report  And I am asked to provide Filter to select date range to jump to aggregated view or jump to anohther day post to the inital display.

Technical Challenge:

The Datasources in the Design Studio are built on top of the BEx queries. In the Bex Queries We can use the SAP EXIT variables to filter on the Date to show only the previoud day sales. However we need to provide the option to user to select any other dates after the intial display. The filter on BEx Query limits the number of possible vlaues in the Dimenstion filter.

Approach1  to overcome the Challenge: By using 2 BexQueries

We can use below methodology to overcome the challenge:

Step 1: Create 2 BEX queries. One with out filter on DATE and another one with filter on Calday. In the second Query , where we want to have the filter , we can use SAP EXIT variables. Here we have used 0DAT SAP EXIT variable with offset -1, to get previous day since this variable always gives us the Current date.

Query1 : With out filter

Query 2: With filter:

Variable properties:

Step 2: In the Design Studio, let us create 2 Datasources , DS_1 on top of BEx Query 1 and DS_2 is on top of the BEx Query 2.

Step 3 : In the application let us take one Dimension filter and one Cross table. Define DS_1 as the source for Dimension Filter and write the statment  for "on Apply" Event, as shown in the below screen shot. This script will help us to change the source of the cross tab dynamically once we select any filter on Calday from Dimension Filter. And Define DS_2 as the source for Cross Tab.

Properties of Dimension filter:

Statement in "On Apply" event:

Cross Tab Properties:

Once we run the report , at the intial display we can see data in the cross tab is only for Previous date and once we select different dates from Dimension filter , we can see Cross tab data gets changed.

Intial Display ( we  have not laoded any data for previous day, so we can see blank cross tab)

We can select any date from dimension filter and start seeing data for those days in the Table.

Approach2  to overcome the Challenge: By using Script

We can use below methodology to overcome the challenge with the script at startup of the application. Here do not need 2 BEx Quries. We can get the expected behaviour by defining the 2 datasources on top of one BEx Query.

Step 1: In the Design Studio, let us create 2 Datasources  on top of one BEx Query.

Step 2 : In the application let us take one Dimension filter and one Cross table. Define DS_1 as the source for Dimension Filter as shown in the below screen shot. And deine DS_2 as the source for Cross tab. Here we can TEXT Component as for testing the values that we derive in the Script for the date.

Step 3: Write a script on the "on the Startup" event of Application. The code is attached to this document. This script will help us to derive the previous date and use the previous date to make a fileter on the DATE field of DS_2 datasource. So this will make sure we see only previous date values in the inital display of the cross tab.

note: For testing I have used July 08 2015 as the current date.

The inital display (by treating July 08 2015 as the current date).