Template-based Excel Reports functionality in NAV 2013 R2 RU

Basic objects of Excel Reports functionality in RU localization of NAV 2013 R2:

Type	ID	Name	Description
Table	14919	Excel Template	Table containing templates. Fields: Code, Description, File Name, BLOB, File Extension
Table	14930	Excel Template Sheet	Table containing Excel template sheets. Fields: Template Code, Name, Paper Height
Table	14931	Excel Template Section	Sheet sections. Fields: Template Code, Sheet Name, Name, Height
Table	14932	Excel Template Sheet Buffer
Codeunit	14930	Excel Report Builder Manager	Codeunit, which creates output Excel printing form based on specific template using DotNet component Microsoft.Dynamics.NAV.RU. ExcelReportBuilder.ReportBuilder
Page	14919	Excel Templates	Main templates managing page

Interaction of objects:

Page for working with templates:

Example of functionality - 12411 Order Factura-Invoice (A)

Objects (except basic stated above):

Type	ID	Name	Description
Report	12411	Order Factura-Invoice (A)	Invoice Report
Codeunit	14931	Factura-Invoice Report Helper	Codeunit, which contains the functionality of the report building (General functionality for several near-functional reports)

When you import a template related data tables Excel Template Sheet, Excel Template Section are populated automatically. On the basis of the data in these tables in the CU Factura-Invoice Report Helper adds sections through the ExcelReportBuilderMgr. AddSection and populating the data section by function ExcelReportBuilderMgr. AddDataToSection. Functionality of building reports examines height of sections and automatically adds new pages when it’s needed.

For Example:

All the sections must be named:

Dynamic lines of document are populated in the same way:

You can find further information in MS Dynamics NAV documentation or by examining functionality in RU demo database. I’ll be happy to answer any questions regarding this functionality. Don’t hesitate to ask!

Unlimited size dynamic Matrix Page in NAV 2009 & Enhanced "Account Schedule Overview"

As NAV 2009 doesn't have Matrix Page type many tasks that required usage of such forms in Classic interface became less user-friendly and more complicated. Since lot of customers prefer to use Matrix Forms in the same way they have been working with Matrix Forms I've decided to make a little research on possibility to create C# Add-in that would provide same functionality. Here's the result:



This solution has following advantages:

• Unlimited number of rows and columns which can be added dynamically (for example while scrolling)
• Account Schedule Overview page has more user-friendly design (everything is on one page, less actions for the same result)
• Ability to hide pane with settings
• Better layout of columns (autosize)
• Possibility to adjust appearance according to customer's need with wide tool set of .NET

Going to technical side

Basically enhanced Account Schedule Overview Page Consist of following controls:

Where Matrix_BigText is a Add-in field with data type of BigText (global variable):

Core function on the page is MakeXML() which builds XML structure of data to display and writes it to BigInterger variable of Add-in control so the Value (XML) is passed to processing methods which displays dynamics matrix. When MakeXML builds it's resulting XML all the Options on the page are processed in the same way as on standard Account Schedule Overview page. 

Add-in has following structure: it waits for changes in XML (which is passed through BigText) and rebuilds Matrix according to new data:

You can find demo of the functionality here. Please don't hesitate to ask any questions you have. Feedback is welcome too :)

Timeouts on Windows Shell Commands

The common problem of using external apps through running by Windows Script Host Shell like: WshShell.RUN('ping microsoft.com',TRUE,TRUE) is that the code performing is stopps if the app malfunction and doesn't finish which causes freeze in NAV. The following example shows how to use defined timeout to prevent freezes while running any external apps or scripts by Windows Shell:

Instead of code:
WshShell.Run(Commandline,WindowStyle,WaitOnReturn);
use custom function:
ExecCommand(Commandline,Setup."Processing Timeout");

Function's signature:

PROCEDURE ExecCommand(CommandString : Text[1024];Timeout : BigInteger) : Boolean

VAR
      WshShell : Automation 'Windows Script Host Object Model'.WshShell;
      WshExec : Automation 'Windows Script Host Object Model'.WshExec;
      TimeStep : Integer;
      WindowStyle : Integer;
      ElapsedTime : Integer;
      WaitOnReturn : Boolean;
      ExecTimedOut : Boolean;

CODE:

CREATE(WshShell);
TimeStep := 1000;
WindowStyle := 0;
WaitOnReturn := FALSE;
WshExec := WshShell.Exec(CommandString);
WHILE (FORMAT(WshExec.Status) = '0') AND ((ElapsedTime <= Timeout) OR (Timeout = 0)) DO BEGIN
  SLEEP(TimeStep);
  ElapsedTime += TimeStep;
END;
ExecTimedOut := FORMAT(WshExec.Status) = '0';
IF ExecTimedOut THEN
  WshShell.Run(STRSUBSTNO('taskkill /PID %1',WshExec.ProcessID),WindowStyle,WaitOnReturn);
       
IF ExecTimedOut THEN BEGIN
  // Here logs can be created
END;
EXIT(NOT ExecTimedOut);

Printing documents on user's behalf from remote machine

In this post I will describe real requirements case of our customer (pharmaceutical company).

Generally the requirement was to improve printing performance by changing the way documents were printed through running print from remote Print Server. In previous configuration printing application was ran on RDP server where Role Tailored Client was installed, then the app sent a PDF document of medical specifications from Print Server remote machine (pretty large size) to user's default printer. To improve performance there could be an option to create a job queue and run NAS and Print Server which would send documents from queue directly to printer, but the following limitation made unsuitable such a nice option: the printer device had sophisticated security system which collected sent documents by all users to a queue, and only after user came to it and used an electronic security badge to perform authorization all the documents sent on user's behalf were printed. So after some research the following solution was applied: printing utility was ran on Printing Server instead of RDP server by using Windows Powershell Command:

where in argument list print device name and path to required document were passed.

So in the case it's absolutely required to run remote printing process on behalf of the user this solution can be applied due to it's simplicity and quite good performance and security.

Error while interacting with Excel "Old format or invalid type library"

Error while interacting with Excel "Old format or invalid type library"

While using Microsoft Office Excel interaction in NAV 2013 you may get the following error message if Language in Excel options doesn't match regional settings on the machine where NAV is running:



Error text:
Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))

The simplest way to fix the problem is to set reginal setting and language in Excel to equal values.