WEBI : Dimension values not displayed in the same row

[Former Member]

Hi Everyone

I'm able to create cool complex reports but this one is driving me crazy, so if you guys can help me on it would be fantastic so thanks in advance.

Let's assume the following :

I have two dimensions : "Question" and "Answer"

The report I would like to design should look like this one

What I tried to do is proceeding by name and started with Lynda's case , in the top value below banana 2 I added the following function : =[Answer]Where([Attribute="How many bananas did Lynda eat")]

The good part is that I had the right answer 2 but the bad part is that when I moved to the second fruit Apple and I added the following function into the cell : =[Answer]Where([Attribute="How many apples did Lynda eat")] I had the following table :

The dimension value didn't want to go into the same row and it instead created a new row "Lynda" and added the value under. Note that the answer dimension values are in "text" and not "numbers".

Can anyone please help me on this or suggest another way to have my table done

Thank you very much

[Former Member]

Hello Sarah,

You can hard-Code the Horizontal Column values as you are having Fixed Values i.e Lynda, Milo, Jack etc and also for Vertical Columns i.e Banana, Apple and Kiwi.

In Cross Tab Measure value you can write below formula in each cell just changing values as per Column and row value.

Formula : =[Answer] Where(Match([Question];"*banana*") AND Match([Question];"*Lynda*"))

You can to replicate this formula in 12 cells with change in value for Fruit and person name.

Regards,

Sushil Padhye

[Former Member]

Thank you

Your method gives the right answers but each function is applied on the whole row which results in having many rows as I have show in the last table. Any suggestion to indicate that the function should only be applied on the cell and not the whole row.

Regards

Sarah

[Former Member]

Hello Sarah,

While creating Cross Tab.. don't add any dimension either in Column or Rows.

Just take Cross Tab Element from Report Element Tab and Write Free text in Column and Rows.

Insert New Column to Right and add Column Name. Like add three Columns and Write "Banana", "Apple", "Kiwi" respectively. Same do for Rows and manually add Rows below each Row.

Then Write this formula for each Cell. This should allow you to write formula in Each cell instead of Rows.

Try and let me know in case this works.

Regards,

Sushil Padhye

[amitrathi239]

Hi,

might be you can try with Match function.

create two variables.

V answer=if(match([question]);"*Banana*) then "Banana" elseif(match([question]);"*Apple*) then "Apple" elseif etc...

V Question=if(match([question]);"*Lynda*) then "Lynda" elseif(match([question]);"*Milo*) then "Milo" elseif etc...

After that use these variables in the crosstabto display the count.

Amit