Need guidance in how to merge this data

[ChrisF79]

I have a user asking for one report that has the following data:



The challenge for me here is that the left part of the chart is based off of 2013.  The middle part calculates the growth based off of 2012 sales figures (obviously needing 2013 sales figures for the calculation) and then the third part just divides the year 2013 data to formulate a product mix.

Whew!  How could I go about putting this all on the same report with different time periods?  I tried 3 different crosstabs but I'm honestly a bit lost at this point.  Any guidance or a shove in the right direction would be greatly appreciated.

Thanks

[Lynn]

Are you using a relational source or a dimensional source?

[ChrisF79]

It's a dimensional source.

[Lynn]

No Lions and Tigers and Bears? Oh my!  ;)

Attached is a sample built from the sample package "Sales and Marketing (cube)" that might illustrate some concepts that I hope will help you.

I used a random group of order methods to simulate your pets dimension.

[ChrisF79]

Sadly, our Cognos admin did not install the same cubes.  I really appreciate the effort though.

[Lynn]

I think you can still open the report in Report Studio and look at the expressions. You just won't be able to run it or validate or anything.

Attached is what it looks like when it runs.

And get those admins off the dime to have the samples installed!! They are really useful to have, especially for support purposes.

[ChrisF79]

The report you attached is PERFECT for what I'm doing.  I guess perhaps what I'm having problems iwth then is not knowing how to create the YOY calculations.  Are you just putting a query calculation in?  If so, what's the query calculation look like ofr the YOY Mammals?

[ChrisF79]

Also, Lynn... is that just one query that is producing that report?  Or, are you creating multiple queries?

[Lynn]

One query. Done in one crosstab. I created a set of four order methods to represent mammals. The set of the other three order methods I used for snakes.

The query calculation for the YOY part uses the aggregate function to subtract 2004 from 2005 and then divide by 2004 to compute the growth. Just change the mammals set to the snakes set for that YOY growth. For the Pets growth, just omit the critters altogether and just aggregate within the year.

The part showing 2004 vs 2005 and also Product Mix are just text objects I dragged in to make the presentation a bit more readable. Hope this gets you there!

Code Select Expand


(
aggregate ( [Revenue] within set [2005], [set(Telephone, Web, E-mail, Fax)] )
-
aggregate ( [Revenue] within set [2004], [set(Telephone, Web, E-mail, Fax)] )
)
/
aggregate ( [Revenue] within set [2004], [set(Telephone, Web, E-mail, Fax)] )


*Edit* I should point out that [set(Telephone, Web, E-mail, Fax)] is actually a query item I created by selecting specific members from the order method dimension and dragging them in to create a set. This is the default name for the query item and I didn't bother changing it.

[ChrisF79]

I got it to work!  Couldn't have done it without your guidance.  Thanks so much, Lynn!

[Lynn]

Hooray, very glad to hear it! Well done!

[ChrisF79]

Sorry to bug you again but I just had one minor tweak that surprisingly isn't working.  since mine is set up the way your .doc attachment is, I'm basically trying to sort the report on Total Pets descending.  I click on the Total Pets heading, go to sorting and choose descending.  When I run it though, there isn't any sorting.  Have you ever come across that?

[Lynn]

Not a bother at all.

Actually sorting works a little differently in the dimensional world. Total Pets is a calculation that only gains context by the rows and columns it is nested within, so although it seems sensible to click on it for sorting, what you really want to sort is the rows based on their value at their intersection with that metric.

So click on your sales reps set and then sort by value (rather than by label), and specify the total pets calculation as the basis for sorting.

Hope that helps!

[ChrisF79]

Fixed again, thanks!