How to schedule a Web Intelligence report with Dynamic prompts

[Former Member]

Hi All,

We are facing issue, while scheduling the WEBI reports. We have Date Prompt that is coming from Database . And the date changes automatically on daily base .   So how can we schedule the WEBI  report . that can pick the date from DB table or change the date dynamically .

We are using BI 4.1

Thanks

Arun K

[Former Member]

Hello Arun,

Below link might help you out

Regards,

Sushil Padhye

[mhmohammed]

Hi Arun,

Try this, but you'll have to update as per your requirements.

Dave's Adventures in Business Intelligence - Dynamic Dates Part I: Yesterday and Today

Thanks,
Mahboob Mohammed

[Former Member]

Hi Arun,

You can define dynamic default values for prompts in universes created with the information design tool. These prompts can be based on functions like CurrentDate().

When defining your prompt in the Information Design Tool, select the Set default values and Formula options. Click the button in the text field below to open the Formula Editor, then enter and validate the formula used to compute this default value.

Prompts Variants

If a document contains many prompts and/or large list of values, you can save your frequent answers as a prompt variant in the Prompts dialog box. Later, you can reuse this prompt variant to automatically answer the same prompts.

You can create several variants for the same document. When you answer prompts in the Prompts dialog box, you can select in the Available prompt variants list, one of the variants you have created.

A prompt variant is specific to a pair user/document and available for any data source (including SAP BW). Prompts variants are stored outside the document in the CMS repository.

These prompt variants are very useful to easily and quickly provide an answer to several prompts.

Prompt variants should help reducing the Web Intelligence document multiplication.

Hope it helps your query..!!

Regards

P.N.Shanthan

[Former Member]

Hi Shanthan,

Thanks for your quick reply.

But we are using universe design tool, and my concern is when we are selecting prompt values in scheduling , we have to select values which are currently available ..

In my case the value will change daily , Example : date  prompt

29/01/2016 for today

01/02/2016 for tomorrow .

02/02/2016 for day after tomorrow.

where as using webi scheduling i can select only 29/01/2016  as prompt value ....and i am scheduling the report for 1 year. so when ever the report runs automatically , it will take only 29/01/2016 .... it will not change .

is it possible to change the date dynamically . so that it should pick date prompt from an object in universe or from DB

[Former Member]

Hi Arun,

Based on your scenario you need to schedule the report automatically and sending it to the users on a daily basis without modifying.

you can probably try creating a prompt using "sysdate" as below.

(Start Date and End Date)

============================

table.column_name  BETWEEN CASE @Prompt('2.Begin Date(mm/dd/yyyy)','A',,mono,free,,{'Today'})
WHEN 'TODAY' THEN sysdate
ELSE to_date(@Prompt('2.Begin Date(mm/dd/yyyy)','A',,mono,free),'mm/dd/yyyy') END
AND
CASE @Prompt('3.End Date(mm/dd/yyyy)','A',,mono,free,,{'Today+3 Months'})
WHEN 'TODAY+90' THEN add_months(sysdate,3)
ELSE to_date(@Prompt('3.End Date(mm/dd/yyyy)','A',,mono,free),'mm/dd/yyyy') END

Note: In place of 90 you can put it as 364 and "sysdate.12" in place of "sysdate.3".

Regards

P.N.Shanthan

[former_member199945]

Hi,

At universe level you have to create dim objects for particular date dim objects using current date /sys date  and you have to use created dim objects in prompts .

Ex : today - sysdate

       tomorrow -sydate -1

Thanks.

[Former Member]

Arun,

1)Create a today  object in your universe ( UDT).

2)Create a new query(say date range) in your webi report and drag only today object.

3)in your prompt use select from another query and pick the today object.

4) schedule as usual, you don't need to touch the filter values during the schedule time..

Regards

Subbu