Category Archives: Date

Calendar View in OBIEE and Applications

Standard

Hi…

Calendar View in OBIEE with Year selection.

Here is the Pivot Layout, you can see the columns used ..

Don’t forget to set an aggregation rule to Max or Min (not sum) for Day Number column there in Measure block of Pivot.

Calendar Week is the column which treat Sunday to Saturday as one week from day starting of year, but not like 1 to 7 is one week, 8 to 15 is 2nd week…

Here is the output:

Applications are like:

Enable a drill report on the day number column to know the transactions for that date..

(it’s not straight forward.. but, possible. If i get time, i’ll come up with an article about that.. )

Please propose other applications on this, i’ll appreciate it .. 🙂

Regards,

Kishore Guggilla.

 

Advertisements

Equivalent “Next_Day” Oracle’s functionality in OBIEE

Standard

 

There is equivalent functionality in OBIEE, for Next_Day function in Oracle. 

          There is a question too in Oracle forums by me. And, excellent answer by mma1709 (Sorry to say this, as i don’t know his name and that is what there in forums too)

Here it’s…

TIMESTAMPADD(SQL_TSI_DAY, ( 7 – dayofweek(Time.ActualDate) + X ), Time.ActualDate) 

 

X = 1 will be sunday. If you want tuesday it will be 3. In that way, X will be replaced by the number depends on the next dayname you want.

Conversion of date datatype to timestamp in OBIEE

Standard

Hi,

In OBIEE, direct date to timestamp conversion is not possible unless we use evaluate functions to cal l it’s database functions…

One way i found to convert date column to it’s timestamp is:

First, cast the date column to char later cast it to timestamp

Ex: Cast(Cast(CURRENT_DATE AS CHAR) AS TIMESTAMP)

  

You may have question why we need to convert it to timestamp..

Well, while using timestampdiff with sql_tsi_hour as interval, we can’t pass date datatype columns as it throws error:

Ex: TimestamDiff(SQL_TSI_HOUR, TimeID, CURRENT_DATE) won’t take as functionality. Gives you following error:

[nQSError: 10058] A general error has occurred. [nQSError: 22025] Function TimestampDiff is called with an incompatible type. (HY000)

 Now, try in this way:

TimestamDiff(SQL_TSI_HOUR, TimeID, Cast(Cast(CURRENT_DATE as Char) as Timestamp))

It gives you proper results with out any error.

Thanks

Kishore Guggilla

Set yesterday or some date value as default in OBIEE dashboard prompt

Standard

Aim is to set default value to yesterday or some other day that you desired in dashboard prompt. So, when we click on the dashboard page.

This can be done in many ways

Here I am going to introduce 3 ways of doing the same.

  1. Using Advanced SQL in Prompt (From Answer side)
  2. Using repository variable (From rpd side)
  3. Using derived column in rpd (Same using repository variable)

We discuss this one by one

Using Advanced SQL in Prompt (From Answer side):

  1. Create a prompt on date column as per your requirement
  2. In Default to option drop-down, select SQL Result SQL Result

3.   Now click on eclipse button provided under selected SQL Result option

4.   Write the following query in the editor & click on OK.

(Here I am explaining to set default value to yesterday,)

SELECT CASE WHEN 1=0 THEN Times.”Time Id” else TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE) END FROM SH

In general, query will be,

SELECT CASE WHEN 1=0 THEN “any date column”  else TIMESTAMPADD(SQL_TSI_DAY, -1, CURRENT_DATE) END FROM “SubjectAreaName”

Write SQL

5.   Now add this prompt to dashboard page and you will be able to see the prompt is filled with the default value for yesterday

Note: If you want to set some other date as default, you can do that by simply changing the else part of SQL written.

Using repository variable (From rpd side):

1.   Open rpd and go to Manage > Variables… and select new initialization block to creating dynamic repository variable. Select new initialization block option.

2.   Click on Edit Data Source… and write the query & choose the corresponding connection pool as shown below.

Query is:

SELECT CURRENT_DATE – 1 FROM DUAL

(or)

SELECT SYSDATE – 1 FROM DUAL

QueryForVariable

3.   Click on OK to confirm the query (You can test here the query o/p by clicking on Test button)

4.   Click on Edit Data Target… option and click New… to create new repository variable

5.   Give repository variable Name, here I am using YstrDay as Variable Name and click OK

Repository Variable

6.   Click OK and again OK to confirm creation of initialization block.

7.   Save the rpd and go to answers and reload the server metadata.

8.   Go to the prompt you have created already, and this time select Server Variable from Default to option.

Server Variable

9.   Click on Eclipse button under the Server Variable option and mention the repository variable name in editor. i.e. “YstrDay” in my case.

10. Save the prompt and check the default value displayed on dashboard.

Note: Again, if you want to change the default date value to some other you need to change the query written in initialization block of rpd

Using derived column in rpd:

1.   Even this is using repository variable, but this time I am creating dedicated column for this in rpd. So that you can simply make it use without mentioning case when condition in writing query or mentioning the repository variable name

2.   Create a Logical column under Time related table and click on Use existing logical column as the source in order to specify the functionality

3.   Write VALUEOF(YstrDay) in eclipse as functionality

4.   Now in query, of 1st method, you can replace timestampadd thing with Yester Day column you have created in rpd

Note: Here i am using SH rpd. So all objects mentioned here are related to SH schema.

ALL THE BEST

Finding week number of current date in current month in OBIEE

Standard

Reference to this post to which i gave reply, in OBIEE forums . 

question is: How to calculate the week of the current month. 

To be more clear if the date is: 1-aug-2009 then week is:1, if it’s 8-aug-2009 then week is: 2 .

Like this, the week number should be generated for given date column.

The solution is: use the CEILING(DAYOFMONTH(“D0 Time”.”T00 Calendar Date”)/7.00) functionality in OBIEE Answers

 

Note: Use 7.00, not just 7 in the denominator to make it act as Real number