I’m creating a report in full client. I have the table in the following format:
Category Frequency IsTrend Sample Numbers if Trend = Yes
A1 0.80 Y 200
300
A2 1.50 N -
A3 0.70 Y 400
500
My question is : I would to display the data in this format:
A1 0.80 Y 200, 300
A2 1.50 N -
A3 0.70 Y 400,500
Is there a way to concatenate the sample numbers currently in different rows into a single cell separated with comma. The sample numbers are extracted from a variable called : “Sample Numbers if Trend is Yes”
There isn’t a function in BO to do this. The BO-only solution would be to do something ugly with multiple aliases and outer joins in Designer to concatenate values from the alias if they exist.
I’ve faced this issue before and a better way to handle is at the database level. If you are using Oracle, create a custom function to concatenate the values. Here is an example where I had to concatenate multiple disease states for grants which exist in one row per grant id - disease state combination and can have one or many disease states per grant.
create or replace
function get_transposedDS( GID in Integer )
return varchar2
is
l_str varchar2(2000) default null;
l_sep varchar2(1) default null;
begin
for x in ( select DISEASE_STATE from GRANT_DISEASE_STATE where GRANT_ID = GID ) loop
l_str := l_str || l_sep || x.DISEASE_STATE;
l_sep := ',';
end loop;
return l_str;
end;
Next, I created a derived table in my universe called Grant_All_Disease_States as:
SELECT GRANT_ID, get_transposedDS(GRANT_ID) AS DISEASE_STATE_LIST
FROM GRANT_DISEASE_STATE
GROUP BY GRANT_ID
Finally, my universe object Disease State List defined as:
Grant_All_Disease_States.DISEASE_STATE_LIST
The end result of this is that if I queried the following data:
GRANT_ID DISEASE_STATE
----------------------------
1 X
1 Y
2 A
3 B
3 C
3 D
…I would see:
GRANT_ID DISEASE_STATE_LIST
----------------------------
1 X,Y
2 A
3 B,C,D
Unfortunately there isn’t a clean way to do this straight from Designer. The database function can handle a variable number of strings to concatenate. In Designer, you can alias the table with the strings to concatenate and do a seriea of outer joins possibly, but it is complicated, ugly, and may have a performance hit.
Hi This Can be done by creating a simple object in the universe level.
Suppose you have company Type under which Number of companies will be there and if you want to display the company names of Company Type in a single cell. then Create a object ’ ‘+company name+’,’
Made this object as measure. Then drag company Type and Name to your report then you wll see the data like
Company type Company Name
A x,y,z
B k,l,m
But there will be some limitation on the number of characters it is going to display, If data exceeds that limit then it will through #Overflow error
I did what divya mentioned, but strangely for only few Company types the order in which Company Names are listed is in Descending order. As i mentioned this is not the case for all the Company types but only for few.
For example:
Expected result:
CompanyT CompanyN
A a,b,c
B a,b,c
C x,y,z
Actual Result:
CompanyT CompanyN
A a,b,c
B c,b,a
C x,y,z
Why is this happening? Any help? We are using XIR2 against DB2 database.
(BOB member since 2005-02-24)
(BOB member since 2005-05-10)
(BOB member since 2004-06-30)
(BOB member since 2002-08-19)
(BOB member since 2008-03-14)
(BOB member since 2008-03-14)