Never close down Visual Studio when designing RDLC Reports

One thing I often see when I see other people designing RDLC Reports is that they almost have the Report Dataset Designer maximized in NAV Object Designer. Nothing wrong with that at all, but when they are designing RDLC reports in Visual Studio and hit ESC in NAV to run the report they always get this message in NAV

“The report layout is currently open in the report designer. You must close the report designer window before closing the report object.”

image

The reason for this message is that NAV team is protecting you from closing down the Report Dataset Designer and their by loosing the connection to the report.rdlc file currently being designed. This protection was introduced in NAV 2009 SP1, since NAV 2009 RTM version you could open multiple versions of Visual Studio and then potential loosing a lot of work done in Visual Studio before importing it back to NAV.

So now the problem comes, because what I then see people doing are navigating to Visual Studio and closing down Visual Studio. To me this is fine if they know the report is completely done, but very often they are still developing the report. If you also do this, I suggest that you do not close down Visual Studio, since Visual Studio has a great feature called “Undo”. Often when designing RDLC reports you make a change that you want to verify and if you have closed Visual Studio you have lost the possibility to revert to an earlier version. I know many of you guys are not used to have this possibility, since we in NAV do not have this possibility. BTW, this would be great if we had this possibility in NAV, Microsoft please make this happen 🙂 Undoing things is much faster then undevelop things. And you do not need to close the Report Dataset Designer window to run the report in NAV, just hit CTRL+R or if you are a mouse person select “File / Run”

But if you keep Visual Studio open, how do you get your changes back into NAV?
You have 2 options:

Option 1. Save the changes in Visual Studio (CTRL+S)
or
Option 2. Build the solution in Visual Studio
Building the solution really only verifies that the XML in RDLC file is in a well formed according to rules for RDL. Notice You can build with all these options, all doing exactly the same in our scenario:

  • Build Solution(Notice that I change the shortcut to F11 🙂 )
  • Rebuild Solution
  • Build Web Site
  • Rebuild Web Site

image1

I prefer option no. 2 since if I have done anything wrong I get the warning in Visual Studio and do not need to take a round trip to NAV. But also notice that when using option 2, you sometimes can no warning in Visual Studio, but a warning in NAV. Why is that? Well Visual Studio is using their own compiler while NAV is using Report Viewer to verify the RDLC. There really should not be a different but sometimes you will see that everything just compiles fine in VS, and when you move to NAV you get errors, a bit strange that this is happening, but it is of course 2 different verifications so eventually they will be different, unfortunately.

Also when using option no. 2, you sometimes also get in a scenario that you can only save a new version of Report.rdlc:

image2

Again why is that? Well simple, end this task in Task Manager, “Microsoft.VisualStudio.Web.Host.exe”, this process sometimes places a lock on the Report.rdlc file you are currently designing and when you try to save it, you cannot since it is locked by the “Microsoft.VisualStudio.Web.Host.exe” and you are presented with the Save As dialog. Quite a pain if you don’t know that you can just end the “Microsoft.VisualStudio.Web.Host.exe” task in Task Manager.

image3

But I have gotten used to this and keeping Visual Studio open all the time, until I’m completely done, I can always undo the changes that I made and I can easily go 10-40 steps back if I have gone down a path I shouldn’t have.

Warning! In Visual Studio 2010(NAV 2013) and Visual Studio 2012 / 2013 (NAV 2013 R2) this works fine, but in Visual Studio 2008 (NAV 2009) you seem to be able only to undo 10 steps back, but really you should not design RDLC reports in NAV 2009 since this is using RDLC 2005 which is pretty much a beta version in my eyes. RDLC 2008 and RDLC 2010 are much more mature. We though still miss the possibility to have the Page No. in the body of our reports for RDLC to really grow up and be an adult Report Designer. Microsoft, will we ever be able to have the Page No. in the body of our reports? Would solve majority of the problems I have with RDLC Reports.

/Claus Lundstrøm, now at Continia working on a secret product. Updated now Continia Expense Management is released and is no longer a secret.
Read more about this here:
http://continia.com/profile-(4).aspx

Advertisements

Report Builder was unable to paste successfully

If you found this page you might have seen this error when trying to copy 1 or more elements in Visual Studio when designing a Report: “Report Builder was unable to paste successfully

This issue is that 1 of the selected textboxes you are trying to copy has custom code in it, and then you cannot copy the textbox(es).

I.e. you cannot copy a textbox with this custom code: “Code.MyCustomeCode()

Well actually it is not an issue, it is bug, which have existed many years now and Microsoft promises to fix the issue in a future version, see here and please vote:

https://connect.microsoft.com/SQLServer/feedback/details/767968/visual-studio-2008-unable-to-copy-and-paste-textboxes-in-ssrs-reports-with-custom-code-after-2008-r2-sp2-upgrade

Well future version is a bit a tricky dear Microsoft since nothing has happen since SQL Server 2008 R2, see my blog here: http://localhost/wordpress/2014/07/25/rdl-vs-rdlc-versions-and-dynamics-nav-v2/

So let me give you 2 workarounds. The obvious one, but hard and then the easy one.

Option 1, obvious one, but also hard if you have many textboxes with custom code:

1.Open the Expression and copy the custom code in the expression

2. Close the Expression

3. Create new textbox

4. Open Expression, and paste the custom code

5. Now set font, padding and size just like the other textbox

Option 2, the easy one:

1. Open the textbox with custom code

2. Now add a comment after the custom code.
I.e. Code.MyCustomeCode() ‘This is my custom code’

3. Close the Expression

4. And copy the textbox

Don’t ask me why this is working, but now you have a good reason to comment your code. If you´are designing an ISV solutions or if you are the NAV team and have custom code in any of your reports be kind to add a comment in the expression so developers after you can copy your text boxes.

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

RDL vs. RDLC versions and Dynamics NAV v.2

A few years ago I wrote a blog post outlining when each version of RDL(Server side Reporting, not supported out of the box in NAV) and RDLC(Client Side Reporting, supported in NAV) was released and which are supported in which version of Dynamics NAV. During my many Report training classes I always start explaining this chart in details.

http://localhost/wordpress/2012/08/08/rdl-vs-rdlc-versions-and-dynamics-nav/

I have trained over 500 NAV developers now and as always if you have been at my training, you are more than welcome to contact me if you think there was something I missed talking about during the class you attended and then I will of course guide you in the right direction for free. This way my training class just gets better and better since I don’t like to give to much free support 🙂 The next couple of months will be pack with training classes mainly in Denmark, Belgium and USA. If you are interested just leave your contact details here: http://localhost/wordpress/about/

But back to point…

Since the NAV team has been real busy is is time for an updated chart which explains when each version of RDL and RDLC was released. It also clear in this chart that the SSRS team in the SQL team, responsible for RDL is planning something big in the next version since nothing has happen since SQL Server 2008 R2, or they have stopped improvements to RDL. We all love updates and improvements, when the upgrade is easy, so let us bet on them making RDL better in next version of SQL Server. Not sure we will win that bet though, but let’s hope.
The Chart should be self explanatory, but if you join my training I will of course explain it in details:

RDL vs RDLC

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

Export images from RDLC report using Excel

In this small tip I will show you have to export images from a RDLC report in NAV 2013 R2. You can do this in NAV 2009 and NAV 2013 also, but then you need to convert the Excel Spreadsheet to the latest version.

Scenario A: You have embedded a image into a RDLC report and no longer have access to the original image, and for some reason you need to extract the images out of the report.

Scenario B: You would like to export i.e all item images so you can have access to all them in one folder, for this you will of course need to create a report which runs through all items and shows the image. If you don’t have a report like that, have a look at my demo report here: http://1drv.ms/1qLDYRj

Here is what you need to do.

1. Print your report to Excel and save it to disk as Report.XLSX. In NAV 2013 R2 the report will have the extension XLSX. Notice that neither NAV 2013 nor NAV 2009 will produce XLSX format here you will get XLS, the old Office 97 format.

2. Rename the Report.XLSX to Report.ZIP

3,. Now open the Report.ZIP in Explorer

4. Navigate to the folder “XL/MEDIA” and here you will find all the images which are in the report.

image

Note to do the same in NAV 2009 and NAV 2013, save the report as Report.XLS, then open the report.xls in Excel 2007 or newer and save as XLSX format. Then you also have access to the images, by renaming the Report.xlsx to Report.zip.

 

/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. Winking smile

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

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 presenting Winking smile, 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 Winking smile
 

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

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:

image5

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:

image6

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 OneDrive. 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

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 A4 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… 😉

image4

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 OneDrive, 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 NAV71RoleTailored ClientReportLayout”

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

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