Thursday 22 November 2012

Is it possible to use Dashboard Prompts to override Session Variables?


This is a two step process:

1) Tick “enable any user to set variable” checkbox in the RPD for the session variable
2) When you create a dashboard prompt, in the Set Variable list, choose to populate a variable for the dashboard prompt using a server request variable.

3)give the variable name same as session variable .
If you set a server variable it will override explicitly the value of this variable set via the initialization block.
The variable is changed in the dashboard that uses the prompt( where the variable is set).

C U Next Time..

Wednesday 21 November 2012

Displaying duplicates in a report


By default, a report in obiee will eliminate duplicate in a report.
If u want to show duplicate records also in u r report.
u need to do this process.
  • In BMM layer created a logical column - rownum 
  • Check 'Use existing logical columns as a source" add the following code in the Expression Builder: EVALUATE('ROWNUM' AS INTEGER). 
  • Include this in your criteria on your report as hidden.

C U Next Time..

Sunday 4 November 2012

Various Calculations for Date


·        Due within Days:

TimestampDiff(SQL_TSI_DAY,CURRENT_DATE,"Time Dimension"."Date")



·        Days Past Due:

TimestampDiff(SQL_TSI_DAY, "Time Dimension"."Date",CURRENT_DATE)



·        Calculate current year with Current quarter:

EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), CURRENT_DATE, 'YYYY-Q')



·        Calculate previous year’s Current Year with previous year’s current quarter:

EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), TIMESTAMPADD( SQL_TSI_YEAR , -1, CURRENT_DATE), 'YYYY-Q')




·        Calculate current year with current fiscal quarter:

EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), CURRENT_DATE, 'YYYY')||'-'||CAST("Time Dim"."Fiscal Quarter" AS char)


·        Calculate previous year’s current year with previous year’s current fiscal quarter:

EVALUATE ('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), TIMESTAMPADD( SQL_TSI_YEAR , -1, CURRENT_DATE), 'YYYY')||'-'||CAST("Time Dim"."Fiscal Quarter" AS char)



·        First Day of the CURRENT Month:

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

·        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)))



·        First Day of the Previous Month

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


·        Last Day of the Previous Month

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



·        First Day of the Next Month

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

·        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)))




·        First Day of the Current Year

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

·        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)))



·        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))

·        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))


·        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))

·        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)))



·        First Day of Current Quarter

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

·        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)))




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


DAYOFYEAR(CURRENT_DATE)

Friday 2 November 2012

The connection has failed in OBIEE 11g

If you get “The connection has failed” error while trying to import data from the database using OCI call interface in RPD, perform the below steps.
1. Add the tnsnames.ora file in the BI Home Directory
Ensure, you add all the data source tns entry details in the tnsnames.ora file
Copy the tnsnames.ora file from Oracle Home (Root directory: \App\User\Product\Network\Admin) and paste the file to the below location in OBIEE11g directory
Root directory: \OBIEE11g\Oracle_BI1\network\admin and
Root directory: \OBIEE11g\oracle_common\admin

2. Add the tnsnames file location path in User.cmd file
Add the tnsnames file location path from one of the above location in the user.cmd file. The user.cmd file is found in the below location
Root directory: \OBIEE11g\instances\instance1\bifoundation\OracleBIApplication\coreapplication\setup


Now, it should work. Still if you are unable to get the data, close your RPD and try it.
Restart the services if RPD is still not importing data.