Dynamic Display Name for Presentation columns

There was a question asked on this post here and I thought of having another post to answers Vik’s question.

Hi carpediemconsulting

Good Artical, Thanks

We have a Similar req at my client, but little bit different.

we have a Dimension, in that we have a hierarchy H1 with Levels, Level1, Level 2 , Level 3 we are implimenting this product for different companies, Lets say comp A, & Comp B,
In Comp ‘A’ we call level 1 as ‘Market’ and in comp ‘B’ we call it as “Location”
can we change these Level1 label dynamically when the user login. FYI . these labels are stored in TABLE

Thanks
Vik

As you can see from the properties of a presentation column you could see that there is a provision to specify a custom display name.

I have two users created in the RPD. USER1 and USER2. My objective is to give a custom display name for a Column (Brand column in the Product Table). User1 would like the display to be USER1 BRAND and user2 USER2 BRAND.

I have created a session variable (BRAND_DISP_NAME) which would be initialized to USER1 BRAND for user1 and USER2 BRAND.

In the properties window for the presentation column brand do the following.

Uncheck use logical table Name check box

Check the Custom Display Name check box

Type in the name of the session variable in the text box. In my case VALUEOF(NQ_SESSION.BRAND_DISP_NAME)

Here is a screenshot.

clip_image002

Now lets see how the column shows up for these 2 users in Answers.

USER 1

clip_image004clip_image006

As shown in the screenshots above the display name for the column “brand” is different for these two users.

Hope this answers Vik’s question.

Period Comparison without time series or ago functions.

Recently at a client site we had to connect to some tables in owned by another application and generate some reports for our OBIEE user base. We had no control over the table structures in the external application and we had to generate some time series measures. We couldn’t use the time series wizard or the ago functions provided by OBIEE.

The report had to display all the quarters of the current year and the growth for the same period for each of the quarters.

This is how the repots looks like in a regular table view in answers.

clip_image002

And the picture below shows what the client wanted.

clip_image004

We could have done the same with a complex case expression but we found an alternative which is more elegant and better performing, using pivot table calculations.

First up we need to change the formula for the quarter to the one shown below.

clip_image006

Current year is a repository variable, this could also have been a presentation variable.

And the table view would look like

clip_image008

Now create a pivot table for this with the year on the left and the quarter on the top.

Create 2 calculated items on the year column

The first one (Actuals) as $2 and the second one as $2-$1 (growth) and then hide the details and this would give the sales amt for all the quarters for the current year and the growth.

clip_image010

Displaying Charts as Images instead of Flash in OBIEE

Recently, one of my clients wanted to spin up Oracle Business indicators, an application to provide Apple Iphone users with real time, secure business information generated by OBIEE and other Oracle Applications.

However, viewing the reports generated by OBIEE on the Iphone requires further configuration of the OBIEE presentation server as  the Iphone presently does not support Flash displayed on web pages.  So reports that contain Flash components like charts  are not displayed correctly.

We  can modify the instanceconfig.xml file to display charts in a custom graphic format  like PNG or JPEG instead of flash.

Include the following in <Server Instance> section of instanceconfig.xml

<Charts>
<DefaultImageType>PNG</DefaultImageType>
</Charts>

It is recommended that you configure a separate presentation server to cater to the mobile device users as displaying charts in PNG or JPEG disables some of the interactive features offered by Flash.

Modeling an Essbase Cube In OBIEE

In my previous blog entry we created a simple Essbase Cube. Now lets see how we can get that cube into OBIEE. First we have to get to the Essbase cube and prep it a bit so it looks neat in OBIEE. Navigate to the cube in the Administration services console. Right Click on one of the dimension tables and select generations.

croppercapture2

Give appropriate names for the different levels in your hierarchy. If you don’t do this your hierarchy levels would show up as Gen 1, Gen2 etc.

croppercapture3

After you rename all the levels in all your hierarchies, save the outline. It’s ok to skip this step too. When you save the outline go with retain all data.

Now open up your admin tool. File > Import from Multidimensional

