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

image

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:

=RunningValue(Fields!Amount_SalesLine.Value,Sum,"Group2")

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.

image

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

“BreakLocation=Between”

“ResetPageNumber=True”

image

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

Code.SetTotal(ReportItems!RunningValue.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:

=Code.GetTotal

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:

image

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
Abakion.com & Supplychainbox.com

Transfooter / Transheader working with groups – Part 1

You might remember I blogged this solution on the NAV Team blog:
http://blogs.msdn.com/b/nav/archive/2011/06/06/transfooter-and-transheader-functionality-in-rdlc-ssrs-reports-revisited.aspx

This solution unfortunately does not support Reports with several groups.
As I see it we have these 3 scenarios when working with Transfooter and Transheader, so carry over the value in the Page, so it visible in the bottom of the page what the last value are and being transferred to the next page header where it is visible what the last value was on the previous page.

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. For us to solve this scenario 100% we need VS 2012 which is supported in NAV 2013 R2. I have explained how to do this here: “Transfooter / Transheader working with groups – Part 2”

Scenario 3. Report with groups and with NO page breaks in between each group.
This is the scenario I will explain in this Blog Post. The solution I’m showing will work in both NAV 2009, NAV 2013 and NAV 2013 R2, but I will do this in NAV 2013 R2. I suggest you download my solution here so you can play with it while I explain. My report is a simplyfied version of the standard report 108. So it shows each the customers in my database where Sales Lines exist.

I want to thank Marko Divnic for pushing me to find a solution for this problem when having groups in your report and want to implement Transfooter and Transheader, just like we had in Classic report in the old days.

Let’s begin!

First copy this code into 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

Private Header As Boolean = 0

Public Function SetShowHeader(ByVal val As Boolean) As Boolean
Header = val
Return val
End Function

Public Function GetShowHeader() As Boolean
Return Header
End Function

We will use the SetTotal to set the total in the Page Footer and GetTotal to get the total from previous page. And we will use SetShowHeader and GetShowHeader to control if the PageHeader should be shown.

Now you need to add 2 columns to your main tablix

image

We need these columns since we need the RunningValue of the Amount_SalesLine in the details and we need to know when we have reached the last row. Both the following expressions 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 these Columns big so you can see them, but you of course would make these as small as possible and hide the values so they are not visible on the report.

In the RunningValue details textbox add this value:

=RunningValue(Fields!Amount_SalesLine.Value,Sum,”Group2″)

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 you value is different than Amount_SalesLine you of course modify this value.

In the Group details textbox add this value:

=RowNumber(“Group2”) = CountRows(“Group2”)

With this we always know when we have reached the last line in the Group.
If your Group is called different that Group2, you will of course need to modify this value.

Our work in the body is now complete.

Let’s start on the Page Footer. In this I have 2 textboxes a small one in the top left corner of the Page Footer and the Main TextBox which shows my Transfooter. It is light green, if you are in doubt 😉

image

In the small TextBox I have the following value:

=Code.SetShowHeader(ReportItems!GroupDone.Value)

What this will do is that it looks at the GroupDone textbox, the Group column we just added. Remember that we had the expression =RowNumber(“Group2”) = CountRows(“Group2”) so when we have reach the last row in the group we have the value TRUE. Now I know when to show the Transheader on next Page, because I will just look at the Boolean value in the visibility expression of the textbox.

In the large light green Transfooter textbox I add this value:

Code.SetTotal(ReportItems!RunningValue.Value)

What this will do, is that it will both show and set the RunningValue of the last visible detail line. 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:
=ReportItems!GroupDone.Value OR iif(Globals!OverallPageNumber = Globals!OverallTotalPages, TRUE, FALSE)

Again we use the ReportItems!GroupDone, so we only show transheader when we have not reached the last line in our Group. I also check for last page just to be sure that it is not shown on last page. If you have not upgraded to NAV 2013R2 OverallPageNumber and OverallTotalPages will be new. If you implement this solution in NAV 2009 or NAV 2013 you need to use PageNumber and TotalPages instead.

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:

=Code.GetTotal()

and Visibility expression is just:

=iif(Globals!OverallPageNumber > 1, Code.GetShowHeader(),TRUE)

So I just show the value with GetTotal and make sure that I only show the Transheader when last row on previus page had not been reached.

This solution works almost 100% but notice on page 23 and 24 of my report  that neither transfooter nor transheader is shown.

image

To work around this, you need to change any of these value until your report does not leave a Group footer alone on next page:

  • Height of Top or Bottom margins
  • Height of Page Header or Page Footer
  • Height in the textboxes in the Tablix

I could of course have fixed this in my report, but I wanted to show you this small limitation

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

Orphan static rows in Visual Studio 2010

After my previous blog about all new Report features in NAV 2013 R2, Natalie Karolak, MVP for Dynamics NAV encourage me to blog a little more about orphan static rows in Visual Studio 2010. If your German is perfect you can just read Natalie’s blog post about this issue here: http://www.msdynamics.de/viewtopic.php?p=92761#p92761. For those of you who do not speak German read on.

If you create several groups in Visual Studio 2010 and activate the Advanced button you see all the static row in your tablix. So in this example I have done 2 groups which looks like this:

image8

Noticed that if I now change my mind and decide to delete Group1 and decide only to delete the group

image9

My report now looks like this:

image10

Notice that I now have a Static row in my Row groups which does not exist in the Tablix. Also notice that the details and the Static rows around this are not indented.
Having this orphan static row, will only create problems for you so want to fix this, but this not possible from the UI. So you have several options:

Option 1: Do not delete the group 1, before upgrading to NAV 2013 R2 and Visual Studio 2012, where this issue has been fixed.

Option 2: Create Tablix from scratch.

Option 3: Fix this in the XML editor in Visual Studio or Notepad.
You need to look for this in the editor and delete these rows:

image11

Now your report looks like this and we have now completely deleted Group1:

image12

/Thanks, Claus Lundstrøm

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: http://msdn.microsoft.com/en-us/library/dd409253.aspx

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, Abakion.com & Supplychainbox.com

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:

imageimageimage

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

imageimageimage

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:

https://connect.microsoft.com/dynamicssuggestions/feedback/details/723730

If the link does not work for you need to registre here first:

https://connect.microsoft.com/dynamicssuggestions 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:

image

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

http://sdrv.ms/ZDdFtn

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, Abakion.com & Supplychainbox.com

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: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=115

I have searched high and low, and I cannot find this file anywhere at Microsoft.com. I have even degraded my self to use gOogle search engine, but as always, if I cannot find it on Bing.com 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:

http://sdrv.ms/ZkPktH

image

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: http://www.microsoft.com/visualstudio/eng/downloads#d-2010-express

Downloading “Visual Studio 2010 Express All-in-One ISO” from Microsoft.com 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 Microsoft.com.

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, Abakion.com & Supplychainbox.com