How Do I: Create Transfooter and Transheader in Microsoft Dynamics NAV 2013 R2

Some of you might have noticed that I started doing a few “How Do I” videos for Microsoft.

All the “How Do I” videos, not just the ones I have done, can be found here:http://msdn.microsoft.com/en-us/dynamics/nav/dn524022.

But the latest one I did, is not on the list yet. When you read this, the list is probably updated and the “How Do I: Create Transfooter and Transheader in Microsoft Dynamics NAV 2013 R2” has been added.

You can watch this “How Do I” video here on YouTube:

http://youtu.be/UZAN2HoC5ng

But this blog is not to complain that this list has not been updated, but only to point you to the Report Object which I’m uisng in this “How Do I” video.

So if you want to have a closer look at the report object, it is located here on my OneDrive:

 http://1drv.ms/1cC3ouA

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

Convergence 2014 Session now available on demand

Did you not attend Convergence 2014 a few weeks ago, or do you want to see the presentations once again. View the Convergence 2014 sessions here:

https://presentations.inxpo.com/Shows/microsoft/MSConvergence/3-14/Login/index.html

You need to click the “Register Now!” to get into the goodies.

image

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

Visual Studio 2013 is now supported in NAV 2013 R2

We now have support for Visual Studio 2013 with NAV 2013 R2 when designing reports.

image

Apply this hotfix:https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;2907585, and you will then open your layout in Visual Studio 2013 if installed.

Why can we use Visual Studio 2013? Because it also supports RDLC 2010 supported in NAV 2013 R2. Here is a small overview of the different RDLC versions and NAV versions and Visual Studio versions:

RDLC 2010 = NAV 2013 R2 = Visual Studio 2013 Pro(New), Visual Studio 2012 Pro or Report Builder 3.0

RDLC 2008 = NAV 2013  = Visual Studio 2010 Pro, or Express using this tip here

RDLC 2005 = NAV 2009  = Visual Studio 2008, read more here. Do not use VS2005

Not a single of the bugs introduced in Visual Studio 2012, which I know of, has been fixed, except the possibility to use the LEFT ARROW key in expressions. In Visual Studio 2012 this is not possible. Now with Visual Studio 2013 we can now use the LEFT ARROW key. So if this is driving you crazy, go and apply above hotfix and start designing you R2 reports in Visual Studio 2013.

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

And the Winner is…1ClickFactory – Updated

This has been a fun week. Everybody who knows me are well aware that I always like a good competition, and also that I really hate to lose.

But 1ClickFactory upgraded the Service Contract report 5970, in less than 8 minutes. that is pretty impressive guys. So 1ClickFactory is the winner of this weeks competition if you ask me, and yes I lost, but my report was better looking. Smiley

If you missed this weeks competition you can watch me upgrade the Service Contract report 5970 manually, and Daniel Parker from ArcherPoint using the YAVEON Report Converter here, neither Daniel or I completed the report 100%, I was very close so with 10 min more I would have been done. This recording can be found here:http://spezialloesungen.yaveon.com/products/yaveon-report-converter/. The sounds is terrible so I’m really considering reshooting my manual upgrade of this report. If you would like me to do this, leave a comment, and I will do this if many people request this.

If you want to watch the winner video from 1ClickFactory go here: https://www.youtube.com/watch?v=q7Zb-Xyrl0g

Here are few pros and cons for each of the solutions from my point of view:

Manual Upgrade:

Pros:

- You become an expert in creating RDLC reports and know how to create new reports going forward.
- You will utilize many of the new capabilities and not just have a doll classic report upgraded

Cons:

- Expensive to learn how to do the RDLC reports correctly. Learning on the job is hard, because Visual Studio has its own life, and does not do what we expect it to do. You need to learn all the tips and tricks. And what works and what does not work. Report training is strongly advised. And yes I do training, but I don’t write this just for you to hire me, but if you attend a training class in RDLC I strongly recommend you verify that the trainer has upgraded many reports. If not you will just get a lecture of what you already yourself can find on the internet.

- Could easily be more costly than using a Report transformation tool, if you are not fast. And you are not fast in the beginning because your struggle.

YAVEON Report Converter

Pros:

- You can stay in your comfort zone, Classic report designer and do all tags 
- Only has a upfront fee so if you many reports, and you get good at this tool, this might be the tool to consider. But really you have to be fast in creating tags to compete with the 1ClickFactory solution. If you have purchased the tool, you can use it everywhere. So not limited to one customer or one report developer.

Cons:

- You do not learn how to design RDLC report. How do create new report?

- Learning curve for putting in the right tags in Classic designer

- Document Outline in Visual Studio is unnecessary crowed. If you do not use Document Outline when designing reports, this might of course not be a problem for you, but I use Document Outline all the time.

- You get a dull Classic report look and feel

1ClickFactory Report transformation service

Pros:

- Lightning fast

- You get to learn how to do RDLC reports a little bit, since reports will only be upgraded 60%-80%

- Nice add in tool for moving things to Page header, but really we should only have Report Title, Page number and Images in header. Having i.e. 54 fields in header using Code.Set and Code.Get is crazy.. So this is just a show off tool, you should just place a repeating row outside main group, like I showed SmileyIf you cannot figure that out, this move to header tool is great and saves tons of time.

Cons:

- Not a onetime fee, as YAVEON’s tool

- 16 clicks for report 5970 is a bit overpriced. 1 click = 15 min. of work. I know 1ClickFactory says that it would take a average Report developer 4 hours to upgrade this report. I would say it should not take more than 2 hours for a report developer, who has been at a report training class, Still 1ClickFactory would probably be cheaper, depending where your are in the world of course

- Just like with YAVEON, Document Outline in Visual Studio is unnecessary crowed. Update: The Document Outline is actually great. Please find on my SkyDrive the Document Outline which we did not see in the 1ClickFactory presentation. You can also find the upgraded report from 1ClickFactory and my manual upgraded report. My report was only 98% completed.
Files are found here: http://sdrv.ms/18IeYzY

- And you get a dull Classic report look and feel

Conclusion

So I really want to love these Report convert tools, but end of the day you need to understand how to design RDLC reports, that is my opinion of course. So I would definitely invest time in understanding how to design RDLC reports, and when you have this knowledge, you can decide to upgrade the next 200 hundred report or how many you have using either 1ClickFactory or YAVEON’s tools.
If you disagree and do not want to be an expert in RDLC reports. These tools are your new friend, if you of course are not already using them. Both YAVEON and 1ClickFactory give you the option to try out their tools for free. YAVEON has a trial version, Contact Andreas H.E. Dorsch: andorsch@yaveon.de for a trial version. This weeks winner, 1ClickFactory, will give you 20 clicks for free, so you can check out how cool their service is.

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

Report Transformation Competition–Updated

Holiday season is upon us and it is time for a little competition.

On Monday 9th of December you will have the opportunity to join a 2 hour Report Converter Webinar hosted by YAVEON.

In the first hour, I will I transform a report all the way from NAV 5.0 to the brand new NAV 2013 R2. To be specific I will transform the Service Contract report ID 5970.

The Service Contract contains all the elements which can be found i.e. the invoice report ID 206. So this is a good report to practice on, when learning how to design Document reports

I will manually upgrade this report, using many of the tips and tricks which I have only shared at my Report Training classes. Of course this only being an hour I will not have the opportunity to share them all.

If you tune in on Monday I will also show a new trick, which I currently have only shown to 2 people. This will speed up report writing even more, and yes of course I will blog about this later when time permits.

Since this is a competition and I hate to lose ;-) I will upgrade the report quite fast, so I might not get every step explained in details, but you can at least see all the steps I take, and then hopefully the Webinar will be made available for download. afterwards.

In the second hour, Daniel Parker from ArcherPoint will show how to upgrade the exact same report, the Service Contract ID 5970, using YAVEON Report Converter. And of course upgrade this to NAV 2013 R2 as well.

Daniel Parker is an expert in using the YAVEON Report Converter, so I’m excited to see how fast he can do this and if he can get the report to look as cool as mine, and not just as doll as the Classic report ;-)

If you want to see me cry, because Daniel is faster, sign up for this 2 hour Report Converter Webinar hosted by YAVEON here:

http://www.yaveon.de/support/seminarewebinare/details/article/yaveon-reportconverter-webinar/

I believe there is limit of 50 online viewers, so you might want to hurry up if you want a seat.

If you are interested in purchasing the YAVEON Report Converter tool, contact Andreas H.E. Dorsch

But before I end this blog post, you might also have heard that 1ClickFactory also have a Report Transformation tool. This tool differs from the YAVEON Report Converter. For the YAVEON Report Converter tool to work, the report developer are required to insert tags in the Classic report, which then guides the YAVEON Report Converted tool to transform the report correctly. This is what Daniel Parker will show. What 1ClickFactory offer is to convert the report 60-80% and then the report developer will need to make the final adjustments in Visual Studio to complete the report.

On Thursday 12th of December, 1ClickFactory is also hosting a Webinar. This Webinar is not exclusive, because Daniel and I are not be presenting Smiley, der blinker, but I would really like to challenge 1ClickFactory to also show how they upgrade the Service Contract report ID 5970, not just 60%-80% but 100%, and to make it look good as well and upgrade it to NAV 2013 R2.

Because if they do this on the 12th of December, it will be very easy for the whole NAV community to compare which solution is best, and what strategy to take when upgrading Classic report to RDLC:

1. Doing a manual upgrade, Like I do

2. Using the YAVEON Report Converter

3. Using 1ClickFactory Classic report to RDLC transformation service

So Phillip Rubino if you read this, please accept this challenge so we all can compare and see which of the above 3 solutions are the best.

Update: 1ClickFactory has accepted the challenge, see more in comments. So next week you will have the option see all 3 ways of upgrading the same Classic report.
Let the best man or tool win next week Smiley
 

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

When to use PadRight to add Leading Dots and when not to use PadRight

In my now many report training classes I have always shown a small example on how to use the PadRight method to extend the string with additional dots(.), also called Leading Dots. I.e. like this:

image

In left column I have just added the Customer Name, while in the second column I’m using the method PadRigth to add additional dots(.) to the Customer Name string.
To accomplish this I have the following custom code in my Report:

Public Function MyPaddedString(ByVal Value As String)
    Return Value.PadRight(100,".")
End Function

And then in my expression in my right column I have this expression:

=Code.MyPaddedString(Fields!Customer_Name.Value)

This works fine, but I have always found that the dots(.) are to close to each when usign PadRight, so I have usually challenged the people attending my report class to come with a solution to add more space in between the dots(.), and now finally somebody, Christian Bockelmann from Agiles, took the bait and came with a solution, which I would like share with all of you.

What Christian is doing is not using the PadRight method, but checking on the lenght of the Customer Name and then applies a space and dot(.) until he reaches 100. Just like in my above PadRight soluiton.

Public Function MyPaddedStringUsingLength(ByVal Value As String)
  while(Value.Length < 100)
    if(Value.Length <> 99) then
      Value = Value + " ."
    else
      Value = Value + " "
    End if
  End while
  Return Value
End Function


Actually quite simple. So with Christians solution I can now add a new column to my PadRight example:

image

In the new column to right I have the following expression:

=Code.MyPaddedStringUsingLength(Fields!Customer_Name.Value)

And as you can see the leading dots now looks much better since they are now all seperated with a space.

If you want, you can download the solution here on my SkyDrive. It’s in RDLC 2008, NAV 2013 version, but you can easily upgrade to NAV 2013 R2 if you want or apply the above code to RDLC 2010 if you want. If you are not using Dynamics NAV, just open the OfflineFolder and open the solution file “OfflineReport.sln” with Visual Studio 2010 Pro or higher version of VS2010. Express version does not work with Offline Reports.

Thanks again Christian for this simple solution

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

Using Template reports for faster RDLC report writing

When designing RDLC reports it is an advantage to have all the report controls which you often use close at hand. As many you know I give out my template reports when you attend one of my many RDLC Reporting Training classes, but after multiple requests I now also place these Template Reports on my public SkyDrive. If you want to use or modify them it is of course up to you, but these template reports increases my speed when I create RDLC reports, so maybe they can also help you become faster when designing RDLC reports.

Here is an image of my Portrait Letter template report for NAV 2013 R2:

image

Every time I design a new report I will get this report shown to me. To accomplish this I replaced the existing report.rdlc file found in the ReportLayout folder in the RTC folder.

As you see I have quite a few elements on this report. Notice that all elements are using the rules outlined in the Report UX Guidelines released by Microsoft now almost 2 years ago. A bunch of standard reports in NAV 2013 and NAV 2013 R2 have been designed after these guidelines. Please notice that the Report Guidelines says that you need to use textbox height of 10 pt. This is a unfortunately a mistake. If you use 10 pt. height in your textboxes AND 8 pt font size you will in some cases have the bottom of the letters: j, g and p cut of. To avoid this I’m now using 11pt. in height for all my cell heights, except the title which needs to be 20pt. in height and 14pt. font size. For font I’m using Segoe UI. Segoe UI, is the standard font use in Windows.

If you are curious about the guidelines for Windows read more here:
http://msdn.microsoft.com/library/windows/desktop/aa511440
And specific for the font Segoe UI, read here:
http://msdn.microsoft.com/library/windows/desktop/aa511440

In the Page Header I have the following textboxes:

1. Title which says INSERTTITLE. Title is unique to each report so you need to change this.

2. Company name. Here you need to insert Company name information. Since I cannot have data bound fields in my template report you will need to add this as well.

3. Execution Time. This field is not data bound, so you can just leave this field

4. Page Caption and PageNumber. Here PageNumber is not data bound while the PageCaption is, so you will need to add the PageCaption to your dataset and add this to this textbox. Notice that you will need to delete “INSERTPAGECAPTION” and create a new placeholder to add you new PageCaption from dataset, since I already have 2 Placeholders in this Textbox. If you don’t like this, just modify the template report, so it fit your needs.

5. Spacer. Just below the CompanyName I have a spacer which is 20pt in height as outlined in the Report UX Guidelines. In some standard reports this spacer has been removed after the report developer has designed the report, but I find it good practice to keep this, since it is much easier to keep this space between the header and body when you make modifications.

I often just open my Template report and copy the page header part over to an existing report where Page header is missing or not designed correct. Therefore I have place a rectangle around all my elements in the page header so it easy to copy the rectangle, since this will just include all of the above fields.

In the Body I have added the 3 different tablixes all with the correct dimensions according to the Report UX Guidelines. Correct height 11pt. for cell height, font set to Segoe UI, just like I have in my page header.

When I create RDLC report I constant need 1 or more of these Tablixes in my report. And having these tablixes setup correctly is a huge time saver for me. I.e. I have set the following up the Tablix used for list reports:

  • Correct font used
  • Correct Cell height used
  • Correct Padding used(Notice that I cheat and set everything to 0pt, and only add 5pt. as outlined in Guidelines when needed. The cost of having 5pt left padding and 5 right padding in document is using to much space in document reports )
  • Greenbar effect in the list(WhiteSmoke every second line)
  • Table created as 4 rows, Outlined in Guidelines here:http://msdn.microsoft.com/en-us/library/jj651611(v=nav.70).aspx
  • Again I’m cheating by adding hidden row in the table header. I do this to avoid Visual Studio adding default hardcoded captions to my report.
  • Added so all Header Table rows are repeated when detail rows spans more than one page. Not sure why this is not just standard in Visual Studio, and why this is so much hidden, and not just work in the Tablix menu, where these buttons don’t work at all, or I’m not smart enough to get them working. If you can get any these button working, I will invite the first person who will accomplish this to noma the world best restaurant situated in Denmark. I will not pay for your trip Denmark, only the restaurant :-) Watch the video, it is great fun. :-)
    So once again none of these buttons work in the Tablix property page in Visual Studio 2010, 2012 and 2013. The worked fine in Visual Studio 2008. Maybe the responsible guy for these buttons left Microsoft when Visual Studio 2008 was released… ;-)

image

Also in my template I have few code snippets I use:

  • Our bellowed SetData and GetData used in all standard document report. I have simplified the code to not use groups, since this concept was discontinue in NAV 2013 but unfortunately the code was not simplified. I rarely use the GetData and SetData in my document reports. Only real reason is when the customer request that the Page number is not in the top of page(Page Header) or bottom of the page(Page Footer), because it is impossible to get the page number in the Body of the report. So if this is a requirement I am forced to use SetData and GetData.
  • Divide by zero protection: 5 times I have now seen that doing Divide by Zero protection inside Expression not working, so it has now become best practice for me to do pass the 2 values to my Function DivideValues

Notice that I have also set the correct values in the Report Properties so margins are set correct based on the Report UX Guidelines. This also the main reason I have A4(Portrait), A4(Landscape), Letter(Portrait) and Letter(Landscape) in my template folder.

You can find my template reports for NAV 2013 and 2013 R2 here on my SkyDrive, and if you prefer to have them as objects in your database instead you can also import them. These reports have a little more added since the data bound compared to single Report.rdlc which we just copied into “C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailored Client\ReportLayout”

I hope your speed of creating reports will increase by using my Template reports. If the do not fit your setup, I urge you to create your own, because these are a huge help when creating and modifing reports. You can also just have different report.rdlc files places in a folder which you can just open in Visual Studio and copy from when needed.

/Thanks, Claus Lundstrøm, MVP for Microsoft Dynamics NAV
Abakion.com & Supplychainbox.com

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

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:

image

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

image

My report now looks like this:

image

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:

image

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

image

/Thanks, Claus Lundstrøm, Abakion.com & Supplychainbox.com

Create a new blog and join in the fun!