Business Intelligence – Oracle

Oracle BI EE 10.1.3.3/2 – Recurring Sequence Numbers and Multi Line Column Display

Posted by Venkatakrishnan J on December 27, 2007

Another 2 interesting questions came up in the forums today. I thought i would blog about them since these are pretty common questions that come up during actual implementations. Lets look at both of them one by one

The first question goes like this (little bit modified so that we understand how it works followed by the actual method of implementation). “How do we generate Sequence Numbers in BI EE? Also, how do we make this sequence number to restart for every value of a specific column?”

The following picture shows what is actually desired.

      

So basically we want a recursive count or a sequence number for the entire report and also a sequence number that will give a recursive count for every Country within a Region. For Achieving the Sequence Numbers all we need to do is to have a column formula like the one below

RCOUNT(1)

      

So, what this does is it automatically determines what is the unique column within the report and will generate a ROW_NUMBER() OVER (PARTITION BY {Over that Column}) clause in the query. Now in order to get the recurring sequence number over every Region we need to have a column formula like the one below

RCOUNT(1 BY GEOGRAPHY.REGION_NAME)

      

Again, what this does it generates a ROW_NUMBER() OVER (PARTITION BY GEOGRAPHY.REGION_NAME,….) clause internally to generate the desired output. This is an extension of what we saw in my previous blog entry here

Lets move on to the second question. This question primarily deals with HTML formatting that we have seen before. But it is worth mentioning here. The question goes like this “I have a column which is concatenation of REGION_NAME and COUNTRY_NAME. But what BI EE does is it prints them side by side. What i would like to have is make them print one below the other”. The 2 pics below show what BI EE does by default and what the user actually desired.

      This is what he got      

      This is what he needed      

Achieving this is pretty straight forward. Use the column Formula below

GEOGRAPHY.REGION_NAME || ‘<br>’ ||GEOGRAPHY.COUNTRY_NAME

And change the data format of the column to HTML.

      

      

Again these are very simple but can be useful in a lot of situations.

Advertisements

2 Responses to “Oracle BI EE 10.1.3.3/2 – Recurring Sequence Numbers and Multi Line Column Display”

  1. Kathy Kimball said

    Excellent. Just what I needed. Been hunting for hours to find a solution. Thank you.

  2. […] Oracle BI EE 10.1.3.3/2 – Recurring Sequence Numbers and Multi Line Column Display page_revision: 2, last_edited: 1241303272|%e %b %Y, %H:%M %Z (%O ago) edittags history files print site tools+ options edit sections append backlinks view source parent block rename delete help | terms of service | privacy | report a bug | flag as objectionable Hosted by Wikidot.com — get your free wiki now! Unless stated otherwise Content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License Click here to edit contents of this page. Click here to toggle editing of individual sections of the page (if possible). Watch headings for an “edit” link when available. Append content without editing the whole page source. Check out how this page has evolved in the past. If you want to discuss contents of this page – this is the easiest way to do it. View and manage file attachments for this page. A few useful tools to manage this Site. See pages that link to and include this page. Change the name (also URL address, possibly the category) of the page. View wiki source for this page without editing. View/set parent page (used for creating breadcrumbs and structured layout). Notify administrators if there is objectionable content in this page. Something does not work as expected? Find out what you can do. General Wikidot.com documentation and help section. Wikidot.com Terms of Service – what you can, what you should not etc. Wikidot.com Privacy Policy. _uff = false; _uacct = “UA-68540-5″; _udn=”wikidot.com”; urchinTracker(); _qoptions={ qacct:”p-edL3gsnUjJzw-” }; […]

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

 
%d bloggers like this: