RTF Template :Variables Initialization and usage

Let’s see how we can use the variables to store temporary data or use for calculation.  This is achieved using  “xdoxslt:” function. These are the BI Publisher extension of standard xslt functions.

Use xdoxslt:set_variable () function to set /initialize the variable  and xdoxslt:get_variable() function to get the variable value.  $_XDOCTX is the System variables to set the XDO Context.


Variables in loops:


/*initialize a variables*/


< ?xdoxslt:set_variable($_XDOCTX, ‘counter’, 0)? >


/*update the variable’s value by adding the current value to MY_CNT, which is XML element */


< ?xdoxslt:set_variable($_XDOCTX, ‘counter’, xdoxslt:get_variable($_XDOCTX, ‘counter’) + MY_CNT)? >


/* accessing the variables */


< ?xdoxslt:get_variable($_XDOCTX, ‘counter’)? >




/*Working in a loop*/


< ?xdoxslt:set_variable($_XDOCTX, ‘counter’, 0)? >


< ?for-each:G1? >


/*increment the counter*/


< ?xdoxslt:set_variable($_XDOCTX, ‘counter’, xdoxslt:get_variable($_XDOCTX, ‘counter’) + 1)? >


< ?end for-each? >


< ?xdoxslt:get_variable($_XDOCTX, ‘counter’)? >


Storing tag values into variable and making calculation with other tags:


I have a variable 'd' with value '8' and I wanted to use that variable and other tags COL1, COL2 in calculating variable 'f' as shown below, Then calculated COL3 with variable 'f' to display results.


< ?xdoxslt:set_variable($_XDOCTX, ‘d’,8) ? >


< ?xdoxslt:set_variable($_XDOCTX, ‘f’, xdoxslt:get_variable($_XDOCTX, ‘d’) + COL1 - COL2 )? >


< ? COL3 div  (xdoxslt:get_variable($_XDOCTX, ‘f’)) ? >


NOTE:

1. Make sure you have spaces between tag names and mathematical operators (ex: COL1 + COL2 - COL3).

2. You can use get_variable() to display results.

