Report – Page 6 – ARKSD Consultancy Private Limited

Development Tips, Excel, Office Integration, Report, Server

Viewing Page Data in Excel Using PowerPivot (OData)

August 19, 2015August 19, 2015 Ashwini Tripathi

Here I will discuss how you can use OData to expose a Microsoft Dynamics NAV 2015 page as a web service and then analyse the page data using Microsoft PowerPivot for Excel 2013.

With OData and PowerPivot, you gain access to a powerful set of tools and technologies for data exchange and analysis.

This walkthrough illustrates the following tasks:

Publishing a Microsoft Dynamics NAV page as a web service.
Verifying web service availability from a browser.
Using the PowerPivot add-in for Excel to import the table data as a new worksheet.
This procedure also includes optional instructions about how to use a web service access key.
Creating a PivotTable from the worksheet, selecting relevant fields, and then organizing and formatting the data to highlight strategic data.

Optional:

If you want to use a web service access key to authenticate access to the web service, Microsoft Dynamics NAV must meet the following requirements:

The Microsoft Dynamics NAV Server is configured to authenticate users by using the NavUserPassword credential type.

There is a Microsoft Dynamics NAV user account that has a web service access key.

You can find more details in my earlier post here

Publishing a Page as a Web Service

You can publish a web service by using the Microsoft Dynamics NAV Web client or the Microsoft Dynamics NAV Windows client.

To register and publish a page as a web service

Open the RoleTailored client and connect to the CRONUS International Ltd. company.
In the Search box, enter Web Services, and then choose the related link.
In the Web Services page, choose New.
In the Object Type column, select Page. In the Object ID column, enter 21, and in the Service Name column, enter Customer.

This exposes the Customer Card page as an OData web service.

Select the check box in the Published column.

Choose the OK button to close the New – Web Services page.

Verifying the Web Service’s Availability

Security Note

After publishing a web service, verify that the port that web service applications will use to connect to your web service is open. The default port for OData web services is 7048. You can configure this value by using the Microsoft Dynamics NAV Server Administration Tool.

To verify availability of a Microsoft Dynamics NAV web service

Start Windows Internet Explorer.

In the Address field, enter a URI using the following format: http://Server : WebServicePort/ServerInstance/OData/

Server is the name of the computer that is running Microsoft Dynamics NAV Server.

WebServicePort is the port that OData is running on. The default port is 7048.

ServiceInstance is the name of the Microsoft Dynamics NAV Server instance for your solution. The default name is DynamicsNAV80.

For example, if the Microsoft Dynamics NAV Server is running on the computer that you are working on, you can use: http://localhost:7048/DynamicsNAV80/OData/

In my case: – http://indel-axt5283n1.tecturacorp.net:8048/DynamicsNAV80/OData/

The browser should now show the web service that you have published, as shown in the following illustration.

Note

If the browser cannot find the web service, it may indicate that the specified Microsoft Dynamics NAV Server instance is not running.

Make Sure Enable OData Services is checked.

Importing Microsoft Dynamics NAV Data into Excel

In the following procedures, you use PowerPivot to import Microsoft Dynamics NAV data into Excel. If you will be using a web service access key for authentication, only perform the second procedure; otherwise, only perform the first procedure.

To import Microsoft Dynamics NAV data into Excel

Start Microsoft Excel.

In Excel, on the PowerPivot tab, choose Manage.

This opens the PowerPivot for Excel window.

In PowerPivot, on the Home tab, choose Get External Data, choose From Data Service, and then choose From OData Data Feed.

The Table Import Wizard opens.

If your Microsoft Dynamics NAV implementation requires that you use a web service access key, you must specify the NavUserPassword credentials as described in the following steps:

In the Advanced dialog box, in the Security section, set the Integrated Security field to Basic. If your OData is configured to use SSL, then set the field to SSPL.

In the Password field, type the web service access key.

In the UserID field, type the user name for the Microsoft Dynamics NAV user account. For this walkthrough, use NavTest.

In the Source section, in the Service Document URL field, type the URL for the OData web service that you verified in the previous procedure, for example, http://localhost:7048/DynamicsNAV80/OData/.

In my case: – http://indel-axt5283n1.tecturacorp.net:8048/DynamicsNAV80/OData/

Choose the OK button to return to the Table Import Wizard.

In the Connect to a Data Feed page, in the Data Feed Url field, enter the OData URI that you verified in the previous procedure.

Choose the Next button.

