Oracle BI EE 10.1.3.3/2 - Between Prompts for Date Columns - Using Presentation Variables
Posted by Venkatakrishnan J on February 26, 2008
I just saw another question in the forums today where the users wanted to know how to go about creating Date Prompts with “in between” operator. So, basically the user had a report containing one date column and he wanted to create a dashboard prompt on that date column which can pass 2 values for the “in between clause”. One of the possible approaches to achieve this is to create the dashboard prompt on this column with “in between” operator and have that same column as “is prompted” in the report. This will pass down both the values chosen in the dashboard prompt. But the main problem with this approach is that there is no way you can know the 2 values passed. For example, say you need a report containing Start Date, Sales and Number of Days where Number of Days is the number of days between the values chosen in the dashboard prompt, then using the first method this is not possible since there is no variable to refer to the 2 values. Now, lets look at an approach today to achieve this using Presentation Variables. This is a variation of the approach that we saw earlier here. Lets start with creating a dashboard prompt. This dashboard prompt shall contain 2 prompts on any column. For both the columns, in the formula tab enter the below formulas
CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test’ END
CASE WHEN 1=0 THEN CHANNELS.TOTAL_NAME ELSE ‘Test1′ END
We have used the above formula in order to protect the columns from getting the filters. This is very important for setting presentation variables and at the same time not affecting any columns
Now, for both the prompts convert the Results to SQL Results and enter the below SQL
SELECT DISTINCT TIMES.CALENDAR_MONTH_START_DATE FROM SH2
In the above case, TIMES.CALENDAR.MONTH_START_DATE is my date column. Make both the above prompts to set 2 presentation variables Date1 and Date2.
Save this prompt. Now go to Answers and start creating a report containing 2 columns, TIMES.CALENDAR_MONTH_START_DATE and SALES1.SALES. Now create another column with the following formula
TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP ‘@{Date2}{1900-01-01 12:00:00}’, TIMESTAMP ‘@{Date1}{1900-01-01 12:00:00}’)
So, what this does is, it calculates the time difference between the 2 dates which would be selected in the dashboard prompt.
Add 2 filters to TIMES.CALENDAR.MONTH_START_DATE (one with less than or equal to and the other with greater than or equal to). In both the filters refer the Date1 and Date2 respectively.
CALENDAR_MONTH_START_DATE is less than or equal to @{Date1}{1/1/1900 12:00:00 AM}
CALENDAR_MONTH_START_DATE is greater than or equal to @{Date2}{1/1/1900 12:00:00 AM}
Now, include both the Dashboard Prompt and the Answers request in the dashboard page. Now, you should see that as you change the 2 dates, the Number of Days in the report would change and also the filter would be applied accordingly.
February 26, 2008 at 11:17 pm
I’ve noticed in a lot of your postings with Presentation Variables and Prompts that you use the formula syntax, “CASE WHEN 1=0 THEN [TABLE].[COLUMN] ELSE ‘Text’ END” when not referencing a specific column to set the Presentation Variable. Is there a reason for the CASE statement? I use a simple ‘Text’ in my formula and it seems to work fine, but was curious if you knew of something additional the CASE statement provided. Thanks!
February 27, 2008 at 10:24 am
Kevin - The main reason why i use that CASE statement is to avoid getting an error since BI Server will not accept a query that does not contain any columns. You can try that using a simple report where your only column would be ‘Test’. It will throw an error. Similarly, this would be the case if you do that in a dashboard prompt. The CASE statement tricks the BI Server into assuming we are actually using a Column but we are actually not. I have an example here http://oraclebizint.wordpress.com/2007/12/31/oracle-bi-ee-101323-dual-table-equivalence/
March 10, 2008 at 1:45 pm
Hi
March 10, 2008 at 1:49 pm
Hi Venkatakrishnan,
Sorry my eaqrlier post was a typo. I did try your method for using presentation variable for date prompts with inbetween clause. However the only short coming was the control type tht you could use for date prompts with the above method is either a drop down list, edit box or multi select and this is something that the business user may not like when compared to choosing a calendar control. Is tehre any other option to get a calendar prompt other than creating two distinct alias of W_Day_d? so that i can set a presentation variable and use them in the Dashboard? Appreciate your inputs.
March 10, 2008 at 5:49 pm
Venkat,
Can you kindly elaborate as to why/(against what) are we protecting the date column with the CASE statement?
Thanks and regards
March 10, 2008 at 6:31 pm
@Soumya - You can still use the Calendar Control if a date field is part of your case statement. In your case, you need to use something like CASE WHEN 1=0 THEN TIMES.CALENDAR_MONTH_END_DATE ELSE TIMESTAMP ‘2200-01-01 00:00:00′ END for one prompt and for the other CASE WHEN 1=0 THEN TIMES.CALENDAR_MONTH_END_DATE ELSE TIMESTAMP ‘2200-01-02 00:00:00′ END.
@Arghya - There are 2 reasons why i use the case statement. One is, if you directly reference a simple value like ‘Test’, it would not work since BI Server expects atleast one column in order to fire a query. The second is to ensure that the filter you are applying does not affect the column which is part of the case statement.
March 11, 2008 at 1:08 am
Hi,
Please Expert Venkat is it possible to create an aggregate column in BI SERVER to calculate a duration between to date?
Please how do i proceed to make that ?
Thanks for your response.
March 19, 2008 at 5:42 pm
Hi Venkat,
I am in the process of learning OBIEE and hence this very basic question. Appreciate your response on this.
What is the real use behind adding the Joins on a Logical Fact in the BMM layer using Add button in the logical sources. Is it necessary to add all dimensions as soon as you create a logical fact provided you have all Logical dimension’s set up in the BMM layer.
( In the general tab we have a add button on the Logical Fact and you can add a join manually to all dimensions joined to the fact in rhe Physical Layer despite the fact that you have set up the Complex Join in BMM Layer.
Could you please help me understand the intracacies?
April 9, 2008 at 7:42 pm
Hi Venkat,
Thanks for your earlier inputs . However one issue that i have noticed for using the CASE WHEN
to mask the date prompts is that the filter values in the request are not Presentation carried while drilling down.
April 16, 2008 at 1:51 pm
Hi venkat,
Can you explain how to work with the same example with calendar control instead of dropdown list in Dashboard prompts.
April 17, 2008 at 6:48 am
Hi venkat,
I used the calendar control like as you said.But i am getting the Error like “Specified format is not matching”.actually i am using the column which has datatype ‘Datetime’ in Database.
Can you please help me in this regard?
April 19, 2008 at 1:04 pm
Hi,
while am following same steps as mentioned here am facing problem while am creating the column
Totaldays=TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP ‘@{Date2}{1900-01-01 12:00:00}’, TIMESTAMP ‘@{Date1}{1900-01-01 12:00:00}’)
error: [nQSError: 10058] A general error has occurred. [nQSError: 27002] Near : Syntax error [nQSError: 26012] . (HY000)
SQL Issued: SELECT SALES_FACT.SALES_AMOUNT, TIMESTAMPDIFF(SQL_TSI_DAY, TIMESTAMP ‘1900-01-01 12:00:00’, TIMESTAMP ‘1900-01-01 12:00:00’), TIME_DIM.BUSINESS_DATE FROM SALES
Please do the needful on the same.
Regards,
Rajkumar
April 21, 2008 at 7:03 am
Hi venkat,
i so impressed about this answer,but
i need to get a result in calender control.
How to get date difference value in a new column by using canlender control in a dashboard.
plz help me..
Thank you
April 22, 2008 at 5:52 am
Hi Venkat
I used calendar control instead of drop down list and it displayed the following error:
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46047] Datetime value 4/4/1998 12:00:00 AM000000000 from 4/4/1998 12:00:00 AM000000000 does not match the specified format. (HY000)
Can anyone suggest me an appropriate solution.
Thank you