OBIEE Expression

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 - Repository Variables, Session Variables

Variables in the Oracle BI Server can be either repository or session variables, and are defined using the Variable Manager within the Oracle BI Administrator (Manage > Variables).

This brings up the Variable Manager, that divides variables out into repository and session variables, with a further subdivision into static and dynamic repository ones and system and non-system session ones. You can also define variables at the Presentation Server level, these are scoped to individual dashboards and are used amongst other things for setting dashboard prompt values and integrating the dashboard with BI Publisher parameters. Here’s a screenshot of a typical Variable Manager screen.

you can either have server variables that hold the same value across all sessions (in BI Server terms, a “repository” variable), or variables that hold a value specific to each user session (“session” variables in the BI Server). Furthermore, repository variables can either be constants (“static repository” variables) or can have values that vary over time (“dynamic repository” variables), with the dynamic ones refreshed to a schedule via SQL calls and session variables usually set at the time of logon by reference to an LDAP server or an SQL statement. So now that’s all clear, in what sort of situation would they be used?

Cheers ;)

TOPN Filter in OBIEE

We want to see the top 10 charges by some practice, this can be achieved by applying the

“is in top” filter to the charge;

But what if you want to retrieve the top 10 results based on some date criteria ?

The workaround for that is to apply an advance filter based on date criteria using “BY” while filtering the charge value.

For that purpose we go to advance and convert the filter to SQL;

Add following to the advance filter;

Finally the filters will look like this;

Cheers ;)

Setting Up Data Sources for BI Publisher

BI Publisher supports three types of data sources:

• JDBC driver database connections
• JNDI datasource connections
• Files
  The files data source option enables you to define a directory to which BI Publisher can connect. You can then place XML files in this directory to use as data input to your reports.

By default, BI Publisher Administrators can access all data sources.
If you have not set up the user roles yet, you can assign data sources to a role from the Create Role interface. See Understanding Users and Roles for more information.
Adding a JDBC Data Source

1. From the Admin page select JDBC Connection. This will display the list of existing JDBC connections.
2. Select the Add Data Source button.
3. Enter the following fields for the new connection:
   
   • Data Source Name - enter a display name for the data source.
   • Connection String - enter the database connect string.
     For an Oracle database the connect string will have the following format:
     jdbc:oracle:thin@server:port:sid
     For example: jdbc:oracle:thin@myserver.mycompany.com:1521:prod
     For a Microsoft SQL Server, the connect string will have the following format:
     jdbc:sqlserver://server
     For example:
     jdbc:sqlserver://myserver.mycompany.com
     Note that if your Oracle BI Server is SSL-enabled, you must copy the keystore to the BI Publisher server and provide it in the connection string.
   • User Name - enter the user name required to access the data source on the database.
   • Password - enter the password associated with the user name for access to the data source on the database.
   • Database Driver Class - enter the database driver class for the connection.
     For example: oracle.jdbc.driver.OracleDriver or
     com.microsoft.jdbc.sqlserver.SQLServerDriver
     
     Important: The JDBC connection library must be placed under [JRE_TOP]/lib. If you are using OC4J, the Oracle driver library will be present with your OC4J install. For other JDBC database libraries you must place them there manually, and restart the server to make them available.
   • Use Proxy Authentication - select this box to enable Proxy Authentication. This applies to Oracle 10g deployments only. For more information, see Oracle Database Security Guide 10g.
4. If you would like to test the connection, select Test Connection. If the test is successful, the confirmation message, "Connection established successfully" will display. If connection error occurs, the message "Could not establish connection," will display.
5. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list will be able to create reports from this the data source.

Adding a JNDI Data Source

1. From the Admin page select JNDI Connection. This will display the list of existing JNDI connections.
2. Select the Add Data Source button.
3. Enter the following fields for the new connection:
   
   • Data Source Name - enter a display name for the data source.
   • JNDI Name - enter the JNDI name of the data source.
   • Use Proxy Authentication - select this box to enable Proxy Authentication. This applies to Oracle 10g deployments only. For more information, see Oracle Database Security Guide 10g.
4. If you would like to test the connection, select Test Connection. If the test is successful, the confirmation message, "Connection established successfully" will display. If connection error occurs, the message "Could not establish connection," will display.
5. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list will be able to create reports from this the data source.