Important: The URI must end with a slash (/) as shown in the example.

Verify that Customer appears in the Source Table column.

Select the check box next to the Customer web service, and then choose Finish.

After you see the Success message, choose the Close button.

The data from the Customer OData web service displays, and you can use the data to build pivot-based views in the Excel workbook.

Creating a PivotTable Containing Key Microsoft Dynamics NAV Data

In this procedure, you use the Excel workbook with data from the Customer web service to create a PivotTable from the worksheet. You select relevant fields and then organize and format the data to highlight strategic data. Building a pivot table is a way to select and arrange data so as to highlight and focus on key elements.

To create a PivotTable

In Excel, select the cell where you want the PivotTable located.

In the ribbon, choose the Insert tab, and then in the Tables group, choose PivotTable.

In the Create PivotTable dialog box, select Use an external data source, and then choose the Choose Connection button.

In the Existing Connections dialog box, on the Connections tab, under Connections in this Workbook, choose the data feed for your OData web service, and then choose the Open button.

Choose the OK button to add the PivotTable to the Excel worksheet.

The PowerPivot Field pane on the right side includes a list of fields from the Customer web service that where imported from PowerPivot.

In the PowerPivot Field List pane, choose Location_Code.

Tip

To quickly find a field in the field list, type part or all of the field name in the Search text box that is above the list of fields, and then press Enter to highlight the first field that contains the text. You can then choose the right arrow to proceed to the next field, and so on.

Select the Balance_LCY field.

Select the Name field.

You can now see the data in the body of the worksheet, as shown in the following illustration.

The PivotTable shows customers by location and individual customer balances, and also adds the balances by location. To make the information more readable, you can update the headings on the PivotTable.

Select the cell that has the heading Sum of Balance_LCY, and then, in the formula field, type Balance.

Select the cell that has the heading Row Labels, and then in the formula field, type Customers by location.

Select the empty cell that is below the Customers by location cell, and then, in the formula field, type Location not specified.

The above illustration shows how the worksheet looks after you make these changes.

Next Steps

Now that you have created your PivotTable, you can continue to enhance the data to make it more useful and readable. You can:

Add a column to the data that shows average balance by region.

Enhance data presentation with a graph.

Post the data in a Microsoft SharePoint environment with live data from Microsoft Dynamics NAV 2015.

Development Tips, Office Integration, Report

Using Automation to Write a Letter in Microsoft Office Word

August 8, 2015August 8, 2015 Ashwini Tripathi

Automation lets you use the capabilities and features of Microsoft Office products, such as Microsoft Word or Microsoft Excel, in your Microsoft Dynamics NAV application.

Today we will implement Word Automation from a customer card in the Microsoft Dynamics NAV Windows client.

Note: The Microsoft Dynamics NAV Web client does not support automation.

Most information that we need to transfer to Word for this example is in the Customer table. The Customer table contains a FlowField called Sales (LCY) that contains the aggregated sales for the customer.

In this example we are learning about Automation, so we will use the existing value. In a real customer installation, we would need to set up an appropriate date filter to get the sales for the past year only.

We also need to retrieve the information about our own company that we will use in the letterhead and in the greeting of the letter. This information is contained in the Company Information and User tables.

The Automation server must be installed on the computer that compiles an object that uses Automation. If you must recompile and modify an object on a computer that does not have the Automation server installed, then you must modify the code to compile it again. We recommend that you isolate code that uses Automation in separate codeunits.
Performance can be an issue if extra work is needed to create an Automation server with the CREATE system call. If the Automation server is to be used repeatedly, then you will gain better performance by designing your code so that the server is created only once instead of making multiple CREATE and CLEAR calls).

Performance can be improved by putting the code on the customer card because you do not have to open and close Word for each letter that is created in the session.

You can work around this problem. If Word is already open when it is called from the code, then the running instance is reused. You can manually open Word or do not close Word after creating the first letter.

We will extract and transfer data one customer at a time. We will also initiate this processing and the subsequent processing in Word from the customer card.

We will insert fields into the Word template and give these fields convenient mnemonic names that correspond to the names of the record fields that we are using.

To make this work, C/AL code must make two extra calls to Microsoft Office Word. You must call the ActiveDocument.Fields.Update method before using the fields. After you have transferred all the information, you must call the ActiveDocument.Fields.Unlink method. This ensures that you can successfully use the Word fields as placeholders.

In addition, while you can name the Customer or Address fields, you must reference them by indexing into the Fields collection of the document. This can make the C/AL code harder to understand.

Creating the Word Template for Use by Automation

First, task is to create a Word template that we will use to create letters to customers that qualify for a discount. To create the template, we will add mail merge fields for displaying data that is extracted from Microsoft Dynamics NAV that you want included in the customer letter, such as the customer’s name, contact, and total sales.

You will create and save the template on the computer running the Microsoft Dynamics NAV Windows client, because you will configure the automation object to run on the client.

On the computer running Microsoft Dynamics NAV Windows client, open Word and create a new document.

Choose where you want to insert the fields. Then, on the Insert tab, in the Text group, choose Quick Parts, and then choose Field.

In the Categories list, select Mail Merge.
In the Field names list, select MergeField.
In the Field Name box under Field Properties, type Contact. This field will display the name of your contact person at the customer site as taken from the Customer table.
Choose OK to add the field.

Repeat steps as above to add the remaining fields as follows:

Field name	Description	Underlying table
Name	The name of the customer.	Customer
Address	The address of the customer.	Customer
Sales (LCY)	The total amount that the customer has purchased from you.	Customer
Company Name	The name of your company.	Company Information

Save the Word document as a template with the name Discount.dotx in folder of your choice.

Creating the Codeunit and Declaring the Variables

The next step is to create the codeunit that calls Word and creates the letter.

To create the codeunit

In Object Designer, choose Codeunit, and then choose the New button to create a new codeunit.
On the View menu, choose Properties to open the Properties window of the codeunit.
In the TableNo field, choose the AssistEdit button to open the Table List window.
In the Table List window, select the Customer table, and then choose OK.

Close the Properties window.

To declare the variables

Choose the OnRun Trigger and on the View menu, choose C/AL Locals, and then choose the Variables tab.
On a blank line, type wdApp in the Name field and set the Data Type field to Automation.

Note

When you create an Automation variable, some hidden events are also created for it. If you want to delete the variable, be aware that the events are also not deleted. This can cause issues if you then create a variable with the same name.

In the Subtype field, choose the AssistEdit button. The Automation Object List window is displayed.
In the Automation Server field, choose the AssistEdit button.
In the Automation Server List, select Microsoft Word 15.0 Object Library if you are running Word 2013, or select Microsoft Word 14.0 Object Library if you are running Word 2010, and then choose OK.
From the list of classes in the Automation Object List, select the Application class, and then choose OK.

Repeat steps above to add the following two Automation variables:

Name	Data type	Subtype	Class
wdDoc	Automation	Microsoft Word 14.0/15.0 Object Library	Document
wdRange	Automation	Microsoft Word 14.0/15.0 Object Library	Range

Add the following variables.

Name	Data type	Subtype	Length
CompanyInfo	Record	Company Information
TemplateName	Text		250

Close the C/AL Locals window.

Writing the C/AL Code

Before you start writing the C/AL code that uses Automation, you must do some initial processing. You start by calculating the Sales (LCY) FlowField. Then, you check whether the customer qualifies for a discount. Finally, you retrieve the information from the Company Information and User tables that you use to fill in some of the fields in the letter.

To write the C/AL code

In the C/AL Editor, add the following lines of code to the OnRun section.

CALCFIELDS(“Sales (LCY)”);CompanyInfo.GET;

To create an instance of Word before using it, enter the following line of code.

CREATE(wdApp, FALSE, TRUE);

This statement creates the Automation object with the wdApp variable.

The first Boolean parameter in the statement (FALSE) tells the CREATE function to try to reuse an already running instance of the Automation server that is referenced by Automation before creating a new instance. If you change this to TRUE, then the CREATE function always creates a new instance of the Automation server.
The second Boolean parameter in the statement creates the Automation object on the client. This is necessary to use this codeunit on a page in the Microsoft Dynamics NAV Windows client.

Enter the following lines of code to add a new document to Word that uses the template that you designed earlier. If required, replace C:\Users\atripathi5283\Desktop\Nav-2015\Word Letter with the correct folder path to the template that you defined in the procedure.

TemplateName := C:\Users\atripathi5283\Desktop\Nav-2015\Word Letter\Discount.dotx’;wdDoc := wdApp.Documents.Add(TemplateName);wdApp.ActiveDocument.Fields.Update;