3. Always use xdoxslt: prefixed when using get_variable()(ex:( xdoxslt:get_variable($_XDOCTX, ‘f’) )

Rank and Dense Rank functions in OBIEE

If in a class of 5 students, they conducted an exam and 2 got 95, other 2 got 93 and the last one got 91. So, if we are using the Rank function, the output will be as shown below.

Students	Marks	Rank
student 1	95	1
student 2	95	1
student 3	93	3
student 4	93	3
student 5	91	5

And if we make use of the Dense Rank Function, the output will be as shown below.

Students	Marks	Rank
student 1	95	1
student 2	95	1
student 3	93	2
student 4	93	2
student 5	91	3

Hence, we can make sure that Dense Rank function doesn’t skip the values and provides a continuous series of numbers as compared to Rank function.

Now, we can see how to write the query for the same on the database.

SQL Query of Rank function in Database 

SELECT Student,Marks,Rank() over (PARTITION BY Marks order by student) as Rank  from Class 

SQL Query of Dense Rank function in Database 

SELECT Student,Marks,Dense_Rank() over (PARTITION BY Marks order by student) as Dense_Rank  from Class 

Now, lets understand how to implement the same in OBIEE.

 Rank in OBIEE

OBIEE has an internal function called Rank and can be used directly for this. We can use the below syntax in the fx of the column:

RANK(“class”.”student” by “class”.”marks”)

  

Dense Rank in OBIEE

Since, Dense Rank is not an internal function of OBIEE we need to make use of the Evaluate function to use this.Evaluate function is used when we have to make use of the database functions in OBIEE.We can use the below syntax in the “fx” of the column

EVALUATE(‘DENSE_RANK() OVER (PARTITION BY %2 ORDER BY %1)’AS INTEGER,”class”.”student”, ”class”.”marks”) 

The above two syntax shows us that how we can make use of the Rank and Dense Rank functions in OBIEE.

Points to be noted:

We can make use of Rank function as filter in a report but we can’t use Dense Rank function as filter in a report. This is because Rank is an internal function to OBIEE.Therefore OBIEE forms a subquery to calculate the Rank and filters the data in the outer query.

Example: RANK(class.student by class.marks) <=2

OBIEE - Display Row Number(Serial Number) in Pivot Table Report or Table Type Report

To add row number to your analysis, You have to follow the below steps:

Step no: 1

Add the required columns to your report with one additional column (Any column).

Step no: 2

Select 'Edit Formula' from the additional column.

Step no: 3

Edit the 'Column Heading' with the relevant name and replace the Column Formula value with 'Rcount(1)'

OBIEE Calender Expressions

First Day of the Previous Year

TIMESTAMPADD( SQL_TSI_YEAR , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD removes a year from the returned date for the First Day of the Previous Year.

First Day of the Current Year

TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This calculation returns the first day of the year by deducting one less than the total number of days in the year.

First Day of the Next Year

TIMESTAMPADD( SQL_TSI_YEAR , 1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD adds a year to the date returned which will give the first day of the next year.

First Day of the Previous Month

TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then subtracts one month from the first day of the Current Month arriving to the First Day of the previous month.

First Day of the Current Month

TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This expression gets the current day of the month and subtracts one less than the current day to arrive at the first day of the month.

First Day of the Next Month

TIMESTAMPADD(SQL_TSI_MONTH, 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD then adds one month from the first day of the Current Month arriving to the First Day of the next month.

First Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)

This was included to show the calculations discussed above can be used with other functions. This is the same expression as the one that returns the first day of the current month except this one uses the DAY_OF_QUARTER property to return the first day of the current quarter.

Last Day of the Previous Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the Current Month. The second TIMESTAMPADD subtracts a month to arrive at the first day of the previous month.

Last Day of Current Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds one month to the date to arrive at the first day of the next month. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Current Month.

Last Day of the Next Month

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 2, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD finds the first day of the current Month. The second TIMESTAMPADD adds two months to the date to arrive at the first day of month after next. The final TIMESTAMPADD subtracts one day from the returned date to arrive at the last day of the Next Month.

Last Day of Previous Year

TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD subtracts one day to arrive at December 31st of the previous year.

Last Day of Current Year

TIMESTAMPADD(SQL_TSI_YEAR, 1, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds a single year to the date to arrive at December 31 of the Current Year.

Last Day of the Next Year

TIMESTAMPADD(SQL_TSI_YEAR, 2, TIMESTAMPADD( SQL_TSI_DAY , -1, TIMESTAMPADD( SQL_TSI_DAY , EXTRACT( DAY_OF_YEAR FROM CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

From right to left the first TIMESTAMPADD returns the first day of the current year. The second TIMESTAMPADD deducts one day to arrive at December 31 of the previous year. The third TIMESTAMPADD adds 2 years to the date to arrive at December 31 of the Next Year.

Last Day of Current Quarter

TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_QUARTER , 1, TIMESTAMPADD( SQL_TSI_DAY , DAY_OF_QUARTER( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))

Demonstrated using Quarters. From right to left the first TIMESTAMPADD returns the first day of the Current Quarter. The second TIMESTAMPADD returns the first day of the next quarter. The final TIMESTAMPADD subtracts a single day  from the date to arrive at the last day of the Current Quarter.

Number of days between First Day of Year and Last Day of Current Month

TIMESTAMPDIFF(SQL_TSI_DAY, CAST('2010/01/01 00:00:00' AS DATE), TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE))))

The second part of the TIMESTAMPDIFF uses Last Day of the Current Month calculation to force the TIMESTAMPDIFF to calculate the number of days between the first day of the year and the last day of the current month.

OBIEE Error - Concat does not support non-text types

In oracle concatenation can be done between non character types however while concatenating no character types in BI it is not supported.

Here is a workaround;

This can be done by casting the Non Charater Type to character types

e.g

cast(Periods."Month" as varchar(10))

cast(Periods."Year" as varchar(10))||'/'||cast(Periods."Month" as varchar(10))

Different type of xml tags in bi publisher

Not Null:


< ? if@column: G_MON1_QTY_MON1!='' ? >

If not null :

< ? xdoxslt:ifelse(UNIT_PRICE!='',UNIT_PRICE,0) ? >

If null then:

< ? xdoxslt:ifelse(UNIT_PRICE ='',UNIT_PRICE,0) ? >

< ? xdofx:if TOTAL_COST = '' then 0 end if ? >

< ? xdofx:if TOTAL_COST = '' then TOTAL_COST= 0 end if ? >

If with <  or   > process:

< ?xdofx:if ITEM_COST <10 else="" end="" if="" mith="" ohn="" then="">

write decode condition in xml tag

< ? xdofx:Decode(tag_name ,'',0,tag_name? >



For loop:

< ? for-each:G_1 ? >

< ? end for-each ? >

Percentage calculation:

< ? xdofx:if COL2=0 or COL2=  ' ' then '     ' else ((COL18-COL37) div COL37)  * 100 end if ? >


Above Percentage is calculated for tag's < ? COL1 ? >,< ? COL2 ? > and prior to calculation always check denominator in not null or 0.


Note: Always place all the tags in field  ,do not hardcode it on rtf as shown below.