Recently I prepared a blog post about a property RepeatWith available in RDLC reports that has the potential to revolutionize the way that we create document reports in Dynamics NAV 2009. It went like this:
When developing reports in Dynamics NAV you are sometimes required to show information, which is in the Dataset, on the Page Header/Footer. Because Dynamics NAV 2009 is using RDLC 2005, this scenario is not supported and you need to find a workaround.
In many of the standard reports the workaround consists of using the ReportItems collection. Via this collection, you can access all textboxes which reside on the Body of the report, directly from within the Page Header/Footer.
This is a fine solution in many cases. But sometimes, for example in a typical document report (like for example report 206) this workaround does not solve the problem completely. The reason being the fact that the textbox which resides in the Body of the report is not available on all pages of the report (at runtime) and as a consequence, the Page Header/Footer of your report stops showing the database information as from a certain page.
The solution for this is yet another workaround. The idea is to store the database information that needs to be shown in the Page Header/Footer somewhere where it is available for every page. For this you can declare and use a Global variable. But to be able to use it you need some kind of mechanism to store a value in that Global variable and also a mechanism to get a value out, for example a couple of functions named GetData and SetData.
Does it start to ring a bell?
Now I was thinking: Isn’t there an easier solution? What other way would there be to be able to have a value available on every page so we can access it from the Header/footer using the ReportItems collection, without the need for a global variable or other complex expressions?
And the solution was actually very simple, so I thought. When you have a look at a Textbox, it has many properties.
The property of interest is: RepeatWith. If you look it up in the help it says: The name of a data region that this report item should be repeated with if that data region spans multiple pages.
So what if I should put all the report items that currently reside in the body of the report into another report item, for example a List and then put a textbox above (outside) that list and set its RepeatWith property to the name of that list? Yes, you are correct, then the textbox would be visible on all pages were the list is available, meaning all pages because everything is in the list!
Then from the header/footer of the report, the ReportItems collection would have access to the textbox, and this for every page of the report.
How cool is that?
I know what you’re thinking, it’s just a textbox, I can only store one value in it. Well that’s not true. You could store multiple values in that one textbox, using a separator, like for example: chr(177). 🙂
But, heum, then you would need a function to get out the values out of the textbox. And that’s exactly what we are trying to avoid.
Another solution is to use multiple textboxes. That is a good solution, but if you forget to set the RepeatWith property of one of them, then it doesn’t work, and also, we will need to resize all those textboxes and hide them, otherwise they will ‘mess-up’ the layout.
Well, it seems that there’s also a RepeatWith property on a Rectangle, and as you already know, a Rectangle could contain a table, and a table could contain many textboxes.
So why not implement the following steps:
- Select all the Report Items currently in the body of your report
- Click on Ctrl-X (Cut)
- Add a List to your report and give it a good name
- Resize the List so it can contain all the report items you just cut into memory
- Select the list and click Ctrl-V (Paste)
- Add a Rectangle above the list and set its RepeatWith property to the List
- Add a table into the rectangle
- Put the values from the Dataset that you want to show in the Header/Footer into the table.
- Add textboxes in the Header/Footer
- Use the ReportItems collection to get the values out of the textboxes, in the table, in the rectangle.
- Save & Run the report to test it
Well, when you try it using a table whithin the Rectangle, then you will get the following error:
So, either you use multiple textboxes and put them above the list (or below) in a Rectangle directly, or you use 1 textbox with separator(s) and a function able to handle the separator.
In the following screenshot you can see a possible implementation of the RepeatWith property and a List:
(Click on the screenshot to enlarge it)
I can hear you thinking, does that mean I would have to use a List in all of my reports and do the Cut/Paste operation?
Well, no. As you might have already noticed, this workaround is usually only required for document reports, and it happens to be the case that in many (most, not all) document reports available in the (localized) Dynamics NAV demo database, everything is already inside a List container.
Of course, the other workaround using a Global variable and Set/Get functions is still a good solution, maybe even a better one, but why make it that complex?
A disadvantage of using the Set/Get functions solution, is that when you look at the textboxes in the Header/Footer you don’t directly see what data will be displayed in them, all you see is something like: GetData(X,Y). And when you want to find out what is X and what is Y, you need to open the Report properties, look at the GetData function to see what is does with it’s arguments, because it might differ from one report to another. Then search in the report for the textbox that executes the SetData and see in which textbox the ReportItems is linking to and then start to count to find the correct field, because it’s usually separated using the famous chr(177) character:
When you do it as in the example above using the RepeatWith property then you can still see exactly by just looking at the textboxes, what is in there, for example: ReportItems!Fieldname.Value, instead of GetData(X,Y).
And when you have to do bug fixing or implement change requests in complex reports that couldbe a real time saver!
I implemented the RepeatWith example, using a Rectangle, in the report in this blog and you can download it as a .fob or .txt file here.
But as any good report developer, or any other kind of developer, should always do, it’s now time to test our report.
When we run the report it works very nice:
And this is what you see in Print Layout:
How frustrating is that?
When you export to Pdf you get the same result as in Print Layout. So basicly it means that it doesn’t work.
The RepeatWith property is broken !
Ofcourse, as soon as I found out I did a little internet search and I discovered these pages:
So it seems like this is by design 🙂 🙂 🙂
Now, personally I don’t understand this. Why bother to create a RepeatWith property and then not support it when printing or exporting? Is it to confuse the developers? Did someone at the SSRS team say, hey let’s make a joke and implement some properties that do not really work? (because, this is not the only example of properties that don’t really work)
Of course, in RDLC 2008 this is not a problem anymore, because then DataSet fields are allowed in the Page Header/Footer, but still, why bother to implement a RepeatWith property when is is clearly broken?