Because the Add method of the Documents collection requires that you pass the path to the template by reference, you must set up the TemplateName variable to hold this information. You will get a compilation error if you put the path into the call as a literal string.
The Documents property returns a Documents collection that represents all open documents. You can also see that the Documents collection object has an Add method, and that the Add method has the following syntax.
expression.Add(Template, NewTemplate, Document Type, Visible)
expression is a required argument, and it must be an expression that returns a Documents object. All the arguments are optional. You will use Template to open a new document that is based on your template.
For the syntax in the C/AL Symbol Menu, note that the Documents property returns an object of type DOCUMENTS, which is a user-defined type. The property returns a Documents class or IDispatch interface. This information helps the compiler perform a better type check during compilation. The following statement can also pass both the compile-time and the run-time type checks.
wdDoc := wdApp.Documents.Add(TemplateName);
Finally, the Add method returns a Document class. While you did not need to declare a C/AL variable for the interim Documents class, you have declared a variable for the wdDoc return value,.
The third line contains a call that must be made to ensure that the template works as intended.
wdApp.ActiveDocument.Fields.Update;

Transferring Data to Word

Now you can transfer the actual data from the Customer record to the placeholder fields in the Word document.

You have set up the first three fields in the template so that they can contain the contact, name, and address of the customer and you can transfer the data.

To transfer data to Word

Transfer the data by adding the following lines of code.

wdRange := wdAPP.ActiveDocument.Fields.Item(1).Result; wdRange.Text := Contact; wdRange.Bold := 1; wdRange := wdAPP.ActiveDocument.Fields.Item(2).Result; wdRange.Text := Name; wdRange.Bold := 1; wdRange := wdAPP.ActiveDocument.Fields.Item(3).Result; wdRange.Text := Address; wdRange.Bold := 1;

You cannot use the fields directly as variables and make an assignment such as Fields.Item(3) := Address. Instead, you use the Result property of the field. This property returns the result of the field as a range. You place this range in the wdRange Automation variable that you declared.
You then set the Text property of the range to the desired values, which is the name of your contact person and the name and address of the customer. Finally, you add bold formatting.
The data you are transferring must be in text format. If it is not in text format, then you get a compilation error. wdRange.Text expects arguments to be of type BSTR, which maps to either Text or Code. This means that any data that is not Text or Code must be converted before it is passed to Word. To convert a field to Text, you use the FORMAT function. All the fields that are transferred in this step are in text format, so no conversion is needed and the FORMAT function is not used. However, in this example, you also need to transfer the Sales (LCY) field, which is a Decimal field. To see how to convert the Sales (LCY) field, go to the next step.
To transfer and format the data from the Sales (LCY) field, add the following code.

wdRange := wdAPP.ActiveDocument.Fields.Item(4).Result;wdRange.Text := FORMAT(“Sales (LCY)”);wdRange.Bold := 0;

To transfer the information from the Company Information table, add the following code.

wdRange := wdApp.ActiveDocument.Fields.Item(5).Result;wdRange.Text := CompanyInfo.Name;

To complete the processing in Word, add the following code.

wdApp.Visible := TRUE;wdApp.ActiveDocument.Fields.Unlink;

The first statement opens Word and shows you the letter that was created. The second statement makes the fields work as placeholders.

Save and compile the codeunit

To-Do List

Although this code will work, you must add a few things to make it complete:

We recommend that you do not use a hardcoded template name. You should keep the template name in a table, and the user should select it from a page. You can then have different templates for different types of letters that you want to send to your customers.
You should add some error-handling code. For example, the CREATE call fails if the user does not have Word installed or if the installation has been corrupted. You should check the return value of CREATE and give an appropriate message if it fails.
The user should get a message if the customer does not qualify for the discount. In the example, the codeunit closes without any message.

Calling the Codeunit from the Customer Card

The final task is to ensure that you can call the codeunit from the Customer Card page in the Microsoft Dynamics NAV Windows client.

To call the codeunit from the Customer card page in the Microsoft Dynamics NAV Windows client

Open Object Designer, and then choose Page.
Select the Customer Card page and then choose Design.
On the View menu, choose Page Actions.
To add a new action, locate the action container with the subtype set to ActionItems.
Right-click the next line after the ActionItems container, and then choose New.
In the Caption field of the new line, type Word Letter.
Set the Type field to Action.
With the new action selected, on the View menu, choose Properties.
In the RunObject field, type codeunit Discount Letter.

Note

If you saved the codeunit that you created in the previous procedure under a different name, then substitute Discount Letter with the name that you used.

Use the arrow buttons to make sure that the new action is indented only once from the ActionItems container above it

