NAV 2009 RDLC Reporting: Working with multiple datasets

[Blog bot]

Damn .. It's so tempting to start writing about NAV2013 (and probably Microsoft would want us to do so...), but I'm not gonna .. (yet). Because quite some time ago, I promised in this post to go a little bit deeper in "working with multiple datasets" on Dynamics NAV RDLC Reports.

Again, as explained here, this solution would be far too complex to see it as a solution for the OutOfMemoryException stuff .. but still .. here are some words about the "multiple datasets" approach.. . On sidenote .. In this blogpost, Steven is talking about - kind of - the same topic.. .

Let's first get something straight: when I say "multiple datasets", I don't actually mean "multiple datasets" like you would be able to select a dataset like it's supposed to .. that's not possible in NAV RDLC Reporting (as far as I know). I actually just mean that there might be "holes" in the dataset, caused by using multiple dataitems, and putting them on sections a certain way. These holes can be considered as being multiple datasets (at least I do..).

You all know how you can look at your dataset when you print a report. You just click "About This Report" twice (first time is for telling the RTC that you want to watch the dataset, and the second time (when you ran the report again), it's for actually looking at the dataset):



What you would expect, is something like this:

Header veld 1

Header veld 2

Header veld 3

Header veld 4

Header veld 5

Line veld 1

Line veld 2

Line veld 3

Line veld 4

Line veld 5

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

All values in all fields.. . Creating a report this way is kind of like creating pivots on tables in Excel. I always compare it like that..

But what you sometimes get, is this:

Header field 1

Header field 2

Header field 3

Header field 4

Header field 5

Line field 1

Line field 2

Line field 3

Line field 4

Line field 5

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

<>

<>

<>

<>

<>

Value

Value

Value

Value

Value

Empty spaces.

Sometimes it's a pain (certainly, when you're not aware of this, expecting a decent dataset). For example, using expressions like FIRST(Fields!Line_Field_1.Value) won't work, because in the first line, there is no value.. .

And sometimes it's a gain .. because you actually only want that value in only the first line, like we would like to have in the reports where we use pictures. Or in reports where we would like to use multiple datasets (e.g. A report where sales and purchase graphs are printed ...)

If you take a look for example at the dataset of the world famous "Sales Dashboard" report, you'll see that kind of dataset.



And it's quite obvious what it does that here, because you have 3 dataitems, all on the same level, which is going to produce each its own "block" of data.. .

This has to be managed

I mean .. you have to take this into account when working with slightly more complicated datasets. As I said .. It can work against you .. But it can work for you as well!

When you're working with default indentation and linking stuff (even in code), all goes fine. For example, dataitems set up like this:



But when you're doing something special, like adding an integer item like this (in fact: adding dataitems on same level as others..):



Then you could be having a dataset like this:



How can I manage it?

Well, putting filters on your tables will bring you far already. When you look at the default Invoice report, for example, you'll see that - about - every table has a filter, like here:



It's just a matter of testing which field has values by the filter

Fields!MyField.value > ""

So, how to solve the OutOfMemoryException problem knowing this?

Remember, I started this blog, because one of the solutions I had in mind (but hadn't really implemented yet) was to actually use this "flaw" to not have a dataset that gets 1,2 Gb just because of the pictures I'm sending. I'm printing one picture, so I only want this in my dataset just once. In fact, the default report gives a dataset like this:



As you can see, it seems e very decent report. All fields perfectly filled. But now, I don't really want that. The picture is in my dataset about 12 times. I only want to have this once in my report, like:



And as you see, I seemed to have succeeded in doing so. And guess what .. my report runs A LOT faster as well :-). Obviously, because I'm sending A LOT less data to the RTC client.

So, how did I do this?

I started out by creating a new "dataset" (I'm still calling it like that :-)), by adding an Integer element, which is filtered to only run once!



Furthermore, I moved the default picture fields to that section:



Next, I had to do some changes to the RDLC-layout. As mentioned above .. I had to "manage" the fact that I had two sets of data .. And I didn't want the first one to influence the second one.

As you probably know, there is a list surrounding the body-section, which is actually going to "group" the report, and making sure that every invoice is going to get its own page.. (at least, that's why I think it's there). I made my first modification there: adding a filter to ignore my first line:



Next, I had to make sure that the table, that manages the set/getPicture-stuff that we love so much. Hidden "fields" to transfer data (in this case: pictures) to the header. I moved it out of the list:



This is probably causing some lost space between header and details of the report .. . I didn't go into that .. just warning you at this point :-).

To conclude, I made sure that this small table was decently filtered as well (only on my first line) by using the reversed filter that I had on my list:



And that's it, guys. It seemed to work for me..

Seemed?

Indeed. I just did this on a Thursday evening, while watching a movie (The Surrogates, if one might wonder ;-) - not my favorite, by the way ..), so this is not used in a production system, this is not tested too thoroughly .. It was just my intention to make my point about working with these "weird looking" datasets.

Hope it made somewhat sense. If not, sorry to waste your time :-).

Is this the only way to solve the OutOfMemoryException?

No. I already blogged a solution that I think is much better here.

  

But hey .. Now I hope at least that "working with datasets" is something you're more familiar with .. :-).



Читать дальше