Category Archives: Functions

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.

Advertisements

Pagination or “Page Contol” in OBIEE Pivot View

Standard

Hello Everybody,

There are several blog entries which explains about pagination in OBIEE Pivot

Please, include this entry also in that list. J

Suppose, i want to see 10 rows for every page in pivot.

Apart from the columns selected in the criteria for particular request, select another column and modify it’s functionality to CEILING((RCOUNT(1))/10.0). Now, change it’s column heading as: Select Page Number:  and drag this column into Pages block of Pivot

Pages of Pivot

Results are like:

Pagination Control

Note: In denominator of the formula, use ‘10.0’ instead of ’10’.  If you use 10, page Numbers are populated  from ‘0’ and in the 0th page only 9 records will be shown for this particular scenario.

Capitalizing the Initial character of a Column’s Data in OBIEE

Standard

Let’s say, your database containing the data in small letters for one of the columns.

Now, requirement is like, need to capitalize the first character of every value in that column.

One approach is: If you are using Oracle database, you can use Initcap function with Evaluate function in obiee. Actually, Initcap function in oracle capitalizes every character which comes after space along with 1st character.

You need to write functionality as shown below, to achieve your requirement.

SyntaxEVALUATE(‘INITCAP(%1)‘, Table.ColumnName)

Ex : EVALUATE(‘INITCAP(%1)‘, Products.”Prod Category”)

If it’s not oracle database, you find equivalent and appropriate function for Initcap for your own database and write the same in Evaluate function

Another Approach is:  Write the following functionality in one column of answers

upper(SUBSTRING(Products.”Prod Subcategory” FROM 1 FOR 1)) || SUBSTRING(Products.”Prod Subcategory” FROM 2)

But this shows only first letter as capital, and remaining all in smaller case.

You can write these functionalities in rpd also… and make use those columns directly in Answers.

You can find my answer in forums for same question here

Hope it’s helpful to you…