Dynamic Date Prompt in Webi Query Panel

[Former Member]

Hi Everyone,

We have a webi report requirement where the user runs the report for a prompt between two order created dates. Such as Order Created Date between <date1> and <date2>.

This prompt is an optional prompt. If the user chose to leave it blank and hit ok, the report should pickup the current MTD as the default prompt and run the report. Such as Order Created Date between June 01st and June 14th (Current Date).

We want to implement this at the universe level so that when the user creates a ad-hoc report, they can easily drag and drop the Order Created Date filter into the report and get the functionality.

This field contains Date and Timestamp. we are using UDT for the universe and the environment is BO 4.1.

Let me know if you have any questions or my question isn't clear. Thanks in advance for all your inputs.

[Former Member]

Hi,

Try to create filter in UDT below condition, It's working in IDT.

@Select(Order\Order Created Date)  BETWEEN

(Case WHEN @Prompt('Shipment Date From MM/DD/YYYY','A',,Mono,Free,Not_Persistent,{'Month Start Date'}) = 'Month Start Date' THEN Cast((DATE - extract(day from DATE))+1  AS DATE FORMAT 'MM/DD/YYYY')

ELSE To_Date(@Prompt('Shipment Date From MM/DD/YYYY','A',,Mono,Free,Not_Persistent,{'Month Start'}),'MM/DD/YYYY') END)

AND (Case WHEN @Prompt('Shipment Date To MM/DD/YYYY','A',,Mono,Free,Not_Persistent,{'Current Date'}) = 'Current Date' THEN DATE

ELSE To_Date(@Prompt('Shipment Date To MM/DD/YYYY','A',,Mono,Free,Not_Persistent,{'Current Date'}),'MM/DD/YYYY') END)

Thanks,

Krishnateja

[Former Member]

Hi Krishna,

Thanks for your reply. Based on what I understood,

1. You are converting the prompt to text format ('A') this will not show a calendar in the webi prompt panel. Our users are expecting to see a calendar to easily select the dates.

2. Also, you are showing text ('Month Start Date') as default values in prompt, our users are expecting to see current month first day as the default value. (i.e., this default will change every month).

Please correct me if I misunderstood you approach.

[Former Member]

Hello Sharan,

May be this will be late to reply. But please check below link which may be helpful.

You need to make changes in code as per your requirement.

Regards,

Sushil Padhye

[Former Member]

Hi ,

Use @Prompt function at the universe level to define your parameters there, You can write a experesion to meet your requirement there.

@Prompt('message','type',[lov],Mono|Multi,

free|constrained|primary_key,persistent|not_persis

tent,[default_values])

Regards,

[Former Member]

[Former Member]

Hi Nagraj,

Somehow I couldn't copy/paste into this text editor. So I typed my response in a notepad, took a screenshot and attached the image. Hope you are able to see it. Let me know if you can't.

[Former Member]

Sorry the Else condition doesn't have "=" part (=  '1/1/999' or = '1/1/1111')

[mhmohammed]

Hi Nagraj,

Try creating a pre-defined filter in the Universe with the below statement (in Where clause):

Syntax if the backend database is Oracle:

Case

When @Prompt('Enter Order Created Date (Start):','D','Order\Order Created Date',Mono,Free,Persistent,,User:0)  is null

     AND  @Prompt('Enter Order Created Date (End):','D','Order\Order Created Date',Mono,Free,Persistent,,User:1)  is null

Then @Select(Order\Order Created Date) Between  TRUNC(SYSDATE, 'MONTH') AND  SYSDATE

Else @Select(Order\Order Created Date) Between

@Prompt('Enter Order Created Date (Start):','D','Order\Order Created Date',Mono,Free,Persistent,,User:0) AND @Prompt('Enter Order Created Date (End):','D','Order\Order Created Date',Mono,Free,Persistent,,User:1)

End

Syntax if the backend database is SQL Server:

Case

When @Prompt('Enter Order Created Date (Start):','D','Order\Order Created Date',Mono,Free,Persistent,,User:0)  is null

     AND  @Prompt('Enter Order Created Date (End):','D','Order\Order Created Date',Mono,Free,Persistent,,User:1)  is null

Then @Select(Order\Order Created Date) Between  DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)   AND  GETDATE()

Else @Select(Order\Order Created Date) Between

@Prompt('Enter Order Created Date (Start):','D','Order\Order Created Date',Mono,Free,Persistent,,User:0) AND @Prompt('Enter Order Created Date (End):','D','Order\Order Created Date',Mono,Free,Persistent,,User:1)

End

Only change you'll have to make to the above is, the text in Blue should be replaced by the Class or FolderName\Date Object Name, you can do it by deleting the text in Blue and double clicking on the Order Created Date object from the list of objects available in the Prompt Creation Screen.

Let us know if you have any issues.

Thanks,
Mahboob Mohammed

[Former Member]

Hi Mahboob,

Thanks for such a detailed explanation.

Is the code same for Teradata too? I have made some changes for Teradata such as instead of SYSDATE I gave Current_date.

I get an error saying "Parse Failed: Invalid Definition (UNV0023). Error parsing default values parameter (7th parameter)."

It is somehow not accepting leaving the default values empty.

[mhmohammed]

Hi Sharan,

Try this:

Case

When @Prompt('Enter Order Created Date (Start):','D','Order\Order Created Date',Mono,Free,Persistent,User:0)  is null

     AND  @Prompt('Enter Order Created Date (End):','D','Order\Order Created Date',Mono,Free,Persistent,User:1)  is null

Then @Select(Order\Order Created Date) Between Current_Date - EXTRACT(DAY FROM Current_Date)+1 AND Current_Date

Else @Select(Order\Order Created Date) Between

@Prompt('Enter Order Created Date (Start):','D','Order\Order Created Date',Mono,Free,Persistent,User:0) AND @Prompt('Enter Order Created Date (End):','D','Order\Order Created Date',Mono,Free,Persistent,User:1)

End

First Day of Current Month for Terradata is calculated by using formula Current_Date  -  EXTRACT(DAY FROM Current_Date) + 1 and Current Date is Current_Date.

Let us know what happens.

Thanks,

Mahboob Mohammed

[Former Member]

Hi Mahboob,

I have tried this approach but the issue is with the prompt syntax itself. I am getting the same 7th parameter error.

[Former Member]

Thanks Mahboob,

Sharan, I don't deal much with these codes, However you try something like the below trick

(all joins and sql and continues the following code ((char(table1.date1) >= CASE WHEN @PROMPT('Enter Date (format YYYY-MM-DD) or * for Current Date','C',,,) = '*'

THEN '9999-12-31' END )

OR  @PROMPT('Enter Date (format YYYY-MM-DD) or * for Current Date','C',,,)  BETWEEN char(table1.dat1) and

char(table2.date2))

This gives you an option to either enter specific dates or just press * so that it takes current date.

Regards,

[Former Member]

I guess I missed something you ,can try writing code in similar lines.

[mhmohammed]

Hi Sharan,

Remove the 7th parameter User:0 or User:1 from the case statement and check what happens. It will show up Order Created Date Start prompt at the bottom and Order Created Date End prompt on top.

Thanks,

Mahboob Mohammed