Cross Tab issue , Need a Help

[Former Member]

HI Expery

In BO Webi report need to show dynamic columns in Report after transposing rows to columns.

Data in Table is like below:

Col1 Col2 Col3

101 Abc 801

101 Abc 802

101 Abc 803

101 Abc 804

101 Abc 805

102 RNM 2009

102 RNM 2010

102 RNM 2011

103 RYZ 3465

In Report It should be as

Col1 Col2 Col3 Col3 Col3 Col3 Col3

101 Abc 801 802 803 804 805

102 RNM 2009 2010 2011

103 RYZ 3465

We have tried implementing the same using cross tab , but issue is in Cross col3 grows with all values present for all records. Here, if we use cross tab we are getting result as below:

Col1 Col2 Col3 Col3 Col3 Col3 Col3 Col3 Col3 Col3 Col3

101 Abc 801 802 803 804 805 2009 2010 2011 3465

102 RNM 801 802 803 804 805 2009 2010 2011 3465

103 RYZ 801 802 803 804 805 2009 2010 2011 3465

[Former Member]

Hi Ahmed

You can use the below logic.

col1 & col2 u can keep it as columns in a cross tab. Now keep col3 as row but here you need to do one thing . Click on the formula button for col3 field and then write the following syntax in it.

= col3 IN ([Col1];[col2])

By using this calculation context it will fetch only the data which is related to the first two columns. so the extra data which u were mentioning about will got to the next row.

Regards

Shyam

Edited by: Shyam Rapeti on Jan 25, 2012 3:20 PM

[Former Member]

Hi,

Try as below.

Create a cross tab for the following col's as

1.Columns->Col1 Col2

2.Rows->Col3

3.Body->Col3

You will get data as desired but it will have one issue. I.e #Multivalue error appears for all the rows as Col 3 is having measures.So to avoid this remove the borders to row heading and make font and backgroung color to white.

Cheers,

Suresh