the – Page 2 – ARKSD Consultancy Private Limited

Development Tips, Excel, How To, Jet Reports, Office Integration, Report

How to Use the Jet Report Scheduler

September 27, 2015September 27, 2015 Ashwini Tripathi

Overview

The Jet Scheduler is a powerful tool that allows users to schedule reports to be automatically run by the Windows Task Scheduler. The user can also control where the output file is saved to, if the report should be emailed once it has been generated, and the output format of the report.

Creating a New Scheduled Task

To set up a scheduled report, open the report in Excel, click the Jet ribbon, and click the Schedule button.

Click the New Task… button to schedule a new task.

The Scheduled Task window will now appear.

Reports Tab

The Reports tab contains general information about the scheduled task.

Task Name: This represents the name of the task as it will appear in the Scheduled Task window to the user.
Run All Reports in a Folder: This button will enable the user to schedule all Jet Reports in a full to be run
Run a Single Report: This button will enable the user to schedule a single Jet Report to be run
Input: This represents the folder and file name of the report that will be run
Output: This represents the folder and file name of where the finished report will be saved

Schedule Tab

This tab will define the frequency of how often the report will be run as well as if the task is currently disabled and if the report should be run when the user is logged off.

The available options for the frequency are:

Once: The report will only be run one time
Daily: The report will be run every day (it is possible to set on the next tab the number of days to wait between runs)
Weekly: The report will be run every week (it is possible to set on the next tab the days of the week for the report to be run on)
Monthly: The report will be run every month (it is possible to set on the next tab the months for the report to run on and the days of the month for the report to be run on)
When Idle: The report will run every time that the computer goes into idle mode
At Startup: The report will be run each time that the computer is turned on
At Logon: The report will be run each time that the user logs on to the computer

Frequency Tab

The name of this tab will change depending on the frequency specified on the Schedule tab.

In the screenshot above a Weekly frequency has been specified.

Start Date and Time: This represents the first date that the report will run and the time that it will run for this and subsequent schedules
Weeks between report runs: This specifies how many weeks the Scheduler will wait between report runs before running the report again
Days to run report: This represents the days of the week that the report is scheduled to run on

Email Tab

The Email tab allows the user to define who the report will be sent to if emailing is desired.

Send email: If this box is checked it will enable the report to be emailed to recipients
Mail properties: This dropdown will allow the user to specify whether the report will be emailed using Outlook or a generic SMTP protocol. SMTP must be configured in the Jet Essentials Application Settings in order for it to be used.
Message: This allows the user to specify a custom subject or body to be sent as part of the email
Attach report to email: If this box is checked the report will be attached to the email. If the box is unchecked the report will not be attached. This can be used as a type of notification when used in conjunction with the subject and body of the email to allow a user to know that the report has been run
Recipients: Email addresses will be specified here for all recipients of the report. Email addresses should be separated by a semi-colon.
Get recipients from Excel Named Range: It is possible to also define the email addresses in the report itself and then assign an Excel named range to the cell(s). If there are named ranges in the report then they will appear in the dropdown below the checkbox. The Output tab allows the user to define how the file should be saved once it is finished running. This tab also enabled the user to turn on logging to troubleshoot errors with the Scheduler process as well as use Batch File Generation for the reports.

Output Tab
The Output tab allows the user to define how the file should be saved once it is finished running. This tab also enabled the user to turn on logging to troubleshoot errors with the Scheduler process as well as use Batch File Generation for the reports.

Output Format: This dropdown list will allow the user to define the format of the finished report. The available options are:
- Jet Workbook: This will save the report as a normal Excel file with all Jet Reports functions still in the report
- Values Only Workbook: This will save the report as an Excel file with all Jet Reports functions removed. The recipient would not be able to refresh the report as it will be a static Excel file
- Web Page: This will save the report as a HTML file with a single sheet. This should be used when there is a single sheet in the report
- Web Page by Sheet: This will save the report as a HTML file with multiple sheets embedded in it. This should be used when there are multiple sheets in a report
- PDF: This will save the report as a PDF file (if your version of Excel supports that ability – Excel 2007 and higher)

Remain tuned for more details, I will come up with more details and features in my upcoming posts.

Development Tips, Jet Reports, Office Integration, Report

Customize the Report layout using Jet Express for Word

September 5, 2015September 5, 2015 Ashwini Tripathi

Click Edit Layout to customize this layout

The Jet Express for Word action panel may automatically open when this word file is opened.

If not, select the Jet Ribbon

Click on the Design icon

This presents a tree hierarchy of the fields from the selected report.

Click on the icons on the hierarchy to expand or contract the lists.

The Search field makes it easy to find the fields you need.

Labels – NAV reports which were created by Jet Reports include a section for Labels. These fields are the descriptions of the fields themselves.

If you have multiple languages in your NAV instance, these labels can be helpful for creating multi-language documents.

Other Groupings – NAV reports which were created by Jet Reports include sections for Company, Customer, etc. These fields include the values of the fields.

The user can select from fields including Header or Lines in the hierarchy and insert an item, typically into a table in the Word document.

To add a field select the desired location in the Word document and double-click on the field.

Alternatively, you can select the field and click on the Insert button in the Jet Express for Word action panel.

To Add a picture to a document, right-click on the item and select Insert Picture

The Lines fields are also inserted as text and can be replicated by a Right-click, and choosing “Insert Repeater”.

Repeating items (such as Sales lines on a Sales order) can be added to a document.

Best practice for this is to create a table in Word that is two rows high.

The first row contains the Labels of the fields that you wish to include.

The second row contains the values of the fields.

Select the entire second row- include the area just to the right of the last column.

Right-click on the item in the hierarchy that you want to repeat and select Insert

To delete an item, click on it, and then click on its Title.

When you click on the Title it will change color.

Then press the Delete Button on your keyboard.

Note: It’s important to select the Title when deleting to avoid any leftover data.

When you have completed your changes Save your Word document

This will return you to NAV and prompt you to import the report layout changes.

Click Yes

You can Run Report to review your changes.

Here is the output for the report below.

We are done with customization of the report using Jet Reports.

I will come up with more details in my upcoming posts.

How To, Instalation & Configuration

How to: Test the ADCS Connection

August 30, 2015 Ashwini Tripathi

The following procedure demonstrates how to use Windows Services and Telnet to test your Automated Data Capture System connection.

Before you can begin, you must install the ADCS component as part of a customized installation of Microsoft Dynamics NAV. For more information, see my earlier post Installing ADCS

To configure and start the Microsoft Dynamics NAV VT100 Plugin service

From the Windows Start menu, in the Start Search box, enter services.msc, and then press the Enter key. The Services window opens.

Right-click the Microsoft Dynamics NAV VT100Plugin service, and then choose Properties.

To specify the user account that the service can use to log on, choose the Log On tab.

To specify an account, choose This account, choose Browse, and then specify a user account in the Select User dialog box. When you are finished, choose the OK button. The format is as follows:

Note: To complete testing, you must also add the same user to Microsoft Dynamics NAV, with an appropriate permission set.

Type the password for the user account in Password and in Confirm password, and then choose the OK button.

On the General tab, choose Start.

To verify that the service is running

Start the Windows Event Viewer. From the Windows Start menu, in the Start Search box, enter eventvwr.msc, and then press the Enter key.

In the Windows Logs console tree, choose Application, and then locate event information for the Microsoft Dynamics NAV VT100 Plugin.

Testing Your Connection

In the following procedure, you will open a terminal connection and use HyperTerminal to simulate using handheld devices. HyperTerminal is a program that you can use to connect to other computers, Telnet sites, bulletin board systems (BBSs), online services, and host computers. HyperTerminal connections are made using a modem, a null modem cable (used to emulate modem communication), or an Ethernet connection.

Note: HyperTerminal is no longer included with Windows. You can usually find a free downloadable version on the Internet.

To test your ADCS connection

