Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Dynamic Column Headers using Presentation Variables, Sets and Conditional Formatting

Posted by Venkatakrishnan J on January 25, 2008

Yesterday we saw how to dynamically vary the columns in a report based on the column that we choose in a dashboard prompt. But if you had noticed, the column header in that report would have had a constant value. I got this question yesterday via email, whether there is any way we can make this column header dynamic. Well, today we shall see how to go about achieving dynamic column headers. Actually if you go to table or column headings in a table view you would notice that it accepts only static text. One cannot enter presentation variables and make it to convert them to their actual values. So, in order to achieve the dynamic column headers, we start with the same report from where we left off yesterday here. The idea is very straight forward. It consists of the following steps

1.   Hide the actual Column and Table Headers.
2.   Cast all the columns to CHAR.
3.   Decide on the name for your static column headers.
4.   Create conditional formatting on the all the columns and make them to display in a specific color whenever data matches the Column Header Names.
5.   Add a new criteria and combine it with the older one using UNION ALL. The columns in this criteria would contain the Actual Column Header values that you want (dynamic).
6.   Make these values to display at the top of the report.

I know it might be a bit confusing why we are doing certain operations above. We shall go through each of the above in detail. Lets take the same dynamic report that we created yesterday.

1.   Go to the table view properties and hide the actual column and table headers. We are doing this because our plan is to use a data element from the table itself(which we shall generate later using UNION ALL) as the column header instead of the actual column headers.

      

2.   Cast all the numeric columns to char since we would be appending non-numeric column headers which would come from another data set. Now decide on a column header name for all the columns that you have in your report. For example, in our report, one column header name would be dynamic (which would come from the presentation variable @{Geography} and the other Column header would SALES

      

3.   Once you have the column header names decided, now add conditional formatting to all the columns in the report to something like this i.e. whenever SALES1.SALES = ‘SALES’ (the static column header name that we decided earlier) then add a background color of light green. In my case. i have added the below conditional formatting to both the columns.

      

4.   Now that we have added the conditional formatting, next is we need to add the criteria that will add our necessary column header values to the report data. So, use UNION all to combine the new criteria with the older one.

      

Add some dummy columns and in the formula enter the column header values. In my case ‘{@Geography}’ and ‘SALES’. Ensure that you include atleast one column in the formula as shown below.

      

      

5.   The last step is to make the new data that we added to display at the top of the report. So, now add 1 new column to both the criteria and enter 2 in formula tab for the one containing the data and 1 in the formula tab for the one containing the column header values. Sort the results on this column.

      

      

6.   Now save this report. And go to the dashboard. You would have dynamic column headers.

      

      

I know the above procedure is too tedious for achieving a very small functionality. But again, if your users absolutely need it and you dont have any other options just use this approach. Just remember this works only for table views.

About these ads

13 Responses to “Oracle BI EE 10.1.3.3/2 – Dynamic Column Headers using Presentation Variables, Sets and Conditional Formatting”

  1. Kurt Wolff said

    Not only tedious, but casting all columns as chars has obvious problems. Clever, though.

  2. Venkatakrishnan J said

    You are right Kurt. But as i said only if a user does not have any other options left and if the users absolutely demand it, only then should this be used.

  3. chandrasekhar said

    Hi Venkat,

    Can you tell me how to display a popup window when i move my mouse over the column heading

  4. Tina said

    Hi,Venka
    It seems we cannot filter the dynamical column using presentation column…

    Regards,
    Tina

  5. Venkatakrishnan J said

    Tina – I am not sure what you mean. Can you explain a bit more?

  6. anusha said

    is conditional navigation possible..
    based on different values of a column, it should navigate to different dashboard pages…

  7. Venkatakrishnan J said

    Anusha – Yes it is possible. Check my blog entry here https://oraclebizint.wordpress.com/2008/03/05/oracle-bi-ee-101332-conditional-drills-and-dynamic-tool-tips-html-formatting-and-go-url/.

  8. Pravin said

    Hi Venkat,
    Can you please let me know how to create dynamic column header in 7.5 version as we don’t have much of options discussed above.I know we are using a very old version which most of times leaves us with no choice,but anykind of suggestions will be a great help for us.
    Thanks a lot.

  9. Arpado said

    Hi,

    This solution is very usful an clever.

    However i have a problem. I created a multipage table report (more than 25 records). When I jump the second page, the header disappears :(

    How can I fix this header to all pages?

    Thanks a lot.

    Arpado

  10. Arpado said

    Hi,

    This solution is very useful and clever.

    However i have a problem. I created a multipage table report (more than 25 records). When I jump the second page, the header disappears :(

    How can I fix this header to all pages?

    Thanks a lot.

    Arpado

  11. Sanjeet said

    Hi Venkat,

    I have the same problem which Arpado is facing. Your technique for getting the column header works fine but only for the first page.
    As soon as we move to the next page, the column header disappears.

  12. Pratik said

    Hi,
    This does not work when you use RSUM function for a fact. Do you have any idea why this does not work?

  13. Adam said

    Hi Venkat,

    I want ask you that in single column heading i would like to acheive two names like
    (Period)  — 2008/09 Period 02 May
    (Metrics) — Mth Amnt £k

    Is this possible

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 151 other followers

%d bloggers like this: