Business Intelligence – Oracle

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.

      

About these ads

27 Responses to “Oracle BI EE 10.1.3.3/2 – Between Prompts for Date Columns – Using Presentation Variables”

  1. 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!

  2. Venkatakrishnan J said

    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/

  3. Soumya Jayaram said

    Hi

  4. Soumya Jayaram said

    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.

  5. arghya roy said

    Venkat,

    Can you kindly elaborate as to why/(against what) are we protecting the date column with the CASE statement?

    Thanks and regards

  6. Venkatakrishnan J said

    @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.

  7. StClair said

    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.

  8. Roopa said

    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?

  9. Soumya Jayaram said

    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.

  10. Shiva Molabanti said

    Hi venkat,
    Can you explain how to work with the same example with calendar control instead of dropdown list in Dashboard prompts.

  11. Shiva Moalbanti said

    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?

  12. Rajkumar said

    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

  13. Roosy said

    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

  14. Naresh said

    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

  15. Nishant said

    hello Venkat,

    I am new on Oracle BI
    i have a small problem here, i cannot add the presentation variable on the filter as you have showed above on the one before the last pic… can you please shed some light on that

    Thanks
    Nishant

  16. Kingsly said

    Hi Venkat,

    Excellent blog. However, I have one question.

    I cannot enter the filter CALENDAR_MONTH_START_DATE is less than or equal to @{Date1}{1/1/1900 12:00:00 AM}. I am getting a msg. saying that ‘The value entered must be a date and time in the form of 11/01/2003 11:00 PM’. I am not sure how to overcome this issue.

    Thank you

  17. Kopi Nadarajah said

    Hi Venkat,
    Do you have any examples where you can input Date Range such as ‘LastMonth’ and ‘LastWeek’?
    Thanks.
    Kopi

  18. Rathinavel said

    Hi All,
    i hope everyone is doing well, i just want to filter the values with the custom calendars(ie) values between date selected from the FROM date and the date selected from the TO Date ,i have to show the value in summary page report and same filter should be carry forwarded to Detail page report and also i have to plot a graph between these two dates.

    Suggestions/Directions/Ideas Very much welcome and it will be definitely appreciated.

  19. Rob said

    Is there a way to call the presentatin variable used to dynamically select the column in the column header.

  20. Dimitris said

    hi Venkatakrishnan,
    ur blog is quite excellent!!!!
    I would like to ask you a question…
    How can we achieve to have 2 periods of time for the same measure column on our report.
    Tnks one more timeeee.

  21. Denu said

    Hi Venkat,
    I have tried the solution given above and used a calender prompt for both the date prompts. When I run the report+prompt in the dashboard I find the following behaviour.
    1) run date prompts initially with no values : I see default values specified in the report date filter column used for generating reports
    2) run date prompts with values selected from calendar : I see valid values
    3) Delete the above selection and run the date prompts again : I see sql error because the date check uses the timestamp ’0000-00-00 00:00:00′. It is not using the default values specified in the report date filter column.

    Please let me know how to resolve this.

  22. Lighton said

    Hi there,

    I have two date fields and I want to subtract one from the other in the presentation layer. How can I do that?

  23. Lighton said

    Opps!! my bad. Not to worry Sir. Found the answer right here on your site…. TimestampDiff (interval, timestamp-expression1, timestamp-expression2)

    Thanks alot!

  24. prabusankar R. said

    hai,
    Right now i am study OBIEE.i need th basce concepts like how creat the variable & security & session. What is diffrent between dasboardfilter & pagefilter ?

    plze to give the website id

  25. Sanjeew Singh said

    Hi Venkat,
    I am new into OBIEE. I am on the way to create a report where I have compare data between two week, not two date. Ex – From Week 42 to Week 41. I have visited lots of blogging site but I am not getting help. Kindly Help me.. I am waiting for your responce.

  26. seiciu said

    Hi Venkat!
    I have a twelve column table, each column with different filters on same measure in fact table. Say filter one to column a (number of persons existing at 01-01-2003) is from dimension persons (date_of_birth <= '01-01-2003'). Filter two to column b (number of persons living in Bucharest between '01-01-2004 and '01-01-2006') is (date_of_birth between 2004 & 2006) etc.
    How can I use column a & column b into same result table (view)? I need different filters on same table.

    Thanks!

  27. Satish said

    Hi

    I have arequirement to create a Prompt on the Expiration date field which should have a Drop down list like 30, 60 and 90 Days and default to 30 days.

    So when i select 30 days and click on go it should display Current Date to +30 days Range of dates.
    When i select 60 its Curren Date to +60 Range and so on….

    Did you ever tried this using case statement if so ….could you please help me on this…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: