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

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

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

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

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:

image 

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

Designer:

image

Print:

image

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:

Designer:

image

Print:

image

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.

Designer:

image

Print:

image

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:

image

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. http://sdrv.ms/155VRjV

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

RDLC Report – Filtering and Visibility “Best Practice”

Well, you could of course argue that just because I have starting to using this, it is not best practice, but I have now had so many issues with the below 2 filters which are used all over the standard reports. So I have started using another approach.

Here are the 2 filter patterns used everywhere in standard reports in Dynamics NAV.

1. Fields![Field].Value > ””

2. IsNothing(Fields![Field].Value)

These filters are often used when filtering rows on Tables and very often in Visibilty expresssion. In many scenarios they work fine, and I guess that is why the report developers decided to to use this way of filtering, but they do not work in all scenarios.

Let me explain!!!

From the standard Report 108 I have taken 2 columns, a Code and Decimal columns, to show that you want to be careful when using above expressions.

For both the Code Value and the Decimal Value I have created 5 columns:

Row: Row number in Dataset

Code Value: The actual value of the field. I have cheated a little so the NULL value “<>” also shows here in my report, normally they look like blank data when the report is rendered.

Field > ””: The actual field with an if statement to tell us if the value is empty or not using Fields![Field].Value > ””

IsNothing: The actual field with an IsNothing statement to tell us if the value is empty or not using IsNothing(Fields![Field].Value)

Len: The actual field with a Len statement to tell us if the value is empty or not using Len(Fields![Field].Value). Len returns an integer containing either the number of characters in a string or the number of bytes required to store a variable.

image

As you can see in left part of the above picture you have to be careful to use IsNothing, when you are working with Code fields, the same goes for Text fields. IsNothing only returns the right result when the text actual is present in the row and when the value is NULL, represented with a “<>” in the dataset. When the value is blank, it is incorrectly saying false. I have marked the False red in the rows where it actually should be True. Notice that both using Fields![Field].Value > ”” and Len(Fields![Field].Value) produces the right result in the left part of the picture.

As you can see in the right part of the above picture you have to be careful to use Fields![Field].Value > ”” , when you are working with Decimal fields, Fields![Field].Value > ”” only produces the right result when the decimal value is Null(“<>“) and when the value is not NULL the expression returns an #Error. Errors like these we want to avoid, and you cannot see these errors unless you copy the Visibility or Filter Expression to a textbox which is visible in the layout. Hint! I always do this if the expression is complex and I want to make sure that it returns TRUE and FALSE the right places. Also Visual Studio will not warn you when you build the Solution / Web Site with errors in you filter or visibility expressions. But Report Viewer will very often complain at run time, and you are presented with Blank screen in Report Viewer. When you see the Blank screen you have made an error in a filter or visibility expression. Report Viewer will show you the error in under a millisecond so really hard to see, but if you want to see the error, print to PDF or copy the Report.rdlc and Dataset.xml to an Offline Project.

If we look a little more at the right side of the above picture you can see that IsNothing is now producing the right result for each row. And also we see that Len is producing the right result. So no matter what value I come with, Len will produce the right result no matter what, and that is the reason I have started using Len in all my Filter and Visibility expression.

When you use Len in a Visibility expression, you need have the syntax to look like this:

=iif(Len(Fields![FIELD].Value) = 0, TRUE, FALSE)

When you use Len in a filter expression, you need have the syntax to look like this:

=Cstr(Len(Fields!SalesHeaderCurrCode.Value)) and then in the Tablix Properties page you set the Operator to “<>” and the Value to “0”. If you want to avoid using Cstr to convert all to Text, you change the value to the right of the Expression to Integer. Since we are now using the Len on all fields, the Cstr() is just unnecessary overhead.

With CStr:

image1

Without Cstr:

image2

Unfortunately Visual Studio changes the value Integer to Text each time you open the Expression, so you might want to keep Cstr in front of the Len function, so you do not create any confusion for other developers coming after you, and want to see what is in the Filter Expression. I guess it is just one more of the things in Visual Studio we have to live with…

You can find the report I used for this blog post here at my OneDrive: http://sdrv.ms/1a1RlBk. It is in a Offline Project so it is not required to have NAV Dynamics installed to look at this report, and the expression I used. You just need to have Visual Studio 2010 installed to be able to run this, as I previous outlined here: Offline, preview and debug reports in Microsoft Dynamics NAV 2013

/Thanks, Claus Lundstrøm

image3

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: http://blogs.msdn.com/b/nav/archive/2009/10/08/send-email-with-pdf-attachment-in-nav-2009.aspx

image

image

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:http://sdrv.ms/1aeGiTr

and then I just upgraded this for NAV 2013 as well: http://sdrv.ms/10oT3Kd 

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 http://sdrv.ms/ZDdFtn 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, Abakion.com & Supplychainshop.com