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.
Posts
