Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Dynamic Report Columns using Dashboard Prompts and Presentation Variables – Part 2

Posted by Venkatakrishnan J on January 24, 2008

If you had gone through my previous blog entry here, you would be knowing how to go about dynamically varying the columns in a report. But one drawback that i mentioned there was, one had to manually enter the column names and any wrong entry would give out an error. Now lets look today how to go about achieving a drop down of column names. Though this can be achieved easily using column selector, the approach that we shall see today would affect all the reports in your dashboard (if you want). This is just an extension of what we saw the other day here. So, our aim is to basically have a list of columns in a dashboard prompt and based on what we choose in the dashboard prompt, all the reports should change having the selected column. Lets start with building the dashboard prompt first. Our aim is to make the dashboard prompt display 2 columns, GEOGRAPHY.COUNTRY_NAME and GEOGRAPHY.CITY_NAME. So, use any column in the dashboard prompt and in the function window enter the following function,

CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘Variable Column List’ END

We are including the above formula in order to ensure that the dashboard prompt does not affect any column.

      

In the SQL Results section enter the below formula. Also make the dashboard prompt to set a presentation variable say Geography. Ensure that you have any one of the column names, for example, GEOGRAPHY.CITY_NAME as a default.

SELECT CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘GEOGRAPHY.COUNTRY_NAME’ END FROM SH2 UNION ALL SELECT CASE WHEN 1=0 THEN GEOGRAPHY.REGION_NAME ELSE ‘GEOGRAPHY.CITY_NAME’ END FROM SH2

Now lets create a new report whose one of the columns would be either CITY or COUNTRY depending on what we choose in the dashboard prompt and the other column would be sales. In order to vary the first column, enter @{Geography} without the single quotes.

      

      

Now include the dashboard prompt and the report in a dashboard page. Now you would notice that when we change the value in the dashboard prompt, the corresponding column in the report changes.

      

      

This can be useful in situations where you would like to change the columns across many reports. If all you have is a single report, then you would be better off with a single column selector view.

About these ads

8 Responses to “Oracle BI EE 10.1.3.3/2 – Dynamic Report Columns using Dashboard Prompts and Presentation Variables – Part 2”

  1. [...] Posts Oracle BI EE 10.1.3.3 – Configuring Delivers – iBotsOracle BI EE 10.1.3.3/2 – Dynamic Report Columns using Dashboard Prompts and Presentation Variables …Contact MeOracle BI Publisher 10.1.3.3.1 – Creating Applications using JDeveloper 11g and Web [...]

  2. Pushan Singhamahapatra said

    Thank you Venkatakrishnan for sharing this information. Nice work!! However on reading this post an idea came to my mind. What if the user wants to vary not just a specific column but the number of columns in a report? i.e what if we need something like a multiselect column selector? I am currently working on the same. You may also give it some thought in your spare time.

  3. [...] by Venkatakrishnan J on February 29, 2008 If you had gone through my blog entry here, you would have known how to go about dynamically varying the columns using presentation variables [...]

  4. Larry Smith said

    How do you handle a situation where your column may be in this format ‘GEOGRAPHY.COUNTRY NAME’ versus ‘GEOGRAPHY.COUNTRY_NAME’? I’ve tried it many ways but can’t seem to get it to work because it can’t resolve the column.

  5. Hiten said

    In that case you need to make entry as ‘GEOGRAPHY.”COUNTRY NAME”‘ …
    In case it is ‘GEOGRAPHY DIM.COUNTRY NAME’ you need to make entry as ‘”GEOGRAPHY DIM”.”COUNTRY NAME”‘..

  6. kurt said

    can you use this with FACT columns, or just Dimensions?

    I’m not sure what ‘variable column list’ means in the column formula, it gives an error.

  7. Raju Amin said

    How can one enable drilling on this dynamic columns? I am using following query to set dyanamic cols. each table is a dimension table but still i am not able to drill.

    CASE WHEN ‘@{GrainPor}’ = ‘SERVICIO’ THEN SERVICIO_VIEW_MV.SERVICIO
    WHEN ‘@{GrainPor}’ = ‘EMPRESA’ THEN EMPRESA_VIEW_MV.EMPRESA
    WHEN ‘@{GrainPor}’ = ‘TERMINAL’ THEN TERMINAL_VIEW_MV.TERMINAL
    WHEN ‘@{GrainPor}’ = ‘ROL’ THEN SERVICIO_VIEW_MV.ROL
    ELSE SERVICIO_VIEW_MV.SERVICIO
    END

  8. Rama said

    Good Post Venkat.
    This was very informative.

    Keep posting.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: