How to convert Rows in to Column

building reports webintelligence
1

Hi Gurus,

I have two record for one person in the data base with different description. But i want to display this information in Single Row in the Report in WEBI like EMP NO Desription1 Description2 like that. I am using XIR3.1

Any Suggestions

Thanks

lovebo :india: (BOB member since 2010-10-06)

2

Is there a way to identify or seperate the two records from each other? if yes then perhaps this may help:

Data provider 1:
Select EMPID, Description
From Table
WHERE “Some filter condition to idetify one set of descriptions”

Data Provider 2:
Select EMPID, Description
From Table
WHERE “Some filter condition to idetify other set of descriptions”

in WebI report:
Merge the two data providers based on ONLY EMPID

create detail variable for each Description field as:
Description 1 = [data Provider 1].[Description]
Description 2 = [data Provider 2].[Description]
* Associate the Detail variables to the EMPID dimension

Pull All the three in your report:

EMPID DESC_1 DESC_2

rig (BOB member since 2010-10-22)

3

dude i am confused but according to what i have understood is

u have display some thing like this

EMPNO desc1 desc2
01 abc def

u can put both the [desc1]+[desc2] in formula bar

if your question is different can you explain the question

i am a bit new to this feild that will help me to understand the solution from others

mony (BOB member since 2010-11-02)

4

Hi Mony,

as i understood the problem,

The data is like:


EMPID    DESC
   1         abc
   1         xyz

But it needs to be displayed as:


EMPID    DESC_1       DESC_2
   1         abc             xyz

So, what I suggested should work if this is what the problem is and provided there is a way to seperate the two records based on some sort of logic

rig (BOB member since 2010-10-22)

5

Thankyou rig

got it buddy

mony (BOB member since 2010-11-02)

6

Thanks Rig, you got my issue exactly, will try and let you know.

Thanks lot

lovebo :india: (BOB member since 2010-10-06)

7

hi Quick question how to associate Empid with these two Detail variables in WEBI

Thanks.

lovebo :india: (BOB member since 2010-10-06)

8

when you select variable type as Detail, then it will give you an additional field below to select associated dimension…there you can associate it to EmpID

rig (BOB member since 2010-10-22)

9

yes i have tried that, but it is giving multivalue error, and one more question, in the merge dimension i need to remove other mergedimension fileds except the EMPId? is this correct?

Thanks

lovebo :india: (BOB member since 2010-10-06)

10

1) #MultiValue Error
Typically WebI exects 1-1 Relationship between the Dim and its associated Detail. (i.e. for any given value of the Dim it expects to have 0 or 1 matching value in the Detail) However, in some cases like this one, if there are more than one values in the Detail for each value of the Dim, then you need to check the “Avoid Duplicate Row Aggregation” option in the table properties to show all the values of the Detail.

2)

Yes. In this particular case, we need to make sure only EmpID is the common dimension.
Although I am not sure, other than the EmpID and the Description, what other fields you fetch in your Data Provider. Most probably, you will have to merge only on EmpID and convert all other fields as the Details of the EmpID for this particular case.
Do let me know if you have any additional fields that may be confusing you to decide whether they need to be Merged or Detailed.

rig (BOB member since 2010-10-22)

11

Thanks a Lot Rig,

I have removed all fields in the merge dimension except the Emm No, and created remaining objects as a detail object. Now i am getting the result as expected.

Thanks a lot Rig.

lovebo :india: (BOB member since 2010-10-06)

12

This solution is much simpler and if you are using Oracle, no need to use the case statement and use || instead of +

In fact you could actually use one object defined as

@Select(Class\Object Name) || ', '

Nniixx :australia: (BOB member since 2009-09-02)

13

Hi Balaji,

What is the problem in converting the table into a crosstab?

-------Desc 1–Desc 2
1--------abc ----xyz

Note: 1 is the EMP ID.

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

14

I’ve an interesting issue around Irfan’s post of aggregating text in single line.

I’ve a comment table where user’s comments are stored, and Irfan’s post helped me to aggregated multiple comments in to single line in the report level.

But since the underlying table is having multiple rows for the key-value pair i end up duplicating my fact table measures.

Is there any workaround to aggregate the comments and display it as single line against my fact table measure.

Eg:
comment table
Row1–Row2–Comment1
Row1–Row2–Comment2
Row1–Row2–Comment3

Fact data
Row1–Row2–10

Report
Row1–Row2–30–Comment1,Comment2,Comment3

Is there any workaround? or can we push string aggregation to the database level?

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

15

Hey,

would like to know too, why you just don´t use Mohammed´s hint. Use crosstab or horizontal tab or something like that. IN case you just have an “displaying” issue, this should fix it within no time.

AndreasF :de: (BOB member since 2011-01-13)

16

Hi All

All your input helped me so far, for this issue but I am getting my result like the below…
CUST 1 #MULTIVALUE
CUST 2 #MULTIVALUE

User wants - the result to be Like this

CUST1 abc abd abd
CUST2 eft efg

Please let me know.

bodiscussion (BOB member since 2010-07-11)

17

Thanks alot Rig

amar12312 :india: (BOB member since 2008-05-13)

18

Hi bodiscussion
I know this is an old post, but wanted to know if you were able to get a solution to the #MULTIVALUE error.

I have encountered a similar situation:

Customer No → Preference1 → Pref2 → Name → adrs
XX45 → XYZ → ABC → cathy → NY
XX67 → BCZ → ABC → mary → NY
XX87 → XYZ → → erin → TX
XX97 → → ABC → karen → FL

The database has one column for Preferences.
I have created three queries and merged on Customer # but get multivalue error when i pull in Preferences. The main Qry fetches customer details like name and addresses etc.

I was hoping if any of you gurus have a solution to this.
Thank you in advance.
Leo

Leo9 (BOB member since 2005-12-14)

19

I know this is an old post, but I wanted to thank ‘Nniixx’ for the solution. Its an excellent solution. Thanks for sharing.

MSR_BO (BOB member since 2009-05-01)