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

Advertisements

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 2

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

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

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

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

Private running As Double = 0

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

Public Function GetTotal() As Double
    Return running
End Function

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

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

image

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

In the RunningValue details textbox add this value:

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

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

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

image

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

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

“BreakLocation=Between”

“ResetPageNumber=True”

image

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

Ok, our work in the body is now complete.

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

In this I have this value

Code.SetTotal(ReportItems!RunningValue.Value)

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

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

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

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

=Code.GetTotal

and Visibility expression is just:

=Globals!PageNumber = 1

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

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

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

image

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

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

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

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

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

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

Transfooter / Transheader working with groups – Part 1

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

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

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

Scenario 2. Report with groups and with page breaks in between each group. For us to solve this scenario 100% we need VS 2012 which is supported in NAV 2013 R2. I have explained how to do this here: “Transfooter / Transheader working with groups – Part 2”

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

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

Let’s begin!

First copy this code into the Report Properties:

Private running As Double = 0

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

Public Function GetTotal() As Double
Return running
End Function

Private Header As Boolean = 0

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

Public Function GetShowHeader() As Boolean
Return Header
End Function

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

Now you need to add 2 columns to your main tablix

image

We need these columns since we need the RunningValue of the Amount_SalesLine in the details and we need to know when we have reached the last row. Both the following expressions need to be placed in the details row, for this to work, so not in the Group Header and not in the Group Footer. For demo purpose I have made these Columns big so you can see them, but you of course would make these as small as possible and hide the values so they are not visible on the report.

In the RunningValue details textbox add this value:

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

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

In the Group details textbox add this value:

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

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

Our work in the body is now complete.

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

image

In the small TextBox I have the following value:

=Code.SetShowHeader(ReportItems!GroupDone.Value)

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

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

Code.SetTotal(ReportItems!RunningValue.Value)

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

In the Transfooter we set the visibilty expression to:
=ReportItems!GroupDone.Value OR iif(Globals!OverallPageNumber = Globals!OverallTotalPages, TRUE, FALSE)

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

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

=Code.GetTotal()

and Visibility expression is just:

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

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

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

image

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

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

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

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

Orphan static rows in Visual Studio 2010

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

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

image8

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

image9

My report now looks like this:

image10

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

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

Option 2: Create Tablix from scratch.

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

image11

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

image12

/Thanks, Claus Lundstrøm

What is new in Dynamics NAV 2013 R2 for Reporting

Biggest News, support for RDLC 2010 and Visual Studio 2012

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Dynamic Headers and Footers in RDLC reports

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

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

See example here in Print Preview:

imageimageimage

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

imageimageimage

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

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

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

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

https://connect.microsoft.com/dynamicssuggestions and then select NAV on the list.

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

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

My Report in Visual Studio looks like this:

image

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

http://sdrv.ms/ZDdFtn

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

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

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

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

Now it seems that Microsoft have removed the “Microsoft Visual Studio 2010 Shell (Integrated) Redistributable Package” which before was found here: http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=115

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

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

So navigate to my Skydrive and download the files here:

http://sdrv.ms/ZkPktH

image

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

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

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

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

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

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

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

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