Webi Reporting Question

building reports webintelligence
1

Dear All,

I have a requirement to include dimension values in a single field…something like below,
Report is like this now,
SO number Serial Numbers
12345678 AWE123
12345678 AWE122
12345678 AWE125
98765432 QWE123
98765432 QWE126
98765432 QWE129
34567890 SDF4566
34567890 SDF4534
34567890 SDF4500

and this is how the user wants.

SO number Serial Numbers
12345678 AWE123;AWE122;AWE125
98765432 QWE123;QWE126;QWE129
34567890 SDF4566;SDF4534;SDF4500

Basically all the serial numbers under a particular sales order should be in a single line. I have attached an excel file that shows it a bit more clearer.

Can any one provide some suggestions, how we can achieve this?

Thanks,
Sharan.
bg.xls (9.0 KB)

sharancs (BOB member since 2010-01-19)

2

Hi,

You might want to check this

Hope that helps.

M H Mohammed :us: (BOB member since 2010-06-16)

3

If your database is Oracle 11g, then this will work:

select
SONumber
,wm_concat (distinct SerialNumber) as SerialNumbers
from
tablename
group by
SONumber

If your backend is not Oracle 11g, let us know what it is.

Mohan :us: (BOB member since 2002-08-15)

4

Hi Mohan,

Thanks a ton for your response…i am using Teradata 13

Thanks,
Sharan.

sharancs (BOB member since 2010-01-19)

5

Thank You Mohammed…the solution looks awesome…anyway we can do this from the reporting layer itself???

sharancs (BOB member since 2010-01-19)

6

I can’t think of a way to do this a the reporting layer but you can do it at the universe layer if the so# is a separate object from the serial number. Define the serial number as a sum measure and then concatenate a “;” to the end of the object.

wayne (BOB member since 2003-08-06)