The Magical World of Rectangles in Dynamics NAV RDLC Reports
One of the challenges of report writing in Microsoft Dynamics NAV’s (Navision) Report Definition Language Client (RDLC) reports is creating multiple repeating data sections pulling from a single dataset. The easiest example I can think of comes from sales orders: You want to have your sales order show the sales lines attached to the sales header, and then the sales comments attached to the header, all in the same report.
The challenge of doing this is that, NAV flattens your dataset for the different sections, but it wants the display for your report to be based around a tablix object with a page header and footer. The tablix is based on your dataset, and it repeats for every row in your dataset. You can think of it like a big spreadsheet, where each row is a record touched by your report (regardless of which table the record is in) and each column is a data field to show in your report (regardless of which table the data field is in). In fact, if you exported the dataset for our sales order report into Excel, you’d see the header information over and over again on each line, and then you’d see rows where the sales line fields were filled and the sales comment fields were null, and after that you’d have rows where the sales line fields were null and the sales comment fields were filled.
If you try to handle this with one tablix, you’ll get frustrated pretty quickly. You’ve got to mess around with column sizes and merging columns and un-merging columns and hiding rows based on whether or not the values from the sales line or comment are null, and it’s infuriating. Everything has to be balanced just so, and even then, the layout looks pretty ugly. (As someone who started out in the world of IT by writing HTML back in the 90’s, it reminds me of how we’d throw our web page layout into tables to get it to work with columns, and then we’d put tables in our tables to get the sub-sections to look right, and then there’d be tables in the subsections, until you had 38 tables inside each other, like a Russian nesting doll. And if you were really unlucky, the person who’d coded the page before you got to it didn’t bother indenting the code for the tables, so you had a bunch of incomprehensible markup and you’d have to spend an hour figuring out which layer of table you were in to change anything. I don’t miss the days when I had to agonize over web page layouts.)
There’s a better way than trying to do everything in one tablix, though. The secret is to create a rectangle, then put one tablix in the rectangle for each table in the report that you want to display, and then hide the tablix when you’re not looping through the table it was intended to display.
To explain: First, start by deleting the default tablix for the report that’s in the body section of the layout. Next, create a rectangle object in the body section of the layout, and then put a new tablix in the rectangle for each table you need to show data for. Set up the columns and cells of the tablix for the data you need to display. If you run the report at this point, you’ll see a whole bunch of extra rows in your tablixes. (Maybe that should be “tablixs” or “tablux”? I honestly don’t know what the plural of “tablix” is, so I’ll punt. Someone can feel free to correct me in the Comments if I’m off-base.)
The secret to getting rid of those extra rows is actually quite simple. Pick a field in each row that will always have a value—you can actually put fields in your dataset specifically for this purpose and just not display them. For the sales order example I’ve been using here, I’d use the Sales Line.Line No. and Sales Comment Line.Line No. fields. Go to the Row Groups for the tablix in the report designer and find the lowest-level Details section for the tablix. If you select it, you’ll be able to see all the properties for the section in the Properties window. Set the Hidden property for the tablix member to an expression of IsNothing(Fields![your field name here].Value]). That will make the system do an evaluation for each row in the data set so that if the dataset has a null value in that field, the tablix won’t show that row.
And that’s really about all there is to it.
If you have any further questions about this or other development issues, please contact one of our development experts at ArcherPoint. If you enjoyed this blog, you might like to read more of Tom Hunt’s blogs, or check out our collection of Development Blogs.
Special video gamey update: I am hyped for Street Fighter V. I am so hyped for it that I went out and bought a PS4 for it, even though the game won’t be out for at least a year. I’m reminded of the halcyon days of my youth, when I was so excited about Final Fantasy VII that I bought a PlayStation 1 a year before that came out. (I actually still have the t-shirt I got for pre-ordering FF VII, by the way.)