Design Ques (combine data for multiple rows in 1 cell) !!

building reports webintelligence
1

I have a a requirement where I need to display data in a particular way and I haven’t been able to achieve this.

This is what I have:

Date ***** Group

4/10/2009 Y4T
4/10/2009 8X4
4/10/2009 ZTU
4/10/2009 1BZ
4/10/2009 1C1
4/24/2009 AB1
4/24/2009 8X4
4/24/2009 C3E

And this is how I need to display it:

Date ***** Group(s)

4/10/2009 Y4T,8X4,ZTU,1BZ,1C1
4/24/2009 AB1,8X4,C3E

Is this possible in WebI ?

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

2

This is a very popular request that has been coming. :slight_smile:

I would suggest you to search with keyword ‘horizontal*’ to get many topics including this one.

Jansi :india: (BOB member since 2008-05-12)

3

This is different from what I am looking to achieve . I need to combine data from multiple rows into one column.

I cannot change it into horizontal table because there are more columns in the report . Those column display measures that summarized by Date .

I need to combine “Group(s)” by Date so that I have one line item for a particular date.

I found some topics when I did a search, but none of them have a definite solution .

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

4

I don’t think you can do this (in WebIntelligence) without creating a second, dynamically positioned, table over your first block.

Would displaying the date range in this cell be of benefit to you :?: =Min([Date]) +” , “+Max([Date])

Joe Szabo :us: (BOB member since 2002-08-19)

5

I have been able to find a solution for this. I saw various posts out here but none of them had a simple Report level solution.

I am attaching a document which shows how I got it to work within WebI :

I am hoping this will help lot of people out there …
How to combine data from multiple rows into one cell in WebI.doc (30.0 KB)

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

6

i’m getting the multivalue error using this solution. how do you handle it?

thanks.

joelmb (BOB member since 2008-06-24)

7

I get two rows, one with all the values and the second with a multivalue#. Were you able to get rid of the multivalue#?

Thanks in advance.

SST :us: (BOB member since 2008-03-25)

8

rimpa,

Are you on XI 3.1 SP2 or are there any fix packs applied? I think it works on Fix Pack 2.3 but would like to confirm.

Thanks in advance

SST :us: (BOB member since 2008-03-25)

9

Rimpa

Thank you for this solution! I had a similar requirement and had no idea how to provide it for our client.

I am sure this will be very helpful for many, many people. :+1:

REB01 :us: (BOB member since 2004-11-29)

10

Rimpa,

Its really cool … But what if i add one more object in to the query, the result is repeating?

Can you throw some light on it :smiley:

zaif235 :us: (BOB member since 2010-06-15)

11

Rimpa,
I get Multivalue error for Object1, object2 and codes.
Followed the steps in the document.
Any more advices.

Thanks

Adious (BOB member since 2009-10-06)

12

I have a date and product field

Date …Product
10/11/2010…HY
10/11/2010…PTH
10/11/2010…GT
10/11/2010…DS
10/12/2010…HY
10/12/2010…GT
10/13/2010…PTH
10/13/2010…GT

I want it in the below way:

Date …Product
10/11/2010…HY,PTH,GT,DS
10/12/2010…HY,GT
10/13/2010…PTH,GT

Products are not limited, they change periodically.

Need a solution to make it work at universe level or at Database level(SQL 2005).

Adious (BOB member since 2009-10-06)

13

Hi,
This is really helpful.
One step further, what I have to show is (as in the document example attached by rimpa) only the date field rows which have more than one Code(s) available and if a date is associated with only one code, then hide it, any idea how can we achieve it.
I am somehow trying to see if we can show only values which contain commas. I am on WebI XiR3.1 and oracle is the backend.
Any help is appreciated.
Thank you
Singh

singh :us: (BOB member since 2005-09-26)

14

Two options to filter on:
Where count(code) in date =1
Where the pos value of the comma in your string = 0

15

Thank you for your reply,
Can you please elaborate,
so what I understand is, I have to create 2 variables at report level,

first_A: count(code) in date =1

what this does is it gives 0 for all the value which have more than one code(s) associated with it and “1” where there is only one Code(s) is available, when I include it in the display table (Date and Code(s) columns). How can I filter the 1 and display only the values whose first_A variable is 0.

Second, I did not get properly, can you please explain more.

Thank you.

singh :us: (BOB member since 2005-09-26)

16

1/ Click on show report filter, drag first_A over and manually enter 1 as the value you want to keep

2/ In your delimited string, you are searching for the position (using Pos) of the first comma. If it is a single code, then there wouldn’t be a comma, so Pos would be 0. Therefore keep all rows where Pos=0

17

Hi Thank you again,

But when I try to drag the first_A object, it does not go since it is a measure variable (aggregrated using count function).

For the second half, string part, so does the below looks correct?

second_a = Code(s) where Pos(","; “”)=0

Thank you…

singh :us: (BOB member since 2005-09-26)

18

It will work - it should drag into the filters section.

second_a would be something like

=If(Pos([Codes];";")=0;1;0)
19

Hi Mark,
Thank you for your help, this is what I did,

  1. Created another measure variable as Count(Code)
    = Count(Code)In [Date] = 1
  2. Insert a vertical table and dragged the above columns, Count(Code), Date, Code(s).
  3. Now, select and right click on Count(Code) column rows, select Input Control, select the Count(Code) variable from the list and clcik next, then Single Value–>Entry Field and from the input properties, change default value as 1 and Operator as Not Equal to, click Next and Finish.
  4. Boom, there we go. :smiley:

Thank you again.

singh :us: (BOB member since 2005-09-26)

20

Good stuff - it’s always difficult offering a precise solution without seeing the actual report but at least you’ve extending the answer to a solution. :+1: