Transpose Rows to Columns

[annegirl]

Hi, I was just wondering if there's a way to transpose a particular data item so that its values show up as columns rather than rows.

So for example, if my query has data items Year, Quarter, Revenue, and Product, and "Product" has the items apple, banana, and grapes, I would like to find a way to have apple, banana, and grapes as individual data items themselves so that they can show up as columns.  So instead of showing this:

Customer       Year        Revenue         Quarter           Product
A                   2009        $100              4                    Apple
B                   2010        $150              2                    Banana
C                   2011        $200              1                    Grapes

I would like to modify the data so that it looks like this:

Customer       Year        Revenue         Quarter           Product1     Product2      Product3
A                   2009        $100              4                    Apple         Banana        Grapes
B                   2010        $150              2                    Banana      Grapes
C                   2011        $200              1                    Grapes       Apples

So basically, I would like to see all of the products that, for example, customer A has purchased in a single row.

I hope I have explained myself sufficiently!  Any help would be greatly appreciated.
       

[Francis aka khayman]

three words: use a crosstab

[BigChris]

Would a crosstab work for this though? anniegirl wants the product to go in the value field of the crosstab, and I'm not sure how to easily do that.

How would you determine which product goes in Product1 etc.?

[Francis aka khayman]

Customer       Year        Quarter           Apple     Banana      Grapes         Total Revenue
A                   2009       4                    50            25               25                  100
B                   2010       2                    0              100             50                  150
C                   2011       1                    100          0                  100                  200

hopefully this is close enough to what you want or can be manipulated to achieve the requirement.

[annegirl]

Yes, exactly as BigChris said, a crosstab won't work because I need to ultimately join this query with another one, so I need to be able to compare "Product1" from query 1 with "Product1" from Query 2.

So the goal is to put all the products that a customer has purchased in a single row so that I can ultimately cross-reference this with another query that already has the products as individual columns, in a single row.

Not even sure if there's a way of doing this?

[Francis aka khayman]

this information is too important to have been left out don't you think? ;D

So the goal is to put all the products that a customer has purchased in a single row so that I can ultimately cross-reference this with another query that already has the products as individual columns, in a single row.

not in report studio. at least not easily. you might have a less hard time in FM. but if I were you, better talk to your ETL person or the database person who is good with SQL.

Not even sure if there's a way of doing this?

[annegirl]

Ok, thanks for the input! :)

[cognos810]

Hello annegirl,
If your Product List is Static, i.e. you will ALWAYS report only on Apple, Banana and Grapes then you can create three data items, one each for each product.
Apple Revenue
==========
CASE [Product]
WHEN 'Apple' THEN [Revenue]
ELSE 0
END

Banana Revenue
==========
CASE [Product]
WHEN 'Banana' THEN [Revenue]
ELSE 0
END

Grapes Revenue
==========
CASE [Product]
WHEN 'Grapes' THEN [Revenue]
ELSE 0
END

Then drag and drop these data items in a list.

Thanks,
Cognos810

[BigChris]

@cognos810 - I don't think that's the result that anniegirl is looking for. To put it into words, I think anniegirl is trying to get something along the lines of "In 2009 Quarter 4, customer A made a revenue of $100 from Apples, bananas and grapes. So she needs to somehow get the apples, bananas and grapes into a single field, in some way concatenate those data elements into one field.

The only solution I can think of, and I haven't tried this, is to have a three queries, one for each of the fruits, You'd filter the first one for grapes and calculate the revenue. You'd also have a field called Grapes into which you calculate a text field with the word "Grapes" in it. Then you'd have two more columns called Bananas and Apples - put a Null into each of those. The second similar query which you filter for Bananas, and you put "Bananas" into the Bananas field and Null into Apples and Grapes...I think you can guess what to do for the third.

Once you've done that you then union those queries together and for each customer / quarter you calculate the sum of the revenues and maximum([Grapes] for [Customer],[Quarter]) + maximum([Bananas] for [Customer],[Quarter]) + maximum([Apples] for [Customer],[Quarter])

Anyway, have a try with that and see how you get on...

[Francis aka khayman]

here goes nothing...

there is already a second query with products as individual columns in a single row. perhaps she can check how that query was made and apply the same technique to the first query.

[annegirl]

Thanks all for the input. 

BigChris, yes, I had that same thought, but unfortunately I have approx. 20 different products, so having 20 separate queries probably isn't the best idea :)

Khayman, the second query you're referring to is coming from a different data source in which the products are entered as columns.  This isn't possible to do with the first, as we're pulling it directly from our financial systems, and that is how they enter it.

So it's looking like there isn't really any way of doing this in cognos I guess...

[CognosPaul]

You could put a repeater into the list with the expression: [Product Name] + ', '

Use the same query and set up a master detail between the list and repeater.

There may be another way with 10.2.1 FP3. Which version are you using?