Enter the name of you “Essbase” server and the login credentials

croppercapture4

You can see all the cubes in the Essbase server that the user has access to.

croppercapture5

Select the cube that you want to import. Now you have the cube imported in the physical layer. You will see that the physical layer looks a lot different that your usual tables and joins. All the hierarchies are defined in the physical layer and there are no fact tables only columns. You will also see that the levels in the hierarchy reflect the names that you had changed to in the beginning from the Administration service console.

croppercapture6

Just drag the cube into the BMM layer and then to the presentation layer. When you drag it into the BMM layer you will see that the fact tables and hierarchies are defined, saving you some work. Also all your measures will be set to External Aggregation. Save your RPD and go to Answers to play with your cube data.

Report Selection using Dashboard Prompts

Recently I was approached with a request to implement ‘REPORT SELECTION” using dashboard prompts.

Basically,  the user is given the choice using a drop-down list to select the report that he wants to see on a dashboard page.

Here is a snapshot of the functionality of the prompt.

When the user selects the QUANTITY from the prompt, he sees report #1 and when he selects SALES he sees report #2

Image-0002 Image-0003

 

We will implement this functionality using Guided Navigation sections.

The first step is to create a dashboard prompt that will give the user the choice of the report to select. In show SQL section of the dashboard prompt enter the following SQL.

SELECT CASE WHEN 1=0 THEN REPORT_ALIAS.DUMMYCOLUMN  ELSE ‘QUANTITY’  END FROM CUBE_DATA
UNION
SELECT CASE WHEN 1=0 THEN
REPORT_ALIAS.DUMMYCOLUMN ELSE ‘SALES’ END FROM CUBE_DATA

Image-0009

Here, the dummy column is actually not being used in the prompt, but serves us  to define the two report names QUANTITY and SALES as values in the drop-down of the dashboard prompt.

We will create a presentation variable named REPORT_NAME for the above prompt.

The next step is to create source requests for the guided navigation section. We need to create a request that will return non-zero rows when the first value in the prompt that is ‘QUANITITY” is chosen and zero rows when the second value that is “SALES’ is chosen. Similarly we will create another source request that will return rows for when SALES is chosen and zero rows when QUANTITY is chosen.

SOURCE REQUEST #1

For this let us create a request that will return one column with value ‘QUANTITY’ and apply the presentation variable REPORT_NAME that we created earlier. So what this does is, when the value of the presentation variable is equal to ‘QUANTITY’ then this source request will return rows.

Image-0004

Image-0005

SOURCE REQUEST #2

This request will return one column with value SALES. We will apply the presentation variable REPORT_NAME as filter so that when SALES is chosen from the dashboard prompt, this request will return non-zero rows.

Image-0006

Image-0005

Now we will create sections in the dashboard that will include the report selector prompt, and the QUANTITY report and SALES report in the other sections. The sections that contain the reports will be guided navigation sections. We will reference the source request we created above for each of these sections and configure it to show the section if the request returns non-zero rows.

Image-0007 Image-0008

Now on the dashboard when the user selects ‘QUANTITY’, he sees only the QUANTITY report and when he selects SALES he see the SALES REPORT.

Making Custom Changes to Requests by modifying XML Files

Let us see how we can make global custom changes to a request in answers by modifying the associated XML files.

The change we need to implement is as follows:

Image-0000

The text “GRAND TOTAL” needs to be changed to “TOTAL”.

This can be accomplished by making changes to  reportgeneratorsmessages.xml file located in the location {\OracleBI\web\msgdb\l_en\messages}.

You should not edit the file in the above folder directly as these files will be overwritten during upgrades. So the  best practice is to copy the file reportgeneratorsmessages.xml to the following folder:

{\OracleBIData\web\msgdb\customMessages}. If you do not have a folder called customMessages then create one and copy the xml file into it.

Now search the XML file for the text “GRAND TOTAL” and replace it with any custom text that you want. In my case I am changing it to total.

Image-0001

Restart  presentation services and make sure that the changes are reflected in the results.

Image-0002