Variable prompt on date column in 11G

3201108

Hi Experts,

I have 2 quick queries but before that let me explain the scenario.

I have two variable prompt on my dashboard one for start date and another one is for end date.In report i have two columns calendar date and revenue.

query 1: i want output at my dashboard in such a way that like if i am selecting start date and end date in prompt, then output should be between that two particular dates only.

query 2: i want output at my dashboard in such a way that like if i am selecting start date and end date in prompt, then output should be for that two particular dates only.

Pedro F

Hi,

First of all, why are you using 2 variable prompts and not a normal column prompt? Do you only have a calendar date column or do you also have a start date and end date columns?

For your 1st query, if you use a normal column prompt with your calendar date column, you can just set the operator to "is between" and just add a filter in your report that will pick up those values.

However, for the 2nd query it gets a bit trickier especially if you only have a calendar date column and no other date columns. One way that you can do it is to create those 2 variable prompts you mentioned using the calendar date column and set 2 presentation variables (pv_startdate and pv_enddate). Then in your report you will need to set up a filter such as:

Calendar date is equal/is in @{pv_startdate}

OR

Calendar date is equal/is in @{pv_enddate}

Cheers

3201108

Hi Pedro,

First of all thanks for your valuable suggestion.

For 1st query i used column prompt and its worked completely as you suggested.

However for 2nd query i am getting SQL error. I used below SQL code in filter:

"Time"."T00 Calendar Date" IN (@{PV_startdate})

OR

"Time"."T00 Calendar Date" IN (@{PV_enddate})

I am getting below error in result tab by using above SQL code:

SQL Issued: @{call NQSGetLevelDrillability('SELECT "Time"."T00 Calendar Date" saw_0, "Base Facts"."1- Revenue" saw_1 FROM "A - Sample Sales" WHERE "Time"."T00 Calendar Date" IN (@{PV_startdate}) OR "Time"."T00 Calendar Date" IN (@{PV_enddate})')}

Can you please suggest me how to get through above error.

Thanks!!

Pedro F

Create 2 variable prompts like the one in image below, one for pv_startdate and one for pv_enddate.

Then, create a report where you have a filter such as the one below.

Put everything in a dashboard, it works fine.

3201108

Hi Pedro,

Again thanks for your valuable time.

I tried the above steps but still i am getting the same error as the previous one.

Please find below the screenshot of filter condition and error message.

Error message:

Please suggest me how to overcome from this.

Thanks!!

Pedro F

Can you put here a screenshot of how you defined the columns in your dashboard prompt?