Flattening Data Sets in RDLC Reports for Easy Development
Back in the day of the old Microsoft Dynamics NAV Report Writer, we got to do a distinct section and loop for each table we were going through with the report. But in the fast-paced future world of RDLC reporting in the Dynamics NAV RTC (RoleTailored Client), we have a very different reporting model.
I have this request from a customer that I’m working on right now. They want a report listing items on sales order and on hand (but not allocated to a sales order) by serial number. In the old Dynamics NAV report writer, I would have created a DataItem for the Item table loop, then two sub-DataItems under Item, one for Sales Line and one for Item Ledger Entry. Then I’d have the appropriate fields put into a section for my sub-data items and go on with development from there.
In Dynamics NAV 2013, the report format flattens out the dataset into a two-dimensional structure, and so what you wind up having to do is create a tablix structure with different rows for the different data items and a Hidden property for the row that looks at some criteria somewhere to see if we’re looping through the Sales Line table or the Item Ledger Entry table.
That’s one way to do it, but I’ve come up with a different way that I find easier to work with. Instead of creating Sales Line and Item Ledger Entry sub-DataItems underneath the Item DataItem, I’m going to put an Integer DataItem there instead. I’ll go ahead and set up the filters for Item Ledger Entry and Sales Line in the OnPreDataItem trigger for the Integer DataItem, and then I’ll use the [record].COUNT property to figure out if I have any Sales Line and/or Item Ledger Entry records to loop through. I’ll also create some variables for the fields from both tables that I want to display. The finished code would look something like this:
***************************************************************
Integer – OnPreDataItem()
[set filters on Sales Line for item, date, openness, and other things]
[set filters on Item Ledger Entry for item, date, remaining quantity, and other things]
SalesLineCount := SalesLine.COUNT;
ILECount := ItemLedgerEntry.COUNT;
IF RecordCount = 0 THEN CurrReport.BREAK;
SETRANGE(Number,1,RecordCount);
Integer – OnAfterGetRecord()
IF Number = 1 THEN BEGIN
IF SalesLine.FINDSET THEN BEGIN
CurrentSection := ‘Sales Line’;
END ELSE BEGIN
CurrentSection := ‘Item Ledger Entry’;
ItemLedgerEntry.FINDSET;
END;
END ELSE BEGIN
IF SalesLine.NEXT = 0 THEN BEGIN
CurrentSection := ‘Item Ledger Entry’;
IF ItemLedgerEntry.FINDSET THEN;
END;
END;
IF CurrentSection = ‘Sales Line’ THEN BEGIN
// fill display variables from sales line table
END ELSE BEGIN
// fill display variables from item ledger entry table
END;
***************************************************************
Note that this was written as I was attempting to wrestle my darling children to sleep. Note also that my son has been nicknamed “Samuel the Unsleeping,” and as such, I might not have been able to devote my full, undivided attention to the code.
That should be about how it would work, though. Your mileage may vary as to whether or not this is worth it for displaying data from two tables, but I’d definitely try it if I needed to display data from three or more instead of screwing around with IF statements in my Hidden property and trying to have a bunch of different rows to mess with in the main loop of my tablix.
Anyway, happy coding, and leave any comments or suggestions to join the discussion. For more development and coding tips, check out my other developer blogs here.
LATE-BREAKING STREET FIGHTER RELATED UPDATE: I know that the millions of readers of my blog entries actually ignore my technical advice, preferring instead to skip right to the parts where I write about my recent Street Fighter happenings. And with the release of Ultra Street Fighter IV (which could also be called Street Fighter IV, Part 5), I’ve been enjoying the buffs to my main character, Blanka, and messing around with Hugo. I might not ever get good with Hugo, but he’s hilarious to play; he takes up a full third of the screen.