Filter condition for Webi report scheduling

[Former Member]

Hello,

I have a Webi report with a query that has one filter (it states the starting and ending date of data to be displayed in the report). This report has to be scheduled but it has to contain different filter values each time the report is run.

I'd like to know if there is a way to schedule this report taking into account that each iteration of the scheduling process has to use different values for the filter.

On the same way, the Webi report has a XY diagram with a text title made up of a fixed literal label. Is there any way to inject the text title from the scheduling process (in order to avoid editing the report before each scheduling iteration occurs)?

Thanks a lot!

[Former Member]

Victor,

one filter (it states the starting and ending date of data to be displayed in the report). This report has to be scheduled but it has to contain different filter values each time 

There are a couple of ways to handle this requirement, depending upon other environmental factors. Here is one method, but it requires the ability to update/change data at the database using an external tool (anything that permits processing transactional information).

You can set up an additional table with two columns: starting_date and ending_date

Someone updates the additional table as appropriate before the next run will kick off in the schedule.

The tricky part is in getting your current report to incorporate the other table, and for this you may need to either create a view in your database or a derived table in your universe. Here is the rudimentary SQL structure that might work for you:

create table start_end_dates (beginning_date datetime,end_date datetime)
go
insert into start_end_dates values ('9/1/2009','9/30/2009')
go
create table my_data (col_a char(1),month_id datetime)
go
insert into my_data values ('a','9/1/2009')
insert into my_data values ('a','10/1/2009')
go
select * 
from my_data 
where month_id >= (select beginning_date from start_end_dates) and
      month_id <= (select end_date from start_end_dates)

The start_end_dates table is the one that receives a single input, populated with the starting and ending date of data. The "my_data" table is an example of how the source table can call in the "parameters" of the "start_end_dates" table to fulfill its quest for filtering (additional filtering on other things within your scenario can also be applied).

Once you can get the mechanics of the SQL to work for you (either in the universe or a view), then you can set the report up to work hand-in-glove with the above structure. In the report, you can either have a second data provider that selects on the "start_end_dates" table, storing that data in the report and displaying it somewhere, or you could use the min(month_id) and max(month_id) concepts and display that somewhere too.

Thanks,

John