Using Placeholders in NAV Reports

Placeholders provide a method to customize the formatting and insert as an expression inside the textbox.

To add a placeholder, click inside the textbox, then right-click and select Create Placeholder as shown in the figure.

Modifying or removing a field in a standard report like Sales Order (10075) is a pain because all the textboxes show their expressions as <<Expr>>, as shown below. Trying to identify which textbox contains the field that you want to modify or remove could be tiring.

If we have used Placeholders inside the textbox then it will be much easier to identify them as Placeholders not only allows you to add expressions but also gives you the option to add Labels to it. The labels will appear on the report layout as shown below.

Microsoft does a good job in naming the textboxes so it is easy to identify them using the name, for example if I look at the same report in Document Outline you can see how they named

The advantage with placeholder labels is you can view that in design mode.

Placeholders also have the ability to format the text differently. You can add more than one Placeholder in a textbox. Similar to Textboxes, Placeholders also have many tabs as shown in the figure below, which will allow you to format and customize text accordingly.

You can add more than one placeholder in a single text box and format each placeholder differently.

One other advantage of Placeholder is, it has the option to Interpret HTML tags. You can add configure your expression to render using HTML formatting. This allows us to add HTML tags directly into the expression, such as line break, font, bolding, underline….

For example, we can use the following expression in the Value field, and select the HTML option for Markup type in the above figure.

<font face="Arial"><b>T </b></font> ' + “Phone No." + '<br>'

If you have any other tips or suggestions, please do share them in the comments below.

Share:

Read more..

Open Xml Error on preview or print of a report in NAV

Recently I was trying to print a report in NAV 2017 Demo Database, and I have encountered the below error. The same error occurred even on preview of the report.

The default report layout setup for the report is set to word , so it is trying to preview or print a word format report . So to answer why does it it need an OpenXml component to print or preview is

In order to generate a word document of the report, the data output of the report which is in XML Format will be transformed by using the .NET Framework components such as Open XML SDK 2.5. Since it is missing that component on my computer, it is giving that warning. Usually you need to install those component on the NAV Server to avoid this error.

There is a nice article on the NAV community blog which explains the whole execution process of a report with different layouts, please check the blog on the below link for more information.

https://blogs.msdn.microsoft.com/nav/2014/10/30/report-execution-insights/

Solution : To fix the above error you need to install OpenXML SDK from the NAV DVD folder Prerequisite Components, once you install the components please restart the services and you will be able to preview or print the report.

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.

Share:

Read more..

How to modify the Sales Invoice Report to print Serial No./Lot No.

There are several requests on the Microsoft Dynamics Community forum to get an example or for the logic to print the serial no. or lot no. on the sales invoice report. In this post I will explain how to modify the standard sales invoice report (Report 10074: NAV 2015) to print the serial no. or lot no. on the report.

For this purpose I have added a new function in the report 10074, called GetSerialLotNo

LOCAL PROCEDURE GetSerialLotNo@1240060002();
    VAR
      ItemTrackingMgt@1240060000 : Codeunit 6500;
      TempItemLedgEntry@1240060001 : TEMPORARY Record 32;
      InvoiceRowID@1240060002 : Text[150];
      LineNo@1240060003 : Integer;
      Inserted@1240060004 : Boolean;
      SerialLot@1240060005 : Code[60];
      ValueEntryRelation@1240060006 : Record 6508;
      ValueEntry@1240060007 : Record 5802;
      ItemLedgEntry@1240060008 : Record 32;
    BEGIN
      
      IF TempSalesInvoiceLine.Type <> TempSalesInvoiceLine.Type::Item THEN
        EXIT;
      TempItemLedgEntry.RESET;
      TempItemLedgEntry.DELETEALL;
      InvoiceRowID := TempSalesInvoiceLine.RowID1;
      ValueEntryRelation.RESET;
      ValueEntryRelation.SETCURRENTKEY("Source RowId");
      ValueEntryRelation.SETRANGE("Source RowId",InvoiceRowID);
      IF ValueEntryRelation.FIND('-') THEN BEGIN
        REPEAT
          ValueEntry.GET(ValueEntryRelation."Value Entry No.");
          ItemLedgEntry.GET(ValueEntry."Item Ledger Entry No.");
          TempItemLedgEntry := ItemLedgEntry;
          TempItemLedgEntry.Quantity := ValueEntry."Invoiced Quantity";
          IF TempItemLedgEntry."Entry Type" IN [TempItemLedgEntry."Entry Type"::Purchase,TempItemLedgEntry."Entry Type"::Sale] THEN
            IF TempItemLedgEntry.Quantity <> 0 THEN
              TempItemLedgEntry.INSERT;
        UNTIL ValueEntryRelation.NEXT = 0;
      END;
      IF TempItemLedgEntry.FINDFIRST THEN
        REPEAT
          SerialLot := TempItemLedgEntry."Serial No." + ' ' + TempItemLedgEntry."Lot No.";
          WITH TempSalesInvoiceLine DO BEGIN
            INIT;
            "Document No." := "Sales Invoice Header"."No.";
            "Line No." := HighestLineNo + 10;
            HighestLineNo := "Line No.";
          END;
          IF STRLEN(SerialLot) + 1 <= MAXSTRLEN(TempSalesInvoiceLine.Description) THEN BEGIN
            TempSalesInvoiceLine.Description := SerialLot;
            TempSalesInvoiceLine."Description 2" := '';
          END ELSE BEGIN
            SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            WHILE (SpacePointer > 1) AND (SerialLot[SpacePointer] <> ' ') DO
              SpacePointer := SpacePointer - 1;
            IF SpacePointer = 1 THEN
              SpacePointer := MAXSTRLEN(TempSalesInvoiceLine.Description) + 1;
            TempSalesInvoiceLine.Description := COPYSTR(SerialLot,1,SpacePointer - 1);
            TempSalesInvoiceLine."Description 2" :=
              COPYSTR(COPYSTR(SerialLot,SpacePointer + 1),1,MAXSTRLEN(TempSalesInvoiceLine."Description 2"));
          END;
          TempSalesInvoiceLine.INSERT;
        UNTIL TempItemLedgEntry.NEXT = 0;
      
    END;

The Serial No. and Lot No. information is stored in the item ledger entry table, so the above function is used to retrieve all the item ledger entries associated to a sales invoice line, and then store those into a temporary ledger entry table, by using the temporary ledger entry table we populate TempSalesInvoiceline table, which will take care of displaying the values on the report without modifying/formatting RTC report.

To print the serial no./ lot no. we just call the new function GetSerialLotNo on the OnAfterGetRecord of the Sales Invoice Line DataItem.

Below is an example of the invoice report printing serial no.
















Download the object from this link Sales Invoice Report

Please leave your comments, feedback or any suggestions you have for me to improve my blog and also if you have any questions, feel free to post.

Share:

Read more..

How to resolve “Divide By Zero” Error in Navision RTC Report

One of the common error in RTC reports is #Error and there are many reasons for #Error in RTC report, but in this post I want to discuss one of the scenarios i.e. "Divide By Zero" exception.

If you have an expression which results Divide By Zero Exception then the result you see is #Error, and normally to avoid this you can write an IIF statement in expression.

For Example:

To display the value as zero if the denominator is zero then I would write something like this

=IIF(Sum(Fields!LineAmt.Value)=0,0,Sum(Fields!NetGP.Value)/Sum(Fields!LineAmt.Value))

 

In the above expression, I am doing a quick check to see if Sum(Fields!LineAmt.Value) is zero, then I want to display Zero otherwise I evaluate my expression, it is pretty simple. But here is the problem the reporting processing engine will still evaluate FALSE part, in this case Sum(Fields!NetGP.Value)/Sum(Fields!LineAmt.Value) and the result will be #Error even if Sum(Fields!LineAmt.Value) is Zero.

There are two possible solutions in this case to resolve the error:

1st Option:

 

=IIF(Sum(Fields!LineAmt.Value)=0, 0,Sum(Fields!NetGP.Value)/iif(Sum(Fields!LineAmt.Value)=0,1,Sum(Fields!LineAmt.Value)))

 

2nd Option:

VB Function

Public Shared Function DivideByZero(ByVal Numerator As Decimal, ByVal Denominator As Decimal) As Decimal

   If Denominator = 0 Then

     Return 0

   End If

   Return (Numerator / Denominator)

End Function

 

and use the following expression

Code.DivideByZero(Sum(Fields!NetGP.Value),Sum(Fields!LineAmt.Value)

 

Personally I prefer the VB function because it is neat and can be used in multiple places.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post.

Share:

Read more..

Empty Rows/Columns on the Navision Report when printed to Excel

I was asked recently to create a  custom sales report which salespersons wants to output the data into excel, since RTC reports have the option to save as excel, I created a simple report with row header and values, so that users can run this report and use Print Save as Excel option, but when the report is saved as excel it created empty rows and columns.

Below is the example of the excel when the report is saved as excel

In my report design the tablix does not have any hidden rows or any other header rows, so I was started checking how those empty rows are created and can be avoided

This is the screenshot of my report design, as you can see there are only two rows, one is the header and other is the detail.

After spending an hour or so, I realized that space is not because of the tablix, but rather the space above the tablix and on the sides, once I removed all the space and made sure there is no space as shown below, the issue was fixed.

Below is the screenshot of the excel after the change

If you encounter these kind of issue, just don’t concentrate on the tablix on the report, do check the space around it . When the report is exported to excel it takes the whole report into consideration, so try to avoid blank spaces if you know the report will be used as excel.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post..

Share:

Read more..

Conditional Page break in RTC Reports

In this post I will try to explain how to dynamically control the page break for a group or in other words how to create a conditional page break in the NAV 2013 RTC report.

For this post I will use the standard report 5057 (Salesperson – To-do’s), this report is already grouped by salesperson but now we will add the option to page break (New Page per Salesperson).

1. Open report 5057 in design mode and declare a new global variable NewPagePerSalesPerson (Boolean) which we will use in the request page

2. Now we will add the option in the request page of the report for the user to choose page break, for that choose View à Request Page and add the variable as show in the below fig.

3. Add the variable NewPagePerSalesPerson to the report dataset and name it as PageBreakPerSalesPerson.

4. Now open the report in layout mode by choosing View à Layout, notice the report already has a group which is grouped on Salesperson field

Currently this group is not enabled for page breaks, but if you enable page break it will use new page per salesperson every time you run the report but we want to control that based on the value in the options window.

5. To enable page break based on the value in the options windows of the report, add a new a outmost group for the current group by right clicking on the Table1_Group and Choose Add Group à Parent Group

In Group Window open the expression editor by clicking Fx and leave other properties to their defaults

Add the following expression

=IIF(Fields!PageBreakPerSalesPerson.Value,Fields!To_do__Salesperson_Code_.Value,Nothing)

Click OK.

6. A new column is added to the table select the column and delete the column, and from the confirmation dialog choose Delete Columns only.

7. From the newly added group, right click and select Group properties

In the Page Breaks, select Between each instance of a group.

Now if you run the report with option selected New Page Per Salesperson it will create a page break for every salesperson.

There are some other standard reports like 108 - Customer - Order Detail, which has similar options and logic but instead of using an expression for a group it uses a variable value.

Please leave your comments, feedback or any suggestions you have for me to improve me my blog and also if you have any questions, feel free to post .

Share:

Read more..

Rendering output error for RTC Reports when you use SAVEASPDF

When i try to print a RTC report and choose SAVEASPDF option it was throwing an error which says “Rendering output failed”, i initially  thought the error was happening because of an expression but this was happening on all the reports which is using the logo.

Share:

Read more..