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.
three words: use a crosstab
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.?
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.
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?
this information is too important to have been left out don't you think? ;D
Quote from: annegirl on 17 Sep 2014 08:55:14 AM
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.
Quote from: annegirl on 17 Sep 2014 08:55:14 AM
Not even sure if there's a way of doing this?
Ok, thanks for the input! :)
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
@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...
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.
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...
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?