Sessions I will be attending today will be “SQL Server Storage Engine under the hood: How SQL Server performs I/O” (DBA-407) by Thomas Grohser and the “The Ultimate Session on Indexes” (AD-302-HD) by Victor Isakov.
Sessions I will be attending today will be “SQL Server Storage Engine under the hood: How SQL Server performs I/O” (DBA-407) by Thomas Grohser and the “The Ultimate Session on Indexes” (AD-302-HD) by Victor Isakov.
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?