Dynamic default values with prompt in webi reports (based on bex queries) in BO

[Former Member]

     There has always been requirement wherein webi reports in BO are scheduled to run periodically for dynamic prompt values.

For e.g. We have reports which are scheduled to run monthly with cal.year month input as last month.

(P.S.  This document considers only universe based on bex queries for using this method.)

This obviously is easily achieved by using customer exit variables in BI. However, the only issue in using such variables is that there is no prompt for such variables in BO reports. Due to which such reports can by default be executed only for the values that are passed in the variables from the exit.

      But, many a time we have requirements wherein we want a report to by default get executed for the dynamic value passed through the variable when it is scheduled on periodic basis and also have a prompt option so that user can execute it for any value he/she requires. Since, there is no such option in BO ( as of now atleast ) to have a prompt with default values, and after looking out for a work around everywhere and failing to find any thing which could suffice such a requirement, I have found a work around which has helped to overcome this difficulty.

       To explain the method used, I would take a simple scenario where every month a BO report which is based on a bex query is scheduled to execute for last month i.e. Cal.year month(0calmonth) is the input for the query and the report should get executed by default for last month.

The idea is, whenever  ‘# ‘(not assigned ) value is passed to the query, the query would by default pick last month as value for the 0calmonth characteristic. However, if there is any other value passed as input other than  ” #” then the input value is to be considered as value for 0calmonth. To achieve this we would require two variables :

1. Manual Input variable (Based on 0Calmonth). 2. Customer exit variable (Based on 0Calmonth).

Here, the manual input variable (i.e. CV_CMON) would contain the value which would be provided as input in the query

and the customer exit variable (i.e. CV_MONDEF) would be filled in exit with the desired value at I_Step = “2” (Call after variable entry ).

Below is the screenshot of the logic used to fill the variable CV_MONDEF.

Next, while creating the query on which the universe would be built, restrict the 0calmonth characteristic to both the variables.

As shown in below screenshot.

On execution of query as shown in below screenshots, if ” #”  value is entered in variable CV_CMON then CV_MONDEF is filled with last month value

whereas if any other value is provided as input then CV_MONDEF is filled with value which is provided as input.

So as shown in the screenshots above, based on the input, the variable is restricted to the desired value and hence the desired output is achieved.

Now, using this a publication of the Webi report in BO can be created and scheduled with the Cal.year month as “#”.

So this way the report will be scheduled to run monthly based on the default value of last month and also at the same time

if user wants to change the cal.year month and execute the report the same can be done. Both the things can be achieved with the same webi report.

Hope this helps.