When writing reports for my various work projects, I’ve always tried to work as fast as possible, and tend to ignore the things that don’t make sense strait off the bat. These things tend to take a lot more time to learn and figure out. I never really have a lot of time, so I find another way that is faster at the time.
One of the things that I never got to play with was the Matrix control in the Microsoft reporting services. It seemed logical enough, but every time I looked at using it, the damed thing never worked, so I went and used the List control instead. This time, I was writing a report that needed to have a different number of columns depending on the number weeks in the month being reported on. So, naturally, I thought that the Matrix control would work well.
Here’s an example of what the data that I started with looks like. I was able to pull this out of my tables with a (relatively) simple sql statement.
Next I threw the Matrix control on a new report, and using the report printer that I prepared earlier in Microsoft SQL Server Reporting Sevices, I got a blank report printing out to pdf (yay!).
After a little trial and error, which I had time for this time (accounts REALLY wants this report), I finally found the Groups tab on the Properties page of the Matrix control. This was the key to getting it to work correctly. After that, it was clear sailing.
As you can see, I’ve added two Row Groups, and 1 Column group. The row groups determine what is shown in each row, while the column groups determine how what is shown in the columns. I know, it sounds like I’m doing the dummies guide, but really I do remember thinking that exact line while actually doing the work, followed by “ooh, so that’s how it works”.. guess I really am a dummy.
Anyhoo, this is what the relevant part of the group edit screen looks like. Incedently, the row groups were on section name, then staff name.
Then I added the field values into the grid items that were available, and after a little trial and error, came out with this tiny, little thing.
Although it’s tiny, it has big results.. take a look.. ooh pretty…
