Data values in single line with commas in a field

building reports webintelligence
1

I have two coumns with values, I need to get the columns 2 values separated by commas in the same line, is that possible in BO ?
Currently I have the ActionType values in mutiple lines of single Claim(WindowsDesc)

(Current:)
WindowsDesc ActionType
Claim1 AD
IQ
Claim2 AD
IQ
Claim3 IQ
NV

I am looking for the format: (expected)

WindowsDesc ActionType
Claim1 AD, IQ
Claim2 AD, IQ
Claim3 IQ, NV

Note: If the above tables are not clear, we can find these table in the attachment too

Thanks
Kush
Forum.doc (31.0 KB)

Kush (BOB member since 2010-10-20)

2

You may achive this with a store proc.

Marfi :poland: (BOB member since 2006-12-18)

3

Hi Kush,

Go through this document and modify the formulas as you required. you can achieve this.

Regards,
Pradeep.
How to combine data from multiple rows into one cell in WebI.doc (30.0 KB)

av_deepu :india: (BOB member since 2010-08-11)

4

Hi Deepu,

Thanks a lot!!! It worked…but I am getting the values in reverse order like the below :frowning:

Claim1 ------ IQ, AD
Claim2 ------ blank (I had the values AD, IQ…but it is blank for this)
Claim3 ------ NV, IQ
Claim4 ------ NV, IQ

Is ther way i can acheive the correct order of the data values ?

I appreciate your response

Thanks
Kush

Kush (BOB member since 2010-10-20)

5

Hi kush,

In the formulas you used max(). so it is taking the max value i.e. IQ has higher ascii value than AD.

you can use Min() instead of Max(). but there is no Next() is available instead of Previous().

If blank is coming means then in the formulas check with if and IsNull()

do some work around in the formulas.

Regards,
Pradeep.

av_deepu :india: (BOB member since 2010-08-11)

6

This is a brilliant tip :smiley:

I dont know if it improves performance much, but you can utilise the reset_dim option in the previous function so that ‘Object 1’ does not get too big.

dfoster99 :uk: (BOB member since 2010-11-05)

7

Hi ,
how to achieve this for objects without numerical values?
Could anyone help me?

Kush,
Could you explain how did u achieve this?

ganeshb85 (BOB member since 2010-02-07)

8

I’m fairly new to Webi and I’ve been trying to get this working and thought I’d had some success, but there are a few problem. I want a report that looks like:

Groups | Users | Auditors | No of Records

I want show the number of records per group/user with the Auditors being the comma separated list which is associated to a particular group.

I initially thought this was working but then I notice the count of No of Records was being multiplied by the number of auditors. Therefore I thought I need to bring this out into a separate query and combine the 2 queries in the report.

I thought I could jsut create a basic query of group/auditor to create the comma separated list. This seems to do what need, but would not let me add it into the original report. I think this is down to the fact that I need to same dimension in each query to get the merge dimension to work properly. However when I added in extra dimension the auditor column seem to throw back random results. Sometime it would not return anything, other times it seemed to return the same auditor multiple times. I’m not exactly clear what it doing and the function help in BOXI is pretty poor.

Any help would be appreciated.

Paul

paul.sims (BOB member since 2011-06-13)

9

Great solution, thank you very much!

heckerf (BOB member since 2004-08-24)

10

There is a similar post with different workaround, may this would help too.

jprasanthram :switzerland: (BOB member since 2010-12-10)

11

Thank you very much for such a clever solution. It helped me a lot.
:lol:

Igor

iboulaevski (BOB member since 2011-07-13)

12

Thanks a lot for the solution

Moffika (BOB member since 2011-08-11)

13

Hi,

For the above solution, when the string length exceeds the pre-defined limit of 16387, it hits #OVERFLOW error. Is there any workaround to avoid this error?

Thanks,
Moffika

Moffika (BOB member since 2011-08-11)

14

Hi, this a longed-for and ingenious solution, props for that.

I am, however, facing the issue of it being too resource-heavy on the server. I am using it on a dimension which has around 3000 values.
I’ve found that unless I run it early in the morning, when there aren’t many users on, it drops the server. It takes about a gig of memory.

Have you observed something similar with this solution or does this happen only in our environment?

Hafsolace :estonia: (BOB member since 2010-08-27)

15

Hi,

The above solution can be simplified to eliminate #OVERFLOW Error, by adding the reset dimensions in the previous() function.

If reset dimensions are not mentioned explicitly, the length of [Object 2] increases with every append until it hits #OVERFLOW.

Considering the two objects from the above doc:

[Date] and [Code] - pulled into the query

1.Create an object called Maximum using the function below:

=Max ([Code]) In ([Date])

2.Create a concatenated object called Object 1 using the formula below:

    = [Code]+" , "+Previous(Self;[b]([Date])[/b])     [[b]Added [Date] ]object as a reset dimension.[/b]]

3.Create another measure object called Object 2 using the formula below:

           =[Object 1] Where ([Code]=[Maximum])

4.Finally create an object called Code(s) as below:

    = Substr([Object 2];1;(Length([Object 2])-3));[b][/b] [[b]For ]eliminating trailing comma and spaces[/b]]

Moffika (BOB member since 2011-08-11)

16

The interesting thing is that I’ve worked with a lot more complex reports, with multi-level sections, a number of chained variables that perform complicated calculations and on a lot more data. Sometimes the calculations can take 3-5 minutes but hell, they finish succesfully.

This solution, however, either runs in ~20 seconds or kills the server and everyone on it with about 3000 rows in the report.

It’s a pain to be so close to such a beautiful solution for a popular request, and yet find it to be unusable in its most absolute sense.

I hope someone can lavish mercy upon me (wink, wink Mak & Mark).

Hafsolace :estonia: (BOB member since 2010-08-27)

17

I’m getting a #MULTIVALUE as one of my string value (object [Codes]) so:

11/12/2011 - UK
14/12/2011 - GR, HU
15/12/2011 - US, BR, CH, SW
15/12/2011 - # MULTIVALUE

How to get rid of that?

Tiny :netherlands: (BOB member since 2004-11-10)

18

Taking credit for a solution someone else came up with ???

rimpa :india: (BOB member since 2008-04-14)

19

Hi,

Check this out
http://irfansworld.wordpress.com/2011/09/15/how-to-display-column-values-in-a-single-cell-in-web-intelligence-using-oracle-database/

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

20

If values want in reverse order then use below method:

  1. Create an object called Maximum using the function below:
    =Max([Cost Centre ID]) In ([Vendor Number])

  2. Create a concatenated object called Object 1 using the formula below:

=Previous(Self;([Vendor Number])) +","+ [Cost Centre ID]

  1. Create another measure object called Object 2 using the formula below:
    =[obj1] Where ([Cost Centre ID]=[maximum])

  2. Finally create an object called Code(s) as below:
    =Substr([obj2];2;(Length([obj2])))

anilkumarch (BOB member since 2010-05-09)