I’m trying to build a simple (at least I thought it was) report which shows a matrix. Let’s just say for example I have a stored procedure which returns the number of bikes manufactured by plant, by color for each month between two dates I specify. Since we are a pretty low volume bike builder, we only make them in Red, Blue, and Black. Also, there are times where we only make one or two of the colors in a month. Finally, there are some months we take off and do absolutely nothing at all.

Here is the result of my stored procedure call when I want 3 months of data, from Jan 2011 to Mar 2011.

2/1/2011 Contoso Red 12
2/1/2011 Fabrikam Red 11
3/1/2011 Contoso Blue 8
3/1/2011 Fabrikam Red 17

And this is what the matrix report shows….

Month
Company Color Feb 2011 Mar 2011
Contoso Blue 0 8
Red 12 0
Fabrikam Red 11 17

However, this is not what I want. I want to be able to display all colors on the rows, and all dates on the columns, like this

Month
Company Color Jan 2011 Feb 2011 Mar 2011
Contoso Blue 0 0 8
Red 0 12 0
Black 0 0 0
Fabrikam Blue 0 0 0
Red 0 11 17
Black 0 0 0

Assuming that I cannot change the results of the stored procedure, but I could get all the colors and all the months, can I get it to work?

No related posts.