Scheduling WebI reports for Dynamic Date Ranges

[former_member184597]

Hello Everyone,

Our Env is BOBJ XI 3.1 SP2 FP2.5 integrated with SAP NW BW 7.02 EPH1

We are trying to schedule few WebI reports which can be used on ad-hoc basis as well. Some reports need to get data from starting of the month to today (like MTD) and few are with dynamic date ranges.

In case of relational universes i used to do Magic Date as mentioned here : http://www.dagira.com/2008/07/21/using-a-magic-date-value-in-prompts

But for OLAP Universes i have written a MDX filter prompt as :

<FILTER KEY= "@Select(Debit Memo Date\Debit Memo Date LOV)">

<CONDITION OPERATORCONDITION= "Between">

<CONSTANT CAPTION= "IIF (@Prompt('Enter\Select DM Start Date: ','D','First day of Current Month\First day of Current Month',mono,free,not_persistent,{'First Day of Month'},,User:0)='First Day of Month','First day of Current Month\First day of Current Month',@Prompt('Enter\Select DM Start Date: ','D','First day of Current Month\First day of Current Month',mono,free,not_persistent,{'First Day of Month'},,User:0))"/>

<CONSTANT CAPTION= "IIF (@Prompt('Enter\Select DM End Date: ','D','Date2\Today',mono,free,not_persistent,{'Today'},,User:0) > '1/1/2100 12:00:00 AM',''1/1/2010 12:00:00 AM'',(@Prompt('Enter\Select DM End Date: ','D','Date2\Today',mono,free,not_persistent,{'Today'},,User:0))"/>

</CONDITION>

</FILTER>

We are getting MDX erros because IIF can only return numeric values. So are there any other approaches for dynamically scheduling the reports or can the above MDX prompt be modified to make it work ?

- Vamsi Ch

[IngoH]

Hi,

I am pretty sure that I did already provide an answer to this question from you. the answer to the question is to use EXIT Variables in the BW query because right now the MDX on top of BW is not able to leverage a system date.

Ingo

[former_member184597]

Hello Ingo,

Thanks for your response. Here the problem is not with getting dynamic dates because we have a seperate DSO with all the required dates like Today,First Day of Month...etc. We have created a Mutli-Provider on top of this DSO and a Cube. With this everything is working fine with respect to WebI reports on demand basis and prompt LOVs with dynamic dates so that user can choose them.

But our question is how to make use of this for Scheduling. If we go with Exit variables then BEx queries will be more specific and might not be able to serve multipe WebI reports. So this will increase BW efforts , BO efforts for new universes/reports and hence project deadlines.

What do you suggest ?

- Vamsi Ch

[IngoH]

Hi,

as mentioned before the EXIT Variable is the approach to go for this use case.

Ingo

[former_member184597]

Hello Ingo,

So we need one exit variable for today and another for first day of month. Then how do we need to proceed. Do you have any documents/references which can help us in achieveing this ?

- Vamsi Ch

[IngoH]

Hi,

that depends on what you trying to achieve.

an EXIT is basically ABAP coding - so depending on what you looking for you might have a single variable or more than one.

whats your scenario ?

ingo

[former_member184597]

Requirement is to get a scheduled WebI report having MTD values based on a business date(say invoice date ) restricting to date ranges like First day of Month and today.

I am assuming these date ranges (First day of month and Today) are going to come from Exit variables. But after getting how to schedule the report to accept dynamic values for these dates ?

- Vamsi Ch

[Former Member]

Hi Vamsi

We are facing same problem in webi but through threads not able to understand how to resolve the issue

whats solution for it.

Regards

Prem

[IngoH]

Hi,

the BW query with the EXIT Variable as part of the BW query will take care of the time ranges based on the system date of your source system.

ingo

[Former Member]

Hi VamsiKrishna,

Did you get any solution for this. Our project also is in deep needs to handle such such issues.

Please provide feedback.

Regards,

Mihir

[IngoH]

Hi,

as mentioned above you can handle those items easily using EXIT variables in the BEx query.

ingo

[Former Member]

Hi,

Thanks for your reply. Our requirement is to use same report for scheduling and for adhoc run by user. We dont want addtional copies of report.

So what you are essenially saying is to add new restricted keyfigures which are restricted to date object which has customer exit variable assigned to it inside it which is not user input. Again this restrcition would be at local kf level which will hamper performance. Adding to it, now I will have a new object which is tailored for scheduling but not for adhoc run. So copy of report is required which is not desirable.

If I use this same customer exit at global level I cant use same universe for adhoc reports. Adding to I will have to created another copy of the report.

Please help. Thanks again for your reply. Also correct me in case where I am wrong.

Regards,

Mihir

Edited by: Mihir Kiri on May 6, 2011 6:56 PM

[IngoH]

Hi,

you can use the EXIT Variable on a keyfigure level and on a global level and for it will not "hamper performance" as it leverages the backend. anything you do in the report itself might become a client side filtering.

Ingo

[Former Member]

Thanks for your reply. But how about using the same copy of the WEBI report without modifying for scheduling as well as adhoc run. Do we have any facility for this. Please let us know.

[Former Member]

Ingo,

Any update on this. We are very curious to know a solution for this. Keenly awaiting your response.

Regards,

Mihir

[IngoH]

hi,

I already gave you the solution above - EXIT variables in the BEx Query.

Ingo

[Former Member]

Hi Ingo,

Any update on this. Can I use same version of the WEBI report (without extra columns or modification) both for scheduling as well as adhoc runs with different date selection ? This is very important for us.

Otherwise this is very big shortcoming in WEBI.

Regards,

Mihir

[IngoH]

Hi Mihir,

I already provided you the solution. if you want to use dynamic date ranges that means EXIT variables in the BEx query.

regards

Ingo Hilgefort

[Former Member]

Ingo,

When we schedule the WEBI report we use the BEX query with a variable based on a customer Exit .

There are good examples in

https://wiki.sdn.sap.com/wiki/display/profile/Surendra+Reddy

In order to avoid providing a hard coded date restriction in the BEX query we want to have a flexible date range solution (e.g. data from 1.1.2011 until yesterday).

Is the folloing scenario possible:

Scheduled webi report with a hard coded date e.g. 1.1.1900 (for the BEX variable)

The BEX query should return the data from 1.1.2011 until yesterday (dynamically)  if date = 1.1.1900

else it should return the data (for the e.g. adhoc reporting)

Is a customer exit like this possible ?

Pseudo code:

IF date = 1.1.900 then return sysdate-1 else return entered date

For a relational universe I always used a magic date condition

[Former Member]

I have implemented the magic date with customer exits. Good examples are available:

https://wiki.sdn.sap.com/wiki/display/profile/Surendra+Reddy