Default value in prompt

building semantic layer
1

Hi

We have reports which need to be scheduled to refresh every month for prior month. To achieve this, I wanted to default the values for the prompts.

So after talking to BO support (they gave solution that it cant be done) and going through the forums like Dave’s blog and the following the topic

I created the following for the report

The report has to run for a period (yyyymm). This is of type integer. So created an object PriorPeriod as character and assgined the value

cast(year(dateadd(month, -1, getdate())) as varchar) + right(‘00’ + cast(month(dateadd(month, -1, getdate())) as varchar),2)

Then created another object PromptPeriod and assigned the Period but as character type. Modified its LOV to list both values from table for Period and PriorPeriod.

Created an object condition on PromptPeriod as below

(
cast(table.PERIOD as varchar) = @Prompt(‘Enter PeriodID:’,‘A’,‘Class \PromptPeriod’,Mono,Constrained,Not_Persistent,{‘PriorPeriod’},User:0)
or
(
‘PriorPeriod’ = @Prompt(‘Enter PeriodID:’,‘A’,‘Class\PromptPeriod’,Mono,Constrained,Not_Persistent,{‘PriorPeriod’},User:0)
and
cast(table.PERIOD as varchar) = cast(year(dateadd(month, -1, getdate())) as varchar) + right(‘00’ + cast(month(dateadd(month, -1, getdate())) as varchar),2)
)
)

In the WebI report, created a query filter for PromptPeriod and defaulted it to PriorPeriod.

The issue is when I run the report for the PriorPeriod, I get both the condition one from the Query filter and another predefined condition as below

WHERE
cast(table.PERIOD as varchar) = ‘PriorPeriod’
AND ((
cast(table.PERIOD as varchar) = ‘PriorPeriod’
or
(
‘PriorPeriod’ = ‘PriorPeriod’
and
cast(table.PERIOD as varchar) = cast(year(dateadd(month, -1, getdate())) as varchar) + right(‘00’ + cast(month(dateadd(month, -1, getdate())) as varchar),2)
)
))

The PriorPeriod in the bolded condition which is from Query filter doesnt get the value. What am i missing in this.

Thanks
Poongs

Poongs (BOB member since 2009-07-20)

2

On your webi report, just drag the PromptPeriod condition filter in the Query Filters area.

Farhan Jaffery :us: (BOB member since 2005-08-27)

3

Hi Farhan

Thanks for your reply.

I have put this condition as object condition of PromptPeriod object (in the where clause of Object properties). so I dont even see this condition when creating the report. I am getting the conditions in the where clause.

The issue is the value for PriorPeriod is not getting replaced as mentioned in other topics. I just get ‘PriorPeriod’ as hardcoded value in the query filter. So when the query is executing, the query filter and the object condition are negating each other.

Thanks
Poongs

Poongs (BOB member since 2009-07-20)

4

Do this to test:
Create a condition object in the universe as follows:

 
cast(table.PERIOD as varchar) = @Prompt('Enter PeriodID:','A',{'1','2','PriorPeriod'},Mono,Constrained,Not_Persistent,{'PriorPeriod'},User:0) 
or 
( 
'PriorPeriod' = @Prompt('Enter PeriodID:','A',{'1','2','PriorPeriod'},Mono,Constrained,Not_Persistent,{'PriorPeriod'},User:0) 
and 
cast(table.PERIOD as varchar) = cast(year(dateadd(month, -1, getdate())) as varchar) + right('00' + cast(month(dateadd(month, -1, getdate())) as varchar),2) 
)

Replace 1 and 2 values in the @prompt definition above with valid table.PERIOD values.

After creating this condition, place it in the WebI query filter to test. This should work. Once this has been successfully tested, all you have to do is create a dimension object on your table.PERIOD column and modify its LOV by adding a UNION clause for ‘PRIOR PERIOD’. Once this object has been created, replace {‘1’,‘2’,‘PriorPeriod’} with the corresponding object path.

Farhan Jaffery :us: (BOB member since 2005-08-27)

5

Hi Fahran

Thank you so much. I tried it and it worked.

I was creating another query filter for the PromptPeriod in the report instead of pulling only the universe condition into the query filter. Thats the reason I was having both conditions.

Thanks for your help.

Poongs

Poongs (BOB member since 2009-07-20)