In Microsoft Dynamics NAV 2009 RTC Report Design, matrixes can provide a lot of easy functionality for a newbie report writer who wants to collate a lot of data without doing a lot of work. With that in mind, there are some significant drawbacks to using a matrix if your report has certain hoops to jump through that often mean that it may be easier to use a table. Note that while advanced NAV report designers can likely get around some of these obstacles by employing some of the less intuitive tricks available, this article is intended for novice users looking at whether or not a matrix control would be faster and easier to implement than the more traditional table control.
If you are not already familiar with what a matrix control is, the easiest explanation is that it is a way to bring the versatility of a spreadsheet pivot table to a report. The joy of working with pivot tables is that you can essentially take a mass dump of data and then organize it however you like and the pivot table logic takes care of the collating and summation without you having to write a lick of code. It bears mentioning that if you’re doing a onetime-only report that requires this sort of processing, you may actually be better off creating a dummy report that simply outputs all your uncollated data and then use the “About this report” feature to dump it into Excel and use an Excel PivotTable to have your way with it. Excel in that particular case is, in my opinion, far more versatile and flexible for the neophyte than trying to figure out a matrix.
For the novice, I do strongly recommend reading up on Small Square Services’ own introductory “how to” for setting up a matrix. If you are like me and you like to have points of comparison so that you can see how multiple authors approach matrixes I can also recommend Claus Lundstrøm’s tutorial on the MSDN blog.
While the focus of this post is to show when not to use a matrix, it’s important to understand why you would want to use one in the first place. Simply put, matrixes are easy to make. You don’t have to process, sort or collate data – all you have to do is create a simple classic report layout that contains all the data you need and then put a matrix control with the proper groups on a page. For many folks that don’t favor writing a lot of processing code, this is certainly an appealing notion, especially as you can define much of the look, feel, and content through the visual designer interface. If you want fancy processing but do not want to spend a lot of time on it, a matrix can be a “dream come true” control in the Visual Studio Report Designer.
If a matrix is so powerful, why not use one all the time? What are some of the features of a report that would cause a newbie report designer to bang their head against the nearest solid object?
One of the great things about matrixes is that they collate data and create dynamic rows and columns to display that output. When something is created at run time, it greatly limits our ability to modify how that data is shown, if some of the data is to be singled out for special treatment. You can do some very useful things with the expression based attributes of matrix cells but if you have anything in a matrix that needs to have different data (as opposed to different appearance) in certain columns, then a matrix is going to likely not be the best solution. An example would be if you want to provide monthly totals with quarterly totals and averages. Trying to add those averages into a mix of totals is possible to do, but you’ll have to employ some more advanced tricks. If that’s what you want to do, tables are probably going to be easier in the long run than trying to squeeze this square peg into a round hole. It is worth mentioning that the very snazzy looking Tablix* control will eventually remove this concern, allowing us to mix matrixes with tables with impunity.
This reason is likely the most important one of the three I list here. It is possible to spend a lot of time wrestling with the matrix control to try to get it to insert an “average” column after the main body but before the totals. Or if you have three columns per reporting item but your totals only make sense for two of those columns and you want to eliminate or hide the third.
Matrixes provide very handy “subtotal” columns that you can add for any column or row grouping. If all you want are subtotals across a data slice, then this is just another marvelous feature of matrixes. If, however, you want totals for some of the data and averages for some of the data, then you’ve essentially got to provide code to the InScope function that will dynamically do one or the other, which likely exceeds the newbie threshold.
Matrixes are primarily a tool for collating repetitive data. If your data is relatively flat and/or doesn’t feature repeating categories, then a table with groups is probably going to offer more flexibility with less hassle than a matrix.
Matrix is a very powerful control, and learning how to use it will be key for great Report Design in Microsoft Dynamics NAV 2009, but always make sure to think if it’s the right powerful tool for the job.
*RDL 2005 is used in Microsoft Dynamics NAV 2009 for the RTC Report Designer. RDL 2008 has more powerful options for controls (such as the Tablix control), grouping, and various other improvements. Hopefully the next major release of Microsoft Dynamics NAV will feature the RDL 2008 improvements.