Start your HyperTerminal application.

Enter a name for the connection. Choose the OK button.

In the Connect Using list, select TCP/IP (Winsock).

The following table describes the details of a local installation on one machine.

Detail	Description	Example
Host Address	IP address of the machine that is running the VT100 Plug-in service.	localhost
Port Number	Port number that the VT100 Plug-in is listening on.	6666

Choose the OK button. The HyperTerminal window opens.

Enter a user ID and password. Press Enter after every step.

Select a warehouse activity option. For example, enter 1 in the Choose field if you want to simulate working with pick documents.

You can review the data about warehouse picks in Microsoft Dynamics NAV. For example, you can use the data in the CRONUS International Ltd. demonstration database to send information from and to Microsoft Dynamics NAV.

Note: To see the interaction between Microsoft Dynamics NAV and ADCS, in CRONUS International Ltd., you can use warehouse activities that are located in the White warehouse. It is preconfigured to use ADCS.

Development Tips, How To

How to: Set .NET Framework Types to Run on the Microsoft Dynamics NAV Windows Client or Microsoft Dynamics NAV Server Computers

August 28, 2015August 28, 2015 Ashwini Tripathi

Here is the brief description for how to set .NET Framework objects that are instantiated by DotNet variables to target either the Microsoft Dynamics NAV Windows client or Microsoft Dynamics NAV Server.

By default, a type is set to target Microsoft Dynamics NAV Server. However, you can set the type to target the Microsoft Dynamics NAV Windows client.

Setting the .NET Framework Type to Target the Microsoft Dynamics NAV Windows Client

To set a .NET Framework type instance to target the Microsoft Dynamics NAV Windows client, you must complete the following tasks:

Set the DotNet variable for the .NET Framework type to target the Microsoft Dynamics NAV Windows client.
Copy the .NET Framework assembly to the computer that is running the Microsoft Dynamics NAV Windows client.

You must copy the assembly to each computer that is running the Microsoft Dynamics NAV Windows client. If the assembly is included in the global assembly cache, then you do not have to do this task.

For the Microsoft Dynamics NAV Web client, you cannot implement Microsoft .NET Framework interoperability objects that target the client.

To set the .NET Framework type to target the Microsoft Dynamics NAV Windows client

In Object Designer, open the C/AL code of the Microsoft Dynamics NAV object that uses .NET Framework interoperability.
Do one of the following steps:
- For a global variable, on the View menu, choose C/AL Globals.
- For a local variable, select the trigger that uses the variable, and then on the View menu, choose C/AL Locals.

On the Variables tab, select the DotNet variable, and then on the View menu, choose Properties.
In the Properties window, set the RunOnClient property to Yes

To copy a .NET Framework assembly to a computer that is running the Microsoft Dynamics NAV Windows client

On the computer that is running the Microsoft Dynamics NAV Windows client, copy the.NET Framework assembly to the Add-ins folder of the Microsoft Dynamics NAV Windows client installation.

By default, the path of this folder is C:\Program Files\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins or C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins.

You can also put assemblies in a subfolder of the Add-ins folder. This can be useful when you have multiple versions of the same assemblies and dependencies.

Setting the .NET Framework Type to Target Microsoft Dynamics NAV Server

To set a .NET Framework type instance to target Microsoft Dynamics NAV Server, you must complete the following tasks:

Set the variable for the NET Framework type to target Microsoft Dynamics NAV Server.
Copy the .NET Framework assembly to the computer that is running Microsoft Dynamics NAV Server.

If the assembly is included in the global assembly cache of the computer that is running Microsoft Dynamics NAV Server, then you do not have to do this task.

To set the .NET Framework type to target Microsoft Dynamics NAV Server

In the development environment, in Object Designer, open the object that uses the .NET Framework variable.
Do one of the following steps:
- For a global variable, on the View menu, choose C/AL Globals.
- For a local variable, in the C/AL Editor, select the trigger that uses the DotNet variable, and then on the View menu, choose C/AL Locals.

On the Variables tab, select the NET Framework variable type, and then on the View menu, choose Properties.
In the Properties window, set the RunOnClient property to No

To copy a .NET Framework assembly to the computer that is running Microsoft Dynamics NAV Server

On the computer that is running Microsoft Dynamics NAV Server, copy the.NET Framework assembly to the Add-ins folder of the Microsoft Dynamics NAV Server installation folder.

By default, the path of the Microsoft Dynamics NAV Server installation folder is C:\Program Files\Microsoft Dynamics NAV\80\Service\Add-ins.

You can also put assemblies in a subfolder of the Add-ins folder. This can be useful when you have multiple assemblies and dependencies.

If you are working in the development environment, to compile and test Microsoft Dynamics NAV objects, you must also copy the assembly to the Add-ins folder of the Microsoft Dynamics NAV Windows client installation on the computer that is running the development environment. By default, the path of this folder is C:\Program Files\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins or C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client\Add-ins.

Cues, Query

Using Query Object to Calculate the Cue Data

August 19, 2015August 19, 2015 Ashwini Tripathi

Today we will learn to create a query to update Cue data.

Creating a Query for Calculating the Cue Data
First, we will create a query object to calculate the number of open sales invoices from table 21 Cust. Ledger Entry.
Create a query for calculating the Cue data as below:

Save the query.

Adding the Table Field for the Cue Data
Next we will add a field to the table Sales Invoice Cue (create new table) for holding the Cue data.

We will add a global function that returns the total amount of sales invoices for the current month from the query object that we created above procedure.

To add C/AL code to the table calculate the Cue data
Add a global function that is called CalcSalesThisMonthAmount as follows:

On the View menu, choose C/AL Globals.

On the Functions tab, in the Name column, enter CalcSalesThisMonthAmount.

Select the new function, and then in the View menu, select Properties.

Set the Local property to No.

In the C/AL Globals window, select the new function, and then choose Locals.

On the Return Value tab, set Name field to Amount and the Return Type field to Decimal.

On the Variables tab, add two variables as shown in the following table:

Name	DataType	Subtype
CustLedgerEntry	Record	Cust. Ledger Entry
CustLedgerEntrySales	Query	Cust. Ledg. Entry Sales

In C/AL code, add the following code on the CalcSalesThisMonthAmount function:

CustLedgerEntrySales.SETRANGE(Document_Type,CustLedgerEntry.”Document Type”::Invoice);

CustLedgerEntrySales.SETRANGE(Posting_Date,CALCDATE(‘<-CM>’,WORKDATE),WORKDATE);

CustLedgerEntrySales.OPEN;

IF CustLedgerEntrySales.READ THEN

Amount := CustLedgerEntrySales.Sum_Sales_LCY;

Save the table.

Adding the Cue to the Role Center Page
To display the Cue fields on the Role Center, We will create a new Page [PageType = CardPart] name Sales Invoice Cue.

Page Designer should look similar to shown above illustration.

Open the C/AL code for the page, and then add the following code to the OnAfterGetRecord Trigger to assign the Sales This month field to the CalcSalesThisMonthAmount function of table Sales Invoice Cue:

“Sales This Month” := CalcSalesThisMonthAmount;

Also add code to the OnOpenPage Trigger.

RESET;
IF NOT GET THEN BEGIN
INIT;
INSERT;
END;

Formatting the Cue Data
We does not want to display any decimal places. To achieve this, we set the AutoFormatType Property and AutoFormatExpr Property of the Cue field on the page. As shown above.

To change the data format

In the Properties window, set the AutoFormatType property to 10.

This enables you to create a custom data format.

Set the AutoFormatExpr property to the following text.

‘<precision,0:0><standard format,0>’

<precision,0:0> specifies not to display any decimals places.

<standard format,0> specifies to format the data according to standard format 0.

Close the Properties windows, and then save and compile the page.

Run the Page created above the output should be similar to as below:

Now you can add this CardPart Page to any of your Role Centre Page.

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: