cancel
Showing results for 
Search instead for 
Did you mean: 

Webi report for dynamic input parameter

0 Kudos

Dear Team,

Could you please help how i can achieve below scenario in WEBI.

we have report which needs to be published every Monday of the month and final report of previous month on 6th working day of current month.

There are two input parameters on report which will fetch data based on dates . Find below example for reference. source is SAP BW.

1. Report on 6th of June with data 1st June– 5th June.

2. Report on 13th of June with data 1st June– 12th June.

3. Report on 20th of June with data 1st June– 19th June.

4. Report on 27th of June with data 1st June– 26th June.

5. Final report on 8th of July with data 1st June – 30th June

Thanks

Ajinkya

Accepted Solutions (0)

Answers (1)

Answers (1)

nscheaffer
Active Contributor
0 Kudos

I have never worked with SAP BW, but perhaps this will work.

You cannot currently schedule a report with dynamic values. Maybe someday. I did submit an idea to do just that nearly 10 years ago. There is another way.

My approach would be to create a second query which would just return the desired date range and then use those values in your SAP BW query. I built a Calendar universe to get all sorts of dynamic dates. You can create a free-hand SQL query something like this...

SELECT
    DATEADD (MONTH, DATEDIFF (MONTH, 0, GETDATE ()), 0) AS [FirstOfCurrentMonth]
  , DATEADD (DAY, -1, GETDATE ())                       AS [Yesterday]<br>

That is SQL Server syntax, but you should be able to come with a variation that works for whatever database platform you have. You have to run that free-hand SQL query in order to choose its values as inputs to your primary query.

You would need to create a second version for the full previous month. Here is the free-hand SQL to get that date range...

SELECT
    DATEADD (MONTH, DATEDIFF (MONTH, 0, GETDATE ()) - 1, 0) AS [FirstOfLastMonth]
  , DATEADD (MONTH, DATEDIFF (MONTH, 0, GETDATE ()), 0) - 1 AS [EndOfLastMonth]<br>

Here is demo of each SQL statement.

You will likely need to create a custom calendar in order to run on the 6th working day of the month.

If you cannot feed these date range values from a secondary query into your SAP BW query, can you incorporate this dynamic date logic directly into the the SAP BW query?

I hope this sparks some ideas.

Noel