Adding a File Data Source

1. From the Admin page select File. This will display the list of existing file sources.
2. Select the Add Data Source button.
3. Enter the following fields for the new data source:
   
   • Data Source Name - enter a display name for the data source.
   • Path - enter the full path to the top-level directory on your server.
4. Define security for this data source. Use the shuttle buttons to move roles from the Available Roles list to the Allowed Roles list. Only users assigned the roles on the Allowed Roles list will be able to create reports from this the data source.

Viewing or Updating a Data Source

1. From the Admin page select the Data Source type to update.
2. Select the name of the connection to view or update. All fields are updateable.
3. Select Apply to apply any changes or Cancel to exit the update page.

(This article is an excerpt from Oracle Library)

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

How to reset your password for OC4J Admin

Try this to reset your password for oc4j admin

1. Navigate to the D:\OracleBI\oc4j_bi\j2ee\home\config directory
2. Backup the file: system-jazn-data.xml for restore or later reference
3. Edit the file: system-jazn-data.xml

Locate the following entries:

<user>
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<description>OC4J Administrator</description>
<credentials>{903}MMGWnYKcnMKBBOR1LroHHrQpy3zmVbGEohN4aUSjxK4=</credentials>
</user>

and add the XML attribute deactivated="true", as shown below:

<user deactivated="true">
<name>oc4jadmin</name>
<display-name>OC4J Administrator</display-name>
<description>OC4J Administrator</description>
<credentials>{903}MMGWnYKcnMKBBOR1LroHHrQpy3zmVbGEohN4aUSjxK4=</credentials>
</user>

When you next start the oc4j instance using "oc4j -start" the container will re-prompt you for the password and add the newly encrypted password to the <credentials> tag in the oc4jadmin entry.

To start oc4j:

1 Open command prompt by issuing cmd in Run
2 navigate to D:\OracleBI\oc4j_bi\bin
3 say ' oc4j -start'

Good Luck :)

While Saving the Report got the Message “Not Logged In“

This is a bug & appears in a special constellation of Username, Machine Name, Subject Area and Report Name.

Here is the woraround for this problem;

1)      1) Goto “Manage Catalog”

2)   2)   Rename the Report

Now you can save & modify report normally without getting the “not logged in” message. 

How to Bypass cache in OBIEE

Follow below steps to disable the cache of the report to get results direct from the database;

 1. Build Report with required fields,
 2. Go to Advance SQL tab check the Check Box ' Bypass Oracle BI Presentation Services Cache'
 3. Goto bottom of page and add below statement in the prefix section
     SET VARIABLE DISABLE_CACHE_HIT=1;
 4. Save the report

How to add images in OBIEE using local path

1. Upload the images to following two folders

D:\ORAHome\j2ee\home\applications\analytics\analytics\res\s_oracle10\images

(Oracle application server install)

D:\OracleBI\web\app\res\s_oracle10\images

(OBIEE install)

2. After adding images to the folders you need to restart the presentation services

3. In answers, edit the title view and add the following

   fmap:images/your_namename.gif

4. In dashboard you can use the same with image or link

   fmap:impages/your_gif.gif

How to add images on the Report using URL

Here are the instructions to add image on the Report:

1.        Copy the image URL from the Website you want to place on your Dashboard;

e.g. http://www.........com/img/logo40.png

2.       Go to Result Tab

3.       Choose Title from the Result View

4.       Paste the URL in the Logo Text Box

5.       Set the Result View Back to the Compound Layout.

6.       Save the Report.

How to add image on the dashboard using URL

Here are the instructions to add image on the dashboard: 

1.       Copy the image URL from the Website you want to place on your Dashboard;

e.g. http://www........com/img/logo40.png

2.       Go to dashboard where you want to add the image.

3.       Select Page Options > Edit Dashboard

4.       From the Page dropdown select the page on which you want to add the report link.

5.       Drag the Link or Image from the Dashboard Objects section and drop it on the section.

6.       Select properties button on the object newly added (Link or Image 1)

7.       Paste the URL in the image Textbox. Make Layout Adjustments if necessary.

8.       Save the Dashboard.

Welcome to my Blog