The goal with this post is to show how NOT to use Code.GetData and Code.SetData in the one of the most common used Document report in NAV, our beloved 206 report. Code.GetData and Code.SetData is used in the header to of the report to keep field values in sync with current sales order currently being printed in the body. Just adding the field in the header will not keep this synchronization, unfortunately. Yes we can now add fields directly into the header in RDLC 2008, used in NAV 2013. Actually when I realized that this was possible in RDLC 2008, I was actually crying, because this limitation in RDLC 2005, used NAV 2009, has been a huge pain, at least for me.
But in I this example which I’m about to show you, I will not use this new capability in RDLC 2008.
This blog post is pretty long so I would like to show you the header in Report 206 before and after my changes. Looking at the before and after images should give you an idea what this post is all about.
Here is a screenshot of the header in standard report 206 in just released NAV 2013:
As you can see a lot of <<Expr>> all over and not really human readable, and it gets worse when opening one of these expression, because the values are just Code.GetData and a number.
And here is a screenshot of the header after my changes which I explained in details in this post:
As you can see now all fields are visible and if you need to make modification it easy to locate where your changes need to take place.
So here we are, NAV 2013 have just been released and standard report 206 is still using Code.GetData and Code.SetData, just like it did in NAV 2009. And they way it is designed it needs to use Code.GetData and Code.SetData. I will show you how you simple can change this so the report becomes human readable, because i.e. “Code.GetData(54,1)” in my eyes is not.
For the people not familiar with Code.GetData and Code.SetData in RDLC reports I will do a small introduction now. If you already know how this works you can skip this section and go directly to the “Redesigning report 206.”, where I show you how to get rid of Code.GetData and Code.SetData in report 206.
Understanding Code.GetData and Code.SetData
To understand how Code.GetData and Code.SetData works there are 4 areas we need to look at. As I always I like to visualize things so if we look at the header and the top of the body of report 206 it looks like the below picture:
- Tabel with actual fields. Notice that there is only 1 textbox, in NAV 2009 we had 4 textboxes. The report developer probably thought adding 4 groups did not really simply things, so now we have all 54 fields added to one single textbox. That is a lot fields in one single textbox, good thing it is hidden and only use to get the values from current scope in the report dataset.
- So we have now identified the where all the fields which we need in the header. Now we need to get the fields to the header, so our dear report 206 developer has added a textbox to set data in the header. With this textbox we now store all the fields from the body as one long string, which we can then access from other textboxes in the header. The textbox is hidden and needs to be first thing that activated when the report is rendered. So hold that thought, it has to be hidden and at the top for Code.GetData to work. In NAV 2009 reports are rendered in Report Viewer 2008 this works like a charm, but in Report Viewer 2010 the SSRS team has been so kind to us to change the logic of how a reports is rendered. So if an textbox is hidden in RDLC 2008, the expression in this hidden textbox, is rendered at the end. So as I said the SetData textbox has to be hidden and at the top for Code.GetData to work, but now it is rendered as the last thing, and of course the result is that the fields get out of sync and the very idea using Code.GetData and Code.SetData is broken in NAV 2013 with Report Viewer 2010. Interesting thought!!!
But of course the NAV team came with a workaround so we all could keep Code.GetData and Code.SetData which we all love so much. Solution was to move the expression to the visibility expression, which is then rendered as the first thing when report is rendered, and Code.GetData and Code.SetData now works again. Great, now we can keep Code.GetData and Code.SetData. 
But for how long, before SSRS team finds out that visibility expressions on hidden textboxes in headers are rendered as the first thing and changes this? Well, your guess is just as good as mine. But end result is that you all standard NAV 2013 reports which uses Code.GetData and Code.SetData, now have textboxes in the header which looks empty, because there is no value in them.
To see the actual value you will need right the textbox and select “Text Box properties” and then select “Visibility” and then expression. SSRS team thought long and hard for how to increase the number of clicks, for how to get there. Just like they have removed Value property from the property list and removed the possibility to go directly to expression from Document Outline, just to increase the number clicks we need to go through. How fun is that!!! You would actually think that going from VS 2008 to VS 2010 would increase productivity. OK, I’m getting sidetracked, just don’t understand why they decided to make this worse than VS 2008, when so many things are great in VS 2010 compared to VS 2008. Why then remove the good things which we had in VS 2008?
- OK, back on track. You hopefully now understand that we have 1 fields (4 fields in NAV 2009) added to the body of the report which contains the 54 fields we want to show in the header of the report. We have 1 empty textbox (4 in NAV 2009 non empty) which holds the data we need to show.
So now we need to add textboxes to the header to show the data. To do this the report developer wrote i.e. “=Code.GetData(20,1)” in a textbox in the header. So in this textbox he wants show field in line 20 in group 1. Not sure why the report developer kept the group concept from NAV 2009, because he has now crammed all fields into one textbox on the body, so he could just have written “=Code.GetData(20)”, but maybe he want’s to introduce more groups in a future release? Ok, let’s find field in line 20, in textbox in body so we actually know what “=Code.GetData(20,1)” will evaluate to when executing the report. So let’s start counting 1,2.3,4….20, found it. I feel like being in first grade again when I sit here and count lines :-(. Well maybe just me. When reaching line 20, I realize that “=Code.GetData(20,1)” has this value “Cstr(Fields!PaymentTermsDesc.Value)”, so when I run the report, Payment Terms Description will be shown in this textbox in the header. Notice that all fields now have “Cstr” in front in NAV 2013, this way our report developer is sure that all strings are strings when passed to the header. In line 16, he wants to be really sure, so he is converting stings to strings, “Cstr(Cstr(Fields!PricesInclVATYesNo_SalesInvHdr.Value))” Good job, better be safe than sorry.
- So now you have seen where the actual fields are in the body, where SetData textbox is placed and how the actual fields in headers are created. Hopefully by now you wonder where the Code.GetData and Code.SetData functions are. So lets the find the code. Make sure you click on the body so the “Report” menu is active. BTW another feature the SSRS team came up just to add more clicks to get to things done. So if you i.e. have the cursor active in the property list, the “Report” menu is not shown. No clue why that is, but does give us the opportunity to click some more on our beloved mouse/trackpad.
Ok, hopefully you have now found the “Report” menu. In this select “Report Properties”, and then select “Code”. In the code you will find this code which are the functions used to transfer the values of the fields on the body to the header.
Notice that the SetData function ends with a “Return True“. This is new compared to NAV 2009, and our report developer has added this to make sure the Visibility expression always returns TRUE, so the Code.SetData textbox at the top are newer visible. Now for sure this works in NAV 2013.
Shared Data1 as Object
Shared Data2 as Object
Shared Data3 as Object
Shared Data4 as Object
Public Function GetData(Num as Integer, Group as integer) as Object
if Group = 1 then
Return Cstr(Choose(Num, Split(Cstr(Data1),Chr(177))))
End If
if Group = 2 then
Return Cstr(Choose(Num, Split(Cstr(Data2),Chr(177))))
End If
if Group = 3 then
Return Cstr(Choose(Num, Split(Cstr(Data3),Chr(177))))
End If
if Group = 4 then
Return Cstr(Choose(Num, Split(Cstr(Data4),Chr(177))))
End If
End Function
Public Function SetData(NewData as Object,Group as integer)
If Group = 1 and NewData > "" Then
Data1 = NewData
End If
If Group = 2 and NewData > "" Then
Data2 = NewData
End If
If Group = 3 and NewData > "" Then
Data3 = NewData
End If
If Group = 4 and NewData > "" Then
Data4 = NewData
End If
Return True
End Function
I know this is a very short explanation on how the Code.GetData and Code.SetData are used. And as you can probably read between the lines, I find this approach overly complicated. Being the Report Program Manager in the platform team of NAV for end of NAV 2009 and NAV 2013 until February 2013, you would have thought that I was responsible for this approach. But for the NAV 2013 project the NAV team was split in Platform and Application teams. And the actual design of the reports where the responsibility of the application teams But I could have affected this design strongly when I was inside the gates, but I did not, me bad!! And yes when pointing fingers I’m well aware that 3 fingers are pointing at me.
So as I said in the beginning my goal was to not use Code.GetData and Code.SetData in report 206, and when you think about it, using Code.GetData and Code.SetData is a complete workaround and not something the SSRS Team thinks much about, so there is no guarantee this will work in future version. So I urge the NAV team to work closely with the SSRS to work out a solution for this, so we in a future release can have document reports with out Code.GetData and Code.SetData. Unless of course the NAV team come up with yet another report tool for document reports in the future!
OK enough about Code.GetData and CodeSetData. Now let’s us simplify the report and get rid of all this unnecessary transferring of fields from body to header.
Redesigning report 206
I want to have each field visible in the report, so when another developer opens the report, he is not clueless of what all these <<Expr>> and Code.Get(x,x) means.
So in all it’s simplicity I’m just going to add more header lines to the main table of the report which is in the List control in Body of the report. I know this will work because we already have all the fields stored in the hidden table used for Code.GetData and Code.SetData. This works because the List Control is Grouped by the [No_SalesInvHdr], which is the Sales Invoice Header No.
Then I will move all the 54 fields in header lines to the body and remove them from header.
First thing I do is to measure the size of all the fields in the header. These is around 10cm in height. So to make room to all the fields which is now going to body I need to make room for them by adding 10 cm to each element in the overall List Control. To easily get an overview of which elements that I have in my report, always use “Document Outline”:
Document Outline can be found in “View / Other Windows / Document Outline” or simply by hitting CTRL+ALT+T. In the List control the first thing is always a Rectangle and in my rectangle I can see that I have a textbox(Used for counting pages) 4 tables and another list control. So in “Document Outline” I simply select from the bottom, in this case the “list2” control. With the “list2” control selected I navigate to the Location property in the “Properties” and then increase the “Top” value with 10cm
Before:
After:
I repeat this for all the elements except for the “NewPage” textbox which I would like to be at the top, because I have seen this not working when not in the top of the list. So just be sure.
The I will then add new header lines to the main table in the report, in this case I’m adding 22 new header lines.
Then when the 22 new headers lines have been added I will add new columns and decrease others so I can locate the fields just like they where placed in the header. This is a bit cumbersome, but I use Excel to my calculations, then I’m sure that I leave other columns intact with same values. When you get good at this it only takes a few minutes to add new columns in a existing table.
Now the table headers are ready, so I start going by each of the fields in the header resolve the Code.GetData values to actual fields. This takes a bit of time.
When this is completed I’m pretty much done, but before I can delete the small hidden table with all fields in Body and Code.SetData text box in header, I need to make a small Page caption label because Page number can only live in Headers or Footer of a report. Notice that the label I have created becomes a parameter to the report and is not repeated in each row of the dataset. This is new thing in NAV 2013, but I dig deeper into this subject in a future post.
So with my PageCaption no longer using Code.Get, I can delete the small table which contains all fields in one textbox and delete the Code.SetData textbox in header.
Now I have 3 Images shown in report header and the Page No. The 3 images don’t work, no matter if I select Left, Center or Right in Sales & Receivables setup Company Logo is printed to the left, so this seem not to have been tested in NAV 2013.
But no matter what, I don’t like the idea of having the image repeated in the dataset, just does not make sense, especially from a performance point of view. Here is a small snapshot of the dataset with the logo repeated in standard report 206.
Imagine what a 1 MB large company logo does to your dataset, when printing multiple Invoices with many sales lines!!!
So when designing report 206 I always delete the Images in header, mainly for performance reasons, but also because I have not seen customer yet who required the possibility to change the positions of their Company logo on Document reports. This is something which is fixed, and maybe only change when they change logo.
With the 3 images gone I add a new image, but instead of using from database, I embed the company logo to the report, because then I’m sure that the image is only sent once to Report Viewer. I took the liberty to change the logo to Abakion which is the best NAV partner in Denmark
You can find the modified report 206 from the W1 version here in, txt, fob and a print in PDF from the demo database in NAV 2013.
Thanks, Claus Lundstrøm, Abakion.com
Filed under: NAV 2009, NAV 2013, Reporting | 18 Comments »
