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

Capture 1.PNG

Query 2: With filter:

Capture 2.PNG

Variable properties:

Capture 3.PNG

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.

Capture 4.PNG

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:

Capture 5.PNG

Statement in “On Apply” event:

Capture 6.PNG

Cross Tab Properties:

Capture 7.PNG

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)

Capture 8.PNG

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

Capture 10.PNG

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.

Capture 21.PNG

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.

Capture 22.PNG

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.

Capture 23.PNG

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

Capture 24.PNG

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Mustafa Bensan

    Hi Anil,

    Nice write-up for a common business requirement.  As a third option, with a single BEx query, what about defining both an input variable and default value for the Calendar Day as shown below?:

    DefaultValuePanel.png

    DefaultValueDefinition.png

    With this approach instead of using the Dimension Filter component to select the date, the Date Picker component could be used in combination with the setVariableValue() method, although admittedly this approach may not be as performant as the ones which use either the Dimension Filter or setFilter() options.

    Regards,

    Mustafa.

    (0) 
    1. Anil Kumar Puranam Post author

      Thank you Mustafa.

      We have taken dimension filter to allow user to select range of dates and multiple dates. I think “Date Field” component is allowing to select only one date.

      Thank you,

      Anil Kumar. Puranam

      (0) 
      1. Ingo Hilgefort

        Hello Mustafa, Anil,

        in case you don’t want to use the SetVariable approach you can still achieve all this with a single BEx query.

        – Add an EXIT variable for the initial view and add the exit variable (configured to be NOT ready for input)

        – at the initial run the “default value” will be used – filled by the EXIT variable

        – after the initial run you can configure any filter values.

        regards

        Ingo Hilgefort, Visual BI

        (0) 
        1. Mustafa Bensan

          Hi Ingo,

          Thanks for the feedback.  In fact, this is exactly what I wanted to clarify by asking the question in my example.  I would prefer not to use the setVariable approach but as Anil has pointed out, when using an EXIT variable as the “default value”, any subsequent filtering with setFilter() seems to apply only to the already restricted result set.  For example, if the exit variable value defaults to a Calendar Day of say 15 Aug 2015 at the initial run and we subsequently try to apply setFilter() say on a date value of 12 Jul 2015, the filter has no effect because the filter applies to the result set already restricted to the default date of 15 Aug 2015 by the exit variable.  So, before applying setFilter() for a new filter value, how can we clear/override the default exit variable value restriction?

          Regards,

          Mustafa.

          (0) 
          1. Ingo Hilgefort

            Hello Mustafa,

            I wasn’t referring to a “Default value” for the variable but instead to just apply the EXIT variable to the initial view, which can be done by placing the EXIT variable into the DEFAULTS section.

            obviously the variable value has to be changes as otherwise the configured value is still active.

            if you want to change the value in that situation you would have to make the EXIT variable (a) optional and (b) ready for input and you would then have to basically remove the value from the variable and then apply the filters on top

            regards

            Ingo Hilgefort, Visual BI

            (0) 
            1. Mustafa Bensan

              Hi Ingo,

              Thanks for the clarification.  I assume by saying “placing the EXIT variable into the defaults section” you mean the defaults section of the BEx Query as I have shown in my screenshot above?

              So in this situation, to remove the value from the variable, don’t you still have to do this with setVariableValue(), in which case wouldn’t you simply apply the setVariableValue() to the new value directly instead of creating the additional steps of setVariableValue() to clear the original default value and then apply a new value with setFilter()?

              Regards,

              Mustafa.

              (0) 
              1. Ingo Hilgefort

                Hi Mustafa,

                yes – there is only one “Defaults” area in the BEx query Designer.

                Sure you can also apply the values to the variable but I was replying to the original entry and simply stating that there is no need to have two queries for this.

                we also know that variables can be slower than filter.

                regards

                Ingo Hilgefort, Visual BI

                (0) 
                1. Anil Kumar Puranam Post author

                  Hi Ingo Hilgefort,

                  I tried this option( Default filter in the BEX with Customer Exit variable) . I am able to see all other dates in the filter but the cross table is not gettting refreshed with data after selecting all other dates. Please note that both Dimnsion filter and Cross tab has same data source.

                  at intial display:

                  Capture 25.PNG

                  After changing the dates in the dimension filter:

                  Capture 26.PNG

                  Thanks,

                  Anil Kumar. Puranam

                  (0) 
      2. Mustafa Bensan

        Hi Anil,

        Thanks for your response.  Yes, the Date Field component only allows the selection of one date.  I was more interested in your feedback regarding the use of the setVariableValue() approach in order to limit the required number of queries and data sources to just one.

        Regards,

        Mustafa.

        (0) 
        1. Anil Kumar Puranam Post author

          Hi Mustafa,

          I think you are reffering calling this method in applciation startup. Bit how to define the value to the variable in the method setVariableValue is the concern here.

          or Am I missing some thing here.

          Thanks,

          Anil Kumar. Puranam

          (0) 

Leave a Reply