Save and compile the Customer Card page.

To run the Customer Card and view the Word letter

In Object Designer, choose the Page
Select the Customer Card page, and then choose Run.
In the ribbon, on the Actions tab, choose the Word Letter

The letter document opens in Word.

Next Steps

The letter that you have just created only contains five fields and sample body text. Before you can use this letter in an actual situation, you will need to add some more fields, such as the name and address of your own company, the date, and the currency code, and the main text of the letter. It will also need some formatting to make it look more attractive. If you alter the order in which the fields appear in the template, you must change the numbering of the fields in the codeunit to ensure that the correct data is inserted into the appropriate fields.

Report

Usage of RUNREQUESTPAGE, EXECUTE, SAVEAS and PRINT function of Reports Object

August 3, 2015August 3, 2015 Ashwini Tripathi

Today we will discuss usage of RUNREQUESTPAGE, EXECUTE, SAVEAS and PRINT function of Report Object.

You will be using RUNREQUESTPAGE function to run the request page of desired Report.

We will save the value of Parameters in Table for further use. For this we will require to create a table with below structure let name it as Report Request Parameters.

Create a table called Report Request Parameters that has the following fields.

Field Name	Data Type	Length
ReportId	Integer
UserId	Code	100
Parameters	BLOB

Then we will use these value with

EXECUTE – To preview the report.

SAVEAS – To save it as pdf file.

PRINT – To print the Report.

To continue with our demo we will require to create a codeunit, let’s name it as Using Report Functions.

We will add the following variables in the C/AL Globals window.

Variable name	DataType	Subtype	Length
ReportParameters	Record	Report Request Parameters
XmlParameters	Text
OStream	OutStream
IStream	InStream
CurrentUser	Code		100
Content	File
TempFileName	Text

Now we will add the following code to the OnRun trigger of the codeunit.

We will be taking Report 206 as an example for this demo.

You can copy below code for your demo.

// Use the REPORT.RUNREQUESTPAGE function to run the request page to get report parameters

XmlParameters := REPORT.RUNREQUESTPAGE(206);

CurrentUser := USERID;

// Save the request page parameters to the database table

WITH ReportParameters DO BEGIN

// Cleanup

IF GET(206,CurrentUser) THEN

DELETE;

SETAUTOCALCFIELDS(Parameters);

ReportId := 206;

UserId := CurrentUser;

Parameters.CREATEOUTSTREAM(OStream,TEXTENCODING::UTF8);

MESSAGE(XmlParameters);

OStream.WRITETEXT(XmlParameters);

INSERT;

END;

CLEAR(ReportParameters);

XmlParameters := ”;

// Read the request page parameters from the database table

WITH ReportParameters DO BEGIN

SETAUTOCALCFIELDS(Parameters);

GET(206,CurrentUser);

Parameters.CREATEINSTREAM(IStream,TEXTENCODING::UTF8);

IStream.READTEXT(XmlParameters);

END;

// Use the REPORT.SAVEAS function to save the report as a PDF file

Content.CREATE(‘TestFile.pdf’);

Content.CREATEOUTSTREAM(OStream);

REPORT.SAVEAS(206,XmlParameters,REPORTFORMAT::Pdf,OStream);

Content.CLOSE;

// Use the REPORT.EXECUTE function to preview the report

REPORT.EXECUTE(206,XmlParameters);

// Use the REPORT.Print function to print the report

REPORT.PRINT(206,XmlParameters);

We are done, definitely this will help you doing some useful stuffs in your projects.

Run the Codeunit and see the output. You will get three output

a) Report Output will be saved as pdf, you can specify your custom path to save the file to specified location.

b) Report Print Preview

c) Report will be send for Printing

Report

Managing Report Layouts

July 31, 2015July 31, 2015 Ashwini Tripathi

Microsoft Dynamics NAV 2015 enables reports to have a single built-in RDLC and Word layout, shared among tenants, as well as any number of customized layouts per tenant.

To browse and manage which layout is currently used for a given report, a new Report Layout Selection list page has been introduced.

We will use the Report Layout Selection page to switch between and run the RDLC and Word layouts that are shipped as part of the new Sales Invoice report 1306.

Open Report Layout Selection page by doing one of the following:
In the Search box, enter Report Layout Selection, and then choose the related link.
In the navigation pane, choose Departments, Administration, IT Administration, Reports, and then Report Layout Selection.
In the Small Business Role Center, on the Actions tab, choose Setup, Company Information, and then choose Report Layouts.
Ensure that the Company Name field is set to the correct company because reports layouts are company-specific.

The Report Layout Selection page lists all of the reports that are available for the company that is specified in the Company field at the top of the window.

The Selected Layout field specifies the layout that is currently used for a given report.

A report can be set up with more than one report layout, which you can then switch among as needed.

Depending on the layouts that are available for a report, you can choose to use a built-in RDLC layout, a built-in Word layout, or a custom layout.

From the Report Layout Selection page, it is also possible to manage custom layouts for reports.

In the list, locate the document report 1306 Sales – Invoice by doing one of the following:
Scroll down through the list.
Filter on the Report ID equal to 1306.
Select the row for report 1306.
Choose the down arrow in the Selected Layout field to show the options (RDLC, Word, and Custom).

First we will have a look at the new report 1306 Sales – Invoice, which has a built-in RDLC and Word layout. Out of the box, it does not have any custom layouts – we will add these in later posts.

As you can see, the RDLC (built-in) is typically the layout that selected by default. This can, however, be controlled by using the Default Layout property on the specific report object in Microsoft Dynamics NAV Development Environment.

Notice that the page also contains a Custom Layouts FactBox. This lists any available custom layouts for a selected report in the list. If there are no custom layouts for the report, then you will have to create one first.

In the row for report 1306, set the Selected Layout field to RDLC (built-in).
On the Home tab, in the Report group, choose Run Report.
In the resulting report request page, use default values, and then select the Print button and choose PDF.
Open and inspect the resulting PDF file for report, which is based on the RDLC layout.

We will now select and run the built-in RDLC layout for report 1306 Sales – Invoice from the Report Layout Selection list page.

In the Report Layout Selection page, select the line for report 1306, and then set the Selected Layout field to Word (built-in).
On the Home tab, in the Report group, choose Run Report.
In the resulting report request page, use default values, and the select the Print button and choose PDF. [Note: This might not work for all client setups because it relies on server-side PDF conversion. As an alternative, use the Preview option on the request page, which will result in a Word document.)
Open and inspect the resulting PDF file for the report, which is based on the Word layout

Finally, we will change the layout to the built-in Word layout and then run the report.

Report

Using Report Selector to run Report

July 31, 2015July 31, 2015 Ashwini Tripathi

The required steps to use the reports are shown:

Open the Report Selection – for example Sales page by doing one of the following:
In the Search box, enter Report Selection – Sales, and then choose the related link.
In the navigation pane, choose Departments, Administration, IT Administration, and then Reports.

In the Report Selection – Sales window, do the following:

Set the Usage field to Invoice.
In the Report ID field, replace 206 with 1306 (an New Mini Document Report.in 2015)
Choose the OK button.

Set up the report selection to run report 1306 instead of report 206 when printing invoices.

Report

Word Document Reports and Custom Layouts

July 30, 2015July 31, 2015 Ashwini Tripathi

Reporting functionality has been greatly improved in Microsoft Dynamics NAV 2015 by adding support for defining report layouts in Word, and enabling end users to create custom RLDC and Word layouts for their reports. These two features make it easier to create visually pleasing document reports, as well as allow end users to modify reports to their liking with as little partner involvement as desired.

Today I am sharing again all the posts from earlier archive, if you missed you can find all of them below.

Soon I will come up with more posts on this topic. Till then go through below links and develop your skills so that we can take up few more complex ones.

Report

Creating Custom Word Layout for Document Reports in Navision 2015

July 17, 2015 Ashwini Tripathi

Open the MS Office Word.

File -> New and type Invoice in search and press enter to list available Invoice Templates.

In my example I am using [Sales Invoice (Blue Border Design)]

Select Create to download the template and create a document for you.

Your new document created from this template will look as below:

Prepare the Layout by removing extralines as we will be using repeating lines for Lines.

Save your Template.

I have made a copy of Report 206 – Sales – Invoice on new ID say (50005 as Sales – Invoice Word).

This report dosen’t have Word Layout. We will be creating one Customize Layout form above saved Invoice above. [Sales Invoice (Blue Border Design)]

Open Report Layout as shown in below Screen:

Slect the Report and choose Custom Layout as shown in above screen.

In Custom Report Layout Window Click on New.

From window Insert Build-in Layout for Report Select Insert Window Layout and click OK button.

From Custom Report Layout window Select Import Layout, Select your word file prepared above and Respond Open to Import.

