Go back to report prompts with a custom button in OBIEE

When a prompt is added to a report in OBIEE, it will ask user to provide the prompt values when the report is opened. Report results will be filtered by the values chosen by user in prompts. After that, user can’t change the report’s filtering condition as the prompts are no longer visible. To solve this, we will add  GoBackToReportPrompt04 button with a link to take the user back to prompts.

How to add Go to Prompts button to the report?

Let’s add a Static Text view in report’s results page.

AddStaticTextViewOBIEE1

Inside the Static Text view check the Contains HTML Markup box and add the following HTML code:

<button onclick="window.location.href='saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FReports%2FSales' ">Go to Prompts</button>

replacing the path= parameter with the path to the report starting with forward slash / (%2F in URL encoding).

GoBackToReportPrompt6

Below the Static Text field you can see the preview of the button. Add the Static Text view to report’s Compound Layout.

GoBackToReportPrompt5

Now the report displays ‘Go to Prompts’ button that takes user back to prompts.

GoBackToReportPrompt3

Auto replace shortcuts in Toad

A very handy tip for Toad for Oracle client: you can create shortcuts for the most used SQL statements or whole queries by adding them to Auto Replace list. Thanks to that feature when you’ll type the shortcut in the editor’s area Toad will automatically replace it with a pre-defined phrase.

Toad_Auto_replace

How to add an auto replace shortcut in Toad?

Go to View / Toad Options:

AutoReplaceToadMenu

Choose Editor / Behavior and click on Auto replace button.

Auto_replace_in_Toad

Let’s add three shortcuts for auto-replace:

 scaf - select count(*) from
 saf - select * from
 wer - where

Auto_replace_in_Toad2

Now after typing the shortcut Toad replaces it automatically with the whole phrase. You can also add shortcuts for whole SQL queries you use often. Keep in mind to create such shortcuts that won’t coincide with the names of objects like tables or columns.

You can achieve the same effect in SQL Developer client – I described it in the Auto replace shortcuts in Oracle SQL Developer post.

Correct order by Month date in OBIEE

How to order by month number not alphabetically by month name?

When you add a column with MON or MONTH date format in OBIEE report its values would be sorted alphabetically, not by the month number order. That way December would come before January etc. To sort by months in their correct order we need a date in a month number format (1 for January, 2 for February etc.).

Order_by_Month_Obiee1

There are two solutions for this:

Change in repository

To avoid such issues, a date column with Month name should have a proper sorting order. This can be done in the repository, by setting sorting order by another date column with correct order (Month Number) in a logical layer. In the logical layer click on Month column’s criteria and change the Sort order column settings to order it by Month number column (it should be available in Date dimension).

Sort_by_Month

Workaround in BI Answers

There is also a workaround solution to achieve correct month order in Oracle BI Answers. To solve this, add the full date column from the same dimension, change the formula to evaluate date in YYYY-MM format.

EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER (30), "Date Column", 'YYYY-MM')

Order_by_Month_Obiee22

Now you can add the column to the table or graph and set the sorting by that column. The values will be sorted by column name. You can hide the Month Number column so its values are not visible in the report.

Order_by_Month_Obiee3

Create a dummy (blank) column in OBIEE

Sometimes we need to add a dummy column to OBIEE report. This is specially true for combine request reports (acting as unions) when we need only the values from one part of the union and nulls or zeroes for the other. To acheive that, we need to add a column with either null or zero value (depending on our requirement). The values to put inside the dummy column’s formula are different, depending on the column’s data format.

If the column is used as a dummy in combine request report it should have the same column type as its respective column in the other part of report – numeric, text or date. Otherwise you’ll get the ODBC error in results.

Dummycolumn02

To create a dummy column with null or zero value, add a column to the report’s criteria and change its formula to one of the below:

Column with numeric values

CAST(null as DOUBLE)
Dummycolumn05 or
0
Dummycolumn07

Text column

null
Dummycolumn04 or
(two single quotes)
Dummycolumn06

Date column

CAST(null as DATE)
Dummycolumn03

Create a non-reusable session out of reusable in Informatica

There is an easy way to create a non-reusable object in Informatica PowerCenter out of a reusable one. This is specially useful when you want to change some settings of the session, so instead of copying the existing one you can drag the non-reusable session directly to workflow and edit the settings there.

A reusable object has the green “cycle” icon in the left corner:

Reusable_session

Notice that a non-reusable session doesn’t have that icon:

Non_reusable_session

How to create the non-reusable session out of a reusable one?

Drag a session (or other object) and just before you drop it in the Designer area hold the CTRL key.

Non_reusable_session

When you’ll hold the CTRL key you can notice that the cursor changed and now has a small  ‘+’ icon. Now you can release the object and you’ll get a non-reusable one.