Transfooter / Transheader working with groups – Part 2

The solution I explain here can only be done with NAV 2013 R2. The reason for this is that we are using a new feature in Visual Studio 2012 where it is possible to reset the page number when a group is done. So if you are not using NAV 2013 R2, which supports Visual Studio 2012, read this blog post: Transfooter / Transheader working with groups – Part 1. You could also do Page breaks in the solution described in Part 1, but the solution I explain here is much simpler. If your report has no page breaks per group, no new features in NAV 2013 R2 can help you and you are back at solution described in Part 1: Transfooter / Transheader working with groups – Part 1

So if you are still here you report has page breaks between each group and you have NAV 2013 R2.

I suggest you first download my solution here, so you can play with solution while I explain.

For this scenario we only need this code added to the Report Properties:

Private running As Double = 0

Public Function SetTotal(ByVal val As Double) As Double
    running = val
    Return val
End Function

Public Function GetTotal() As Double
    Return running
End Function

As in part 1, we will use the SetTotal to set the total in the Page Footer and GetTotal to get the total from previous page.

And in this scenario you only need 1 column added to your main tablix


We need this column since we need the RunningValue of the Amount_SalesLine in the details so we always know the accumilated value of Amount_SalesLine in the last row on the current page. The expression need to be placed in the details row, for this to work, so not in the Group Header and not in the Group Footer. For demo purpose I have made the Column big so you can see it, but you of course would make this as small as possible and hide the value so this is not visible on your report.

In the RunningValue details textbox add this value:


With this we always know the accumulated value of the Amount_SalesLine, so we are ready to show this in the PageFooter and PageHeader on next page. If your value is different than Amount_SalesLine you of course modify this value.

Now click on “Group2” in the Grouping Window at the bottom in Visual Studio, again your group might be called different.


Now navigate to the Properties page in Visual Studio, make sure that it says “Tablix Member” right under the name Properties:

Now expand “Group” and  “PageBreak” and set the values




With this small exercise, the report will now create a page break every time a new customer is shown in our group, and it will even reset the page number so each customer is shown in its own page range. Yes this means that we can now truly have Page x of y in all our outgoing documents, when our customers are running NAV 2013 R2 of course.

Ok, our work in the body is now complete.

Let’s start on the Page Footer. In this I have only 1 textbox, not 2 as I had in solution explain in Part 1.

In this I have this value


What this will do, is that it will both show and set the RunningValue of the last visible detail line on current page. It looks at the Detail textbox in the RunningValue column with this value: =RunningValue(Fields!Amount_SalesLine.Value,Sum,"Group2")

In the Transfooter we set the visibilty expression to:
=Globals!PageNumber = Globals!TotalPages

So when PageNumber=TotalPages we know we are on the last page of the group and should not show the Transfooter textbox. Notice that there is a difference on OverallPageNumber and PageNumber. OverallPageNumber is for all pages in the report, while PageNumber is only for the group when you Reset the PageNumber and break between the groups.

Now our work in Page Footer is complete, so lets go to the Page Header, where we want to show our Transheader. Here I have added a textbox which is light blue.
This textbox is quite simple, since the value is just:


and Visibility expression is just:

=Globals!PageNumber = 1

So I just show the value with GetTotal and make sure that I only show the Transheader when on all page except page number 1 of each group.

This solution will show Transfooter and Transheader eventhough the Group footer is alone on last page of the group

See i.e page 15 and 16 in my report:


If you can think of any Transfooter / Transheader scenario that I have not covered in the my 3 scenarios, please let me know in the comments:

Scenario 1. Simple list report with no groups. I explained how to do this at the NAV team blog here.

Scenario 2. Report with groups and with page breaks in between each group. This blog post.

Scenario 3. Report with groups and with NO page breaks in between each group. I explained this here.

BTW, notice that I also have Copy working perfect in above report. I will not explain here how I did this, but you are of course welcome to explore my report and see how I did it.

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV &


What is new in Dynamics NAV 2013 R2 for Reporting

Biggest News, support for RDLC 2010 and Visual Studio 2012

1. ResetPageNumber on groups. With this we can now finally do page x of z when i.e. printing multiple invoices, something which was impossible to do in NAV 2009 and NAV 2013. With ResetPageNumber on groups we can also do Transfooter and Transheader much more elegant. If you attended my presentations at Directions EMEA in Vienna you saw how I did this, and I promise to blog about how this in done in R2 soon.

2. Export to XLSX and DOCX and not the old XLS and DOC format introduced around 16 years ago in Office 97.

3. Possible to make expression which can run only when render is interactive, i.e. Report Preview and not show if not interactive i.e. Print Layout, PDF and Excel. This could be very useful if you want something shown only in Report Viewer and not PDF or vice versa.

4.Create expressions that calculate an aggregate of an aggregate For example, in a cell in the row group header that is associated with a group based on year, you can calculate the average monthly sales for year by using the expression =Avg(Sum(Fields!Sales.Value,”Month”),”Year”). This is cool, but remember that you should not pollute the dataset, so if this only value you need, you should do this calculation in NAV.

5. Support for Report Builder, yes now we have a free editor for RDLC reports, so we do not have to do workarounds to get Visual Studio Express to work with reports in NAV. Report Builder has a few limitations, i.e. we do not have support for Document Outline and my best friend in Visual Studio the Layout Toolbar is very basic in Report Builder. So if you are experienced RDLC report designer you will probably prefer to work in Visual Studio 2012 Pro. If you on the other hand need to make a small change in the customers environment, Report Builder is the perfect solution.

6. Support for Sparklines, Databars, Indicators and Maps, all great demo features so expect in the future to find demo reports using these capabilities on my SkyDrive

7. Here is one of my favorites. Support for opening reports in either Print Preview and Print Layout. In Print Layout we can see how the reports will look when printed. This is especially useful for document reports which usually are in Preview mode. And when in Preview the PrintLayout button is disabled, leaving the user unable to see how this report looks on paper. Also it makes no sense to show document reports in an Interactive mode when they have no interactive features. Opening reports is also faster in Print Layout, so if you are running large reports, try to run them in PrintLayout mode, and see how much faster they are. This is configured on each individual report.

8. Roollback to Security model in Report Viewer 2008. I have unfortunately not had the change to test this, but it should make large reports run much more smoothly compared to NAV 2013. The  <NetFx40_LegacySecurityPolicy>  setting in the Microsoft.Dynamics.Nav.Client.exe.config in the RTC client folder is default configured to use the old securtity model. You can read more about this here:

9. Support for writing vertically textboxes in a 270 angle. In VS 2008 and 2010 we only had support for vertical textboxes in a 90 angle. Not the most exciting feature, but maybe useful for you.

10. Orphan static rows are now truly deleted when you decide to delete a group, and the group row are also moved to correct indention level. In Visual 2010 this had driven me crazy many times, deleting a group was not possible, since it meant that I had to recreate the tablix from scratch since there was no way of deleting these orphan static rows from the UI. Microsoft thanks for fixing this in VS 2012, but why have this not been fixed in VS 2010???

11. UserID converted to NAV UserID. When Printing reports from the WebClient or NAV Server it will be the WebServer UserID or NAV Server UserID printed on the report. Since pretty much all standard reports are using the “User!UserID” built in UserID from Visual Studio, Microsoft decided to automatically to convert this to the NAV UserID. It’s not even shown in the dataset, so does not polute each line, as it would have done if we used the User as column in the dataset. Going forward best practice will be to use the built in  “User!UserID” in Visual Studio.

12. Printer Selection is now respected when in Print Preview. in NAV 2013 Printer Selections was only supported when printing from Request Page.

13. And then of course all the small new additions in Visual Studio 2012 that I have not discovered yet. 😉

With NAV 2013 R2 the RDLC reports now feels much more solid, so next thing is to get the NAV standard reports to look great and all outgoing document reports to look the same, so it easy for us partners to make changes to these, without needing to reinvent the wheel each time. Overall you should really look forward to do RDLC reports in NAV 2013 R2.

/Thanks, Claus Lundstrøm, &

Dynamic Headers and Footers in RDLC reports

It’s time to vote, if you want Dynamic Headers and Footers in RDLC reports.

Today it works perfect when view our reports in Print Preview:

See example here in Print Preview:


But as soon as I view my report in Print Layout it looks like this:


The Page Header and Page Footers background is red, but should never be shown since I have controls all the way to the edges of the Page Header and Page Footer.

Eventhough the SSRS team have gotten feedback many times that we want this fix, I now have trust in we can get this issue fix if we are many people voting to get this fixed. You can vote here:

If the link does not work for you need to registre here first: and then select NAV on the list.

When you have registred you can click the link to go directly to the issue and then please vote íf you think RDLC reports should support Dynamic Headers and Footers

When registred you can of course also give any other feedback to the NAV product team.

My Report in Visual Studio looks like this:


and if you want to play with the report object you need can find it here at my SkyDrive:

It requires Visual Studio 2012 to work. So if you are looking for my VS 2012 OfflineReport project this is it. 🙂

/Thanks, Claus Lundstrøm, &

Free Visual Studio Designer for editing Reports in Dynamics NAV 2013 – Part 2

You might remember that I blogged about how to design RDLC reports in NAV 2013 using Microsoft Visual Studío Express Web Developer Edition: http://localhost/wordpress/2012/05/31/free-visual-studio-designer-for-editing-reports-in-dynamics-nav-2013-and-other-options/

Now it seems that Microsoft have removed the “Microsoft Visual Studio 2010 Shell (Integrated) Redistributable Package” which before was found here:

I have searched high and low, and I cannot find this file anywhere at I have even degraded my self to use gOogle search engine, but as always, if I cannot find it on I cannot find it on gOogle either.

Lucky I have a backup of the files we need to be able to design RDLC reports in Visual Studio 2010 for free.

So navigate to my Skydrive and download the files here:


In Step 1 you will find the “Visual Studio 2010 Express All-in-One ISO” including SP1

If you have not updated to Windows 8 where we can open ISO files directly, I have also extracted the ISO files for you. You of course don’t need the extrated folder if you can use the ISO file. If downloading from my SkyDrive is to slow you can also download “Visual Studio 2010 Express All-in-One ISO” here:

Downloading “Visual Studio 2010 Express All-in-One ISO” from of course only works until Microsoft decide to remoe the Express version as well.

In Step 2 you will find the “Microsoft Visual Studio 2010 Shell (Integrated) Redistributable Package” which I cannot anywhere at

Remember that when using the free editor, OfflineReport is not supported.

In NAV 2013 R2 we do not have to go through all these tricks, but much more about this in next blog post.

/Thanks, Claus Lundstrøm, &

How to fit address fields correctly in a Windows Envelope

Summer is over and Directions EMEA is only 9 days away. I have a Reporting Tips and Tricks session at Directions so if you are there I expect you to join my session :-).

This year Abakion will also sponsor Directions EMEA. We have never done that in past so this is real exiting. We will show case our Supply Chain Box product, so be sure to stop by our booth. You can also read much about Supply Chain Box here:


OK, enough marketing lets get down to business, and let’s face it, you are reading this blog post because you want Reporting tips and tricks :-). So how do we actually consitently place the address fields, so they fit perfectly in the small windows of a Windows Envelope. By now many of you have worked on Document reports and have struggeled with this. In the begining I felt like I was designing my reports in water, because everytime I placed the address fields the moved around, until I understood how our lovely Visual Studio Report designer was working.

To find the right location we need to consider the following things:

  1. Code.SetData textboxes
  2. Creating a retangle around the address fields
  3. Top Margin
  4. Left Margin
  5. A report which have the address fields place correctly

Code.SetData textboxes

First you might wonder why we need to look at the Code.SetData textbox. Well the reason is that this or these text boxes will influence the location of the Address fields. Let me show you.

Here I have sample document report with the address fields perfectly fitted for the Windows Envelope





I have emmbedde a background image, which shows me exactly where the window on the Windows Envelope will be. Notice that the backround image of the Report will not show in the Margins. No something I will go into now, but now you know if you did not already of course.

Now let me add a Code.SetData text which we have on all our document reports. For demostration purpose I have increased the size of the Code.SetData textbox and marked it red. Now let see what happens when I print the report:





As you see “funny” things start happening, and it looks like the Code.SetData textbox completely deletes the section where it is placed and then pulls the address fields up the report, exactly of height of the Code.SetData textbox. In some sceanrios that could actually be handy, by here it is just a pain.

So how do we fix this. Well in the first place it is broken that the value has to go to the Visibility expression. Hidden expression in Report headers are rendered as the last thing in  Report Viewer 2010(NAV 2013) so tricking the Report Viewer to render the expression as the first thing by moving it to the Visibilty expression is just wrong. And we see above that this is not something the Visual Studio ever tested or they newer thought we we would work with it like this.To fix this we copy the Value from the "Show or Hide based on a expression" and set Visibilty to "Show". Paste the value into normal expression on the General tab, and then set the font to white in the Font tab. This way the the textbox will be visible, but "TRUE" value will be hidden because font is the same color as the paper. For demo purpose I keep the Code.SetData textbox red so you can see that I have a value in textbox. You of course mark the textbox white.





First problem solved

Creating a retangle around the address fields

Usually customers don’t like the address fields of font size 8pt which the UX-Guidelines tells us to do. 8pt is just to small for Postman Pat to read. So we increase the font to 10pt. Now the we increase the size of the Address Textboxes to 13pt. 12pt is no good, because it will truncate the bottom of the letters “g”. “j” and “y”. With this excersize you now have textboxes on left side of the report of size 13pt and on the right side of the size 10pt or hopefully 11pt, because 10pt will again truncate the bottom of the letters “g”. “j” and “y”. When you do this Visual Studio has an easter egg built in, so it moves the textboxes on left and right side around and when printed they are not placed in the right places. This is just a pain and to resolved this issue we place a rectangle around the address fields on the left side. Sometimes this does not completely fix thes issue, if not, you place a rectangle around the textboxes on the right side as well.

Second problem solved


Top Margin and Left Margin

Now finnally we can get down to placing the address fields at the correct position in our report. So for us to place the address fields correctly we need to note down the values in the in the Top Margin and Left Margin in Report properties. In my example I’m using the lovely margins in report 116 – Statement in the Danish version:

Top margin = 1,05834cm

Left Margin = 1,76388cm

These values is all I need from this report.

A report which have the address fields place correctly

Now I need the values from a report which I have verified prints the address fields correctly. And the I place all these values in this spreadsheet:


You could of course argue that I do not need this spreadsheet, because I could just set the Margins to the same size in the report that I’m fixing, but I’m considerng that as major changes and could easily give me extra work fixing the whole report to fit correctly to the new margins.

After you have inserted the correct 4 values on left side and inserted Left and Top Margins on the right side for the report you are fixing, you now have the values for the Location properties Left and Top. So go back to Visual Studio and paste these values in for the Rectangle you created around the Address field in above step 2. Notice that the Textboxes does not follow along with the rectangle when you do this. This is another easter egg in Visual Studio. To fix this move the top Address textbox to the top left corner of the Rectangle, do not use the mouse for this excersize. And then left align below Address textboxes to the this textbox and then remove vertical spaces. Tip! Using the buttons in Layout toolbar you can do this in under 5 seconds, so if you are spending more time on this aligment, you are doing it wring. The Layout Toolbar is my best friend in Visual Studio.

The above spreadsheet I am using can be found here along with the background image for C5 Window Envelopes. This background image only works if the margin is to Left=2cm and Top=1cm, so use the spreadsheet to get the correct values for your report.

/Thanks, Claus Lundstrøm, &

Transfooter and Transheader warning

Last week I did another RDCL Report Training class, this time in UK. In this class we stumbled over an issue, I had never seen before, while we where implementing Transfooter and Transheader functionality in a report. You might never encounter this issue, but to save you the headache I got solving this issue, here is the solution or bug you might call it.

You might remember the Transfooter and Transheader solution I blogged about a few years ago here: This solution also works perfectly in NAV 2013 and Visual Studio 2010. I have upgraded the report to NAV 2013 and made some layout changes to it. You can download the new version here from my SkyDrive:

But back to the problem. While we were adding Transfooter and Transheader functionality to the report, we also played around with the LeftIndent property on the textbox that feeds information to Transfooter Text box in the Page Footer. In the above demo report this would be Text Box “Customer__Debit_Amount_”.
When i.e. setting the LeftIndent property to 5pt to this specific Text box, we suddenly got and error in the Transfooter Text Box:


Why that is, I have no clue, but I have stopped using LeftIndent, since I no longer trust that property.

If you have an idea why the Transfooter Text box gives an error when using LeftIndent, I would love to hear. If not I will just park this as yet another bug in Visual Studio.

/Thanks, Claus Lundstrøm, &

Fixing Inventory Valuation report

When running Inventory Valuation report you might have experience that the data in this report is not visible in Print Preview like this:


Inventory Valuation is not the only standard report with this problem. I.e. you will also see this problem in the Account Schedule in the NA(North America) version.

Easy workaround is of course to click the “Print Layout” button, if this is visible of course, and the report looks like this:


But in this blog post I want to fix the report so the report also shows all the data in “Print Preview” and not just “Print Layout”

The fix is easy, and it is clear that this report was not completely fixed after the rules in UX Guidelines. In the UX Guidelines it is outlined that cell padding should be:

Left: 5pt
Right: 5pt
Top: 0pt
Bottom: 0pt

but the cell padding is the default values in this report:

Left: 2pt
Right: 2pt
Top: 2pt
Bottom: 2pt

I don’t want to mess with the report to much so the only thing I will change is the Top and Bottom padding in this report, and which is not needed in this report. 
So I will change both Top and Bottom padding to 0pt.

You have 2 options for making these changes:

Option 1. Export the report as txt and search and replace the following:

Replace <PaddingTop>2pt</PaddingTop> with <PaddingTop>0pt</PaddingTop>


Replace <PaddingBottom>2pt</PaddingBottom> with <PaddingBottom>0pt</PaddingBottom>

and then of course import the txt file again.

Option 2. Make the changes in Visual Studio by right clicking on the Report.rdlc in the Solution Explorer and select Open With / XML (Text) Editor, and do the same search and replace as above.

Save, compile and run you’re the report, and the report now looks like this in Print Preview with all data shown:


It would of course be nice if the NAV team would fix this. It only takes 2 minutes to fix Smile

Or request the SSRS team to fix the Report Viewer so it shows the data just like in Print Layout. This would of course not be a 2 min. fix, and this issue would not be on my top priority list for things the SSRS team should fix, If had the luxury of prioritizing what they should fix.

I dedicate this blog post to Kamil Sacek

/Thanks, Claus Lundstrøm, &

Attach a Report as PDF in e-mail

It has been a while since I blogged about how to do attach a report as PDF in e-mail here:



The files found at the NAV Team blog is a bit old now, and I have made a newer version which does not required you to hard code the computer name.

The new NAV 2009 version is found here on my SkyDrive:

and then I just upgraded this for NAV 2013 as well: 

Feel free to copy the code.

You might also note that I have a lot of other examples at my Public folder at SkyDrive. Feel free to explore and steal any examples I have placed there, direct link to My Public folder is Smile

When I started adding examples in my Public Folder, only NAV 2009 existed, so I did not think of creating a NAV 2009 folder. So all examples in the root is for NAV 2009. Now I don’t want to move them since I will brake any links to these files.
I will make you guess what report examples can be found in the NAV 2013 folder Smile

Again feel free to copy any of my examples, if you find them usefull of course.

/Thanks, Claus Lundstrøm, &

How to Shut down and Restart Windows 8 using only the keyboard

Update June 26 2013: In Windows 8.1 just hit WINDOWS+X+U+U to Shut down your computer. Thanks Microsoft for making this easier in Windows 8.1


I know this tip is not Dynamics NAV related but ever since I upgraded to Windows 8, the only thing that has really been a pain for me, is that I could not Shut Down the computer without using the mouse. Finally I found the solution, so here it is:

Press WINDOWS+M and then press ATL+F4

You will then get this option:


This works both from within Metro UI and normal Desktop.

Finally I’m happy with Windows 8 and it has been a puzzle to me why the Power Button is so well hidden in Windows 8, but just like RDLC reports things are easy to do when you know which buttons to press. Maybe the engineers building Report Designer in Visual Studio are the designers behind the Power Button in Windows 8 as well. I would not be surprised if that was the case Smile

BTW, If you are using Remote Desktop to Windows Server 2012, the same above options work and you get this this option:


Here you even have the option to uncheck “Planned” if you did not plan to close down the computer SmileAnd you need to write a comment why you are shutting down the computer before the “OK” button is available. We have support for 511 characters in this textbox, so don’t be shy explaining why you want to shut down the computer.

/Thanks, Claus Lundstrøm, &

Working with images in RDLC reports

Working with images in RDLC reports and Visual Studio 2010 in NAV 2013 are unfortunately not done without doing some workarounds.

In this blog I will address 2 issues with images in RDLC reports:

  1. Images with transparent background will not show correctly when opened in PDF.
  2. Images will show unexpected lines at the edges of the image when printed.

Let us start with Transparent background issue:

Several times I have received company logos which transparent background in PNG format. The logos look great in the RDLC report when printed, and viewed in either Print layout or Print Preview, but when saved to PDF the image looks compressed when opening report in Acrobat Reader, i.e. version 11. Funny enough this is only when viewing the PDF file. When printing to paper from Acrobat Reader the image looks great.
But usually when printing to PDF we don’t plan on doing printing to paper, so we of course want the image to look great in Acrobat Reader as well. Here is a screen dump from Acrobat Reader of an image in PNG format with transparent background and an image in JPG format with no transparent background. Maximize the picture here, then it easy for you to see the difference.


Here is the workaround for getting the image to look nice in Acrobat Reader:

1. Open image with transparent background in Paint

2. Save the image in JPG format.

3. Accept that transparent background will be removed


4. Import image into report again and print to PDF. Issue is then solved. This of course does not work if you are required to have the transparent background in your report.

Note that PDF viewer in Windows 8 does not have this issue, so this seems like a Acrobat Reader issue.


Next issue; Unexpected lines showing at the edges of the image when viewing the report in Print Layout and when printing from either Print Preview or Print layout in Report Viewer.
When viewing the report in Print Preview and when printing to PDF this issue is not seen. But if you print to directly from Report Viewer images will have extra lines at the edges of the image:

Here is a screen dump from Report Viewer in Print Layout mode, and how it will look at paper when printed. It seems like start of the A in Abakion is printed in the end of the picture and the top of the head of the guy in the logo is printed under his right foot. This is clearly a bug in Report Viewer 2010. This bug is not solved in Report Viewer 2012, so I guess it is time to request a hotfix for Report Viewer 2010 and Report Viewer 2012.


Here is the workaround for getting the image to look nice in Report Viewer:

1. We need to increase the canvas size of the image. Unfortunately no standard image tools can do this in Windows. But lucky one of the developers of Paint in Windows left Microsoft long time ago and created Paint.NET a great Image tool for us, and best thing is that it is free. Paint.NET can be downloaded here:

2. Open Image in Paint.NET.

3. Select “Image / Canvas Size”


4. Increase Width and Height with 10 pixels

5. Make sure that Anchor is set to Middle. We want to have middle here because we want make a border outside the image of 10 pixels so edges of the real picture is not repeated on other edges of the image.

6. Save image and Import image into report again.

7. View report in Print Layout in Report Viewer and try to print report to real paper. As you can see issue is now solved.


I hope this post was useful for you.

/Thanks, Claus Lundstrøm, &