Then, press Edit Layout and prepare layout on Developer tab, find XML Mapping Pane and find your document in Custom XML Part.

Now, you can find all fields from Dataset.

Please see below links to complete your report:

Adding image fields

Add fields from a report dataset to a word report layout

Creating my first word list report in Navision 2015

How to specify the default built in report layout

Create a word report layout for a report

Once you are done with your field assignment close the word layout.

Select Yes to import changes to the report.

Select Run Report to view the output of your report.

Now you are done with your Custom Report Layout.

Please see sections which will be more helpful to complete your task:

Reports

Report

The New Report Scheduling feature for end users running reports in Microsoft Dynamics Navision 2015

July 12, 2015July 12, 2015 Ashwini Tripathi

The new Report Scheduling feature for end users running reports:

End user can schedule reports for later execution.
User can get to his next task quicker.
User has an inbox part for viewing the scheduled reports.

A. Setting up the job queue to run a report

My Customer needs to run a number of long running reports and does not want to wait for them to complete.

The scenario is simplified because a typical customer setup would involve setting up a NAS instance to process the reports.

* Open the Job Queues page.

* On the Home tab, in the New group, choose New to set up a new job queue

* In the Code field, type Reports.

* Fill in the Description field My Report Schedule.

* Leave the Job Queue Category Filter field blank.

* On the Home tab, in the Process group, choose Start Job Queue.

* Close the Job Queue Card

A job queue with no filter runs the reports.

In many customer installations, the reports will be picked up by the DEFAULT job queue

B. Adding the Report Inbox to the Role Center

My Customer wants easy access to the reports that he has scheduled, so he adds the Report Inbox part to the Role Center page.

* To open the Role Center page that you want to customize, in the navigation pane, choose the Home button, then choose the Role Center menu item.

* On the Application menu , choose Customize, and then Customize This Page.

* In the Available Parts pane, select Report Inbox, and then choose the Add button.

* To move the Report Inbox part to top of the second column in the Role Center layout pane, select it and then use the Move buttons.

* (Optional) To add the My Job Queue to the Role Center, in the Available Parts pane, select My Job Queue, and then choose the Add button.

* (Optional) To move the My Job Queue part below the Report Inbox in the Role Center layout pane, select it and then use the Move buttons.

* Verify that the Report Inbox appears in the Role Center.

C.Scheduling a report and viewing the result

* On the Role Center, on the Report tab, choose Customer Order – Summary.

* On the report request window, choose the Print button, and then choose Schedule

* In the Schedule a Report window that appears, in the Description field type a different text.

* In the Report Output Type field, select the down arrow, and choose PDF.

* Choose the OK button.

* Go to the Report Inbox on the Role Center and show that the report appears when it is finished running.

* To view the report, select it in the Report Inbox, and then choose Show.

D. When a scheduled report fails

* Open the Role Center.

* On the Application menu , choose Customize, and then Customize This Page.

* In the Available Parts pane, select My Job Queue, and then choose the Add button.

* To move the My Job Queue part below the Report Inbox in the Role Center layout pane, select it and then use the Move buttons

* In the Role Center, go to the My Job Queue part.

We have already done above step in above steps. Let’s continue with next step. If not already done follow the steps as defined above.

* On the Role Center, on the Reports tab, choose Price List.

* In the Sales Type field select Customer.

* To schedule the report to run later, choose the Print button, then select Schedule.

* In the Schedule a Report window, leave the default values in fields and choose the OK button.

* When the error shows up in the My Job Queue part, select the error, and then choose Show Error.

* Read the error message, and then choose the OK button to close it.

* Open the Job Queue Log Entries page.

* Filter the list to display entries whose Status is Error.

* To view the error message, point to the Error message field for the entry, or, select the entry, and then on the Home tab, choose Show Error Message.

* Return to the Role Center, and then run the Price List report again.

* On the report request window, set the Sales Type field to All Customers, choose Print, and then choose Schedule.

* In the Schedule a Report window, leave the default values in fields and choose the OK button.

* In the Report Inbox on the Role Center, view the completed report.

Report

Creating Word Mail Merge Report in Microsoft Dynamics Navision 2015

July 10, 2015July 10, 2015 Ashwini Tripathi

Today we will see Mail Merge feature in Word Report.

I would suggest to please go through my earlier post for creating simple List Report for better understanding as some parts I will be skipping in this post assuming you have already learned from my previous post.

Here is the Quick Link : [ Creating My First List Report in Navision 2015 ]

Lets assume ever month we require to send a Reminder Letter to our Customers for Due Payments.

Now we can design such report at customer ease.

Lets Start with creating a new Report.

Add the Data Items and desired columns as one shown below.

Add appropriate filters on Data Items, I have added [ Open = Yes, Document Type = Invoice, Remaining Amount > 0, Due Date <> ‘ ‘] you select your own as desired.

Set the Report Properties as shown in below screen.

Define Global Variables as shown in below screen.

We have Included WordMergeDataItem as Cust. Ledger Entry. This will set the table to loop for records.

Each records will be considered as New Page for the Report.

In this report we are using Include Caption which will include caption for each required columns from one defined in respective table.

Add this property for each required column.

Add the code to the Data Item Trigger to fetch Customer Information for each record.

Insert New Blank Word Layout to the Report.

Export the Layout Template and Save.

Open saved Layout Template in Word.

In below screen each component is shown, refer to earlier post Creating List Word Report, Link provided above for better understanding.

In this report Label is included as we have set property for each column to include caption.

Design the sample Letter as one shown below.

I have highlighted all the Dataset Columns/Fields in yellow.

How to add fields please refer to earlier post Creating Word List Report, Link provided above.

Now Save the Template and Import into the Report.

Here is the out put from the report as below.

Each page will contain data as per in Customer Ledger Entry Due Payment Records.

We are done !!!

You can use formatting feature of Word to add more better look to your Report as desired.

Stay tuned for more in upcoming posts.

Don’t forget to follow the Blog Site for future posts.

Report

Creating my first Word List Report in Navision 2015

July 9, 2015July 9, 2015 Ashwini Tripathi

Today we will learn creating Word List Report in Navision 2015.

Prepare the Dataset for Report as below, to keep it simple I am creating List report for Item Inventory.

I am adding one additional field for Company Logo from Company information.

Make sure you add calcfields code for Picture from Company information.

—————————————————

I have added below piece of code in [ Item – OnPreDataItem() ]

CompInf.GET;
CompInf.CALCFIELDS(Picture);

—————————————————

Place the cursor on Blank Line and open Report Properties and add DefaultLayout Property.

Next we will Add a Blank Word Layout to the Report, Step as below.

Confirm the action by responding OK to the Dialog Box.

Now Export this Blank Template

and save as docx file (MS Word File)

Open the Exported Template in Word.

Click on DEVELOPER Menu.

From Ribbon click on XML Mapping Pane.

You will now be able to see XML Mapping Pan which contains your Dataset for the Report.

Click to the Dropdown List and select entry containing your Report Name & ID.

Now you will be able to access your Dataset with Table & Fields details. As shown in below screen.

Place the cursor on top (or wherever desired) of the document to add Logo to the Report.

Select the Picture Filed from the Dataset -> Right Click -> Insert Content Control -> Picture.

See below screen for details:

You will see the Place holder for the Picture.

Now add Table with 2 Rows ( 1 for Header & 1 for Data) and 6 columns.

We have six columns in our dataset excluding Logo.

Add your Header for the table, We are using fixed Text Heading as of now but you can add captions from your dataset.

In current report I have not included we can check same in some other report posts.

Select Entire 2nd Row and Add Repeating Content Control for Item (Table).

As this will show records from the table Item we included in our Dataset.

Now in each column add your columns from Dataset as shown below.

Place your cursor in each column and choose Insert Content Control -> Plain Text.

Now our Report is almost ready.

May be the Report have data which may continue on several pages, in this case our Heading Row must Repeat on every new Page. To do this we will Select the entire 1st Row -> Right Click -> Table Properties.

Go to Row Tab of Property window and put a checkmark on option “Repeat as header row at the top of each page”

Save your Word Template an Import back in your report as shown below.

Now Save the Report and Execute it.

Here is the Output below of the Report.

We are done !!!

You can use formatting feature of Word to add more better look to your Report as desired.

Stay tuned for more in upcoming posts.

Don’t forget to follow the Blog Site for future posts.

Rate this:

Share this with:

Creating the Word Template for Use by Automation

Creating the Codeunit and Declaring the Variables

To create the codeunit

To declare the variables

Writing the C/AL Code

To write the C/AL code

Transferring Data to Word

To transfer data to Word

To-Do List

To call the codeunit from the Customer card page in the Microsoft Dynamics NAV Windows client

Next Steps

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with:

Rate this:

Share this with: