“How to” print reports to PDF and send email


“How to” print reports to PDF and send email

Hi guys, another post about my “How to” posts series.

In this case, I want to talk about this topic: “How to” print reports to PDF and send mail; we can have some methods to achieve this goal, with NAV 2017, NAV 2016 and with old NAV releases.

First Scenario: “How to” print reports to PDF

Schedule a report to print to PDF Format

1) “HOW TO” PRINT REPORTS TO PDF FORMAT

WITH NAV 2016 and mailing & sending documents process

“In NAV 2016 managing mailing & sending documents process has been improved.
Compared to the NAV 2015 version, you can send documents to printer, via email and via EDI in a single operation; you can set a default “document sending template” for each customer.
For this purpose new NAV objects have been introduced.”

Look at my old post here: https://robertostefanettinavblog.com/2016/05/30/nav-2016-documents-sending-objects-features/

WITH NAV 2017 and NST Reports Parameters

With NAV 2017, we can use NST Reports Parameters to activate “SAVEAS” function to produce reports in PDF format, we can schedule a report with PDF Output using SAVEAS function in report scheduling.

To achieve this gold you need only to:

Activate SAVEAS function in NST report section

Schedule a report in task scheduler

Configure the Output Layout for report

Example:

Look at my old post here NAV 2017 Task Scheduler https://robertostefanettinavblog.com/2016/11/04/nav-2017-task-scheduler/

and look at section of post: “JOB QUEUE” IMPROVEMENTS

NST Reporting Parameters (nice for activate “SAVEAS” function for reporting scheduling)

Sample of Report Scheduling

WITH OLD NAV RELEASES

..but for older releases of NAV we need to produce a PDF Output using SAVEAS function after report printing, we haven’t report parameter optionrequest page available in older releases.

To achieve this gold you need only to:

Activate SAVEAS function in report

Schedule a report in task scheduler

NAV 2013 SAMPLE REPORT TO CREATE PDF IN BATCH MODE (in this case using report 206 Sales Posted Invoice)

Scenario

Generate PDF report with SAVEAS feature

Schedule this report on Job queue

SAVEASPDF Function (Report)
[Ok :=] REPORT.SAVEASPDF(Number, FileName[, Record]) OR

[Ok :=] ReportVariable.SAVEASPDF(FileName)

“When you call SAVEASPDF, the report is generated and saved to “FileName.” A Saving to PDF window shows the status of the process. Note that the request page will not be shown.

The FileName parameter specifies a location on the computer that is running Microsoft Dynamics NAV Server. “

Source MSDN: https://msdn.microsoft.com/en-us/library/dd301166(v=nav.70).aspx

Example

Filename := ‘C:MyReportsreport206Demo.pdf’;

ReturnValue := Report.SAVEASPDF(206, Filename, record);

Sample of PDF generator batch

Codeunit CreatePDFFromPostedInvoices()

CreatePDFFromPostedInvoices()

// READING ALL POSTED INVOICES TO PRINT PDF REPORTS

SalesInvoiceHeader.RESET;

SalesInvoiceHeader.SETRANGE(“PDF Generated”, FALSE);

IF SalesInvoiceHeader.FINDSET THEN BEGIN

REPEAT

Filename:= SalesInvoiceHeader.”No.” + ‘.PDF’;

Report.SAVEASPDF(206, Filename, SalesInvoiceHeader); //PDF CREATED BY SAVEAS FUNCTION

UNTIL SalesInvoiceHeader.NEXT = 0;

END;

2) “HOW TO” EMAILING PDF REPORTS

Second Scenario: “How to” emailing PDF reports

Ø Send generated PDF reports via email

Sendmail with NAV SMTP Sendmail Codeunit (NAV .NET integration)

To send email from NAV (OnDemand or Scheduled), you can use standard NAV SMTP Codeunit: Codeunit 400 SMTP Mail.

This codeunit use one .NET object with some functions

Mail DotNet Microsoft.Dynamics.Nav.SMTP.SmtpMessage.’Microsoft.Dynamics.Nav.SMTP’

Functions: CreateMessage, TrySend, Send

Example SMTP SENDMAIL TEST

Subject := ‘SUBJECT SAMPLE TEXT’ ;

Body := ‘BODY SAMPLETEXT’;

SMTP.CreateMessage(‘SENDER’, ‘info@Cronus.it’, ‘r.s@xxxxx.it’, Subject, Body, TRUE);

Body := Body + ‘ OTHER’;

SMTP.AppendBody(Body);

SMTP.Send;

MESSAGE(‘Mail Sended’);

Sendmail with external .NET Integration Solution

Some time ago I published a project solution on MSDN TechNet.

Solution: “Send e-mail with attachment and templates – My Script on MSDN Gallery,a short C# script to send e-mail with attachment and template, DLL .NET usable on Windows Systems.

Function: SetMail(string parfrom, string parto, string parcc, string parsubject, string partextmessage, string parpathattach, stringparpathtemplate)

https://robertostefanettinavblog.com/2015/07/13/send-e-mail-with-attachment-and-templates-my-script-on-msnd-gallery/

https://gallery.technet.microsoft.com/scriptcenter/0c32d24a-3651-4fe1-8d10-80f9c6440a70

MY NEW SENDMAIL 2.0 PROJECT WITH WS INTEGRATION

Sendmail 2.0 C# Project

New Features:

Complete package solution (PRINT REPORTS TO PDF+SEND EMAIL INTEGRATION)

NAV Web Services integration

Best mailing errors check and logging.

SOON AVAILABLE: look at MSDN Scripting gallery section or on my GitHUb page soon.

Advertisements

“ALL ABOUT” Kill (In)active Sessions when “no more licenses”


Sometimes it happens that there are “no more licenses available” for Dynamics NAV, from NAV 2013 R2 and next releases, it’s necessary to run the Windows Client or to use Powershell statements to kill some sessions and unlock others.
If you are unable to access to NAV (“no more licenses”), you should need to use SQL Console and kill sessions from there (reading from Active Sessions Table of from sysprocesses View on master db..)
“Some user don’t want to use SQL Server to achieve this !” …
WARNING : “No more licenses !!”   … Immediately Buy Others !!!
license1.png
There are many ways to kill sessions, i list below some methods.
With NAV Server Login
– C/AL
– Powershell
Without NAV Server Login
– SQL Server Stored Procedures\T-Sql
– VB .NET Application
Below some methods to kill the sessions even in the case of “no more licenses available“: SQL Script and VB.NET
C/AL Statement to Kill Sessions
The Standard Statement for C/AL Code is:  STOPSESSION(“Session ID”);
Example:
IF CONFIRM (‘Kill Session ?’) THEN STOPSESSION(“Session ID”) … GOAL !
…. BUT YOU NEED TO CONNECT TO NAV SERVER !  .. sometimes is NOT possible!
———————————————————-
My VB.NET Solution
“I wrote a simple solution on VB.NET to Kill Active Sessions using a SQL Connections,
you cand find it on GITHUB and TECHNET.”
ON GITHUB
ON TECHNET
Post link
Download link
About My VB.NET Simple Solution, some pictures
kill1
kill-2
kill-3
App Config File
<?xml version=”1.0″ encoding=”utf-8″ ?>
<configuration>
<configSections>
</configSections>
<connectionStrings>
<add name=”NAV_2017_KIll_SQL_Inactive_Sessions.My.MySettings.Demo_Database_NAV__10_0_ConnectionString”
connectionString=”Data Source=STEFANETTILEN\SQL2012ENT;Initial Catalog=&quot;Demo Database NAV (10-0)&quot;;Integrated Security=True”
providerName=”System.Data.SqlClient” />
</connectionStrings>
<startup>
<supportedRuntime version=”v4.0″ sku=”.NETFramework,Version=v4.5″ />
</startup>
</configuration>
———————————————————-
SQL KILL STATEMENTS
KILL (Transact-SQL)
– Syntax for SQL Server, Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse

KILL ‘session_id’ [;]    
Source https://msdn.microsoft.com/it-it/library/ms173730.aspx

KEEP IN MIND !!!   DON’T KILL PROCESSES IN THIS STATES:
-AWAITING COMMAND
-CHECKPOINT SLEEP
-LAZY WRITER
-LOCK MONITOR
-SIGNAL HANDLER
My Customized release
1) SP_SQL_KillNavServerSession
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: KILL ALL NAV ACTIVE USER SESSSIONS WITH MULTIPLE INSTANCES
— =============================================
CREATE PROCEDURE [dbo].[SP_SQL_KillNavServerSession]
AS
BEGIN
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
 SET @RowIndex = 1;
SELECT @MaxIndex =
(SELECT top 1 MIN([Session ID])
FROM [ITA].[dbo].[Active Session]
WHERE [User ID] = ‘************’
group by [Session ID])
 — Remove Active Session
DELETE FROM [DB].[dbo].[Active Session] where [Session ID]>@MaxIndex AND [User ID] = ‘*********’
END
2) SP_SQL_KillNONavSession
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Description: KILL SLEEPING NO NAV SESSIONS
— =============================================
CREATE PROCEDURE [dbo].[SP_SQL_KillNoNavSession]
AS
BEGIN
DECLARE @Session TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[sid] int,
ltime DATETIME,
hname varchar(MAX),
uid varchar(100),
lret datetime,
etime int
);
DECLARE @NotAffectedUserList TABLE
(
id INT IDENTITY(1,1) PRIMARY KEY,
[uid] varchar(MAX)
);
DECLARE @session_time as int;
DECLARE @vSID as int;
DECLARE @command as varchar(MAX);
DECLARE @RowIndex AS INT;
DECLARE @MaxIndex AS INT;
set @session_time=15; — Assign Session time in Minutes
— Assign List of Users who “will not be affected” by this Session Kill
INSERT into @NotAffectedUserList SELECT ‘sa’
INSERT into @NotAffectedUserList SELECT ‘NT AUTHORITY\SERVIZIO DI RETE’
INSERT into @NotAffectedUserList SELECT ‘**************’
INSERT into @NotAffectedUserList SELECT ‘scheduler’
INSERT into @Session
SELECT
session_id as [Session ID],
login_time as [Login Time],
host_name as [Host Name],
LOGIN_NAME as [Login ID],
last_request_end_time as [Last Request End Time],
DATEDIFF(minute,  last_request_end_time,getdate()) as[Elapsed Time (minutes)]
FROM
sys.dm_exec_sessions
WHERE
open_transaction_count = 0
AND host_name <> ‘NAVSRV’ COLLATE Latin1_General_CI_AS
AND host_name is not NULL
AND DATEDIFF(minute,  last_request_end_time,getdate())>=@session_time
–AND client_version=4
AND login_name COLLATE Latin1_General_CI_AS not in
(
SELECT DISTINCT uid
FROM @NotAffectedUserList
)
SET @RowIndex = 1;
SELECT @MaxIndex = MAX(id) FROM @Session;
WHILE( @RowIndex <= @MaxIndex)
BEGIN
SELECT @vSID = sid
FROM @Session
WHERE id = @RowIndex;
 — String together the KILL statement
SELECT @command = ‘KILL ‘ + CAST(@vSID AS varchar(5))
 BEGIN TRY
  — Execute the final string to KILL the spids
EXEC (@command)
END TRY
BEGIN CATCH
declare @emsg varchar(500);
SELECT
@emsg=ERROR_MESSAGE();
  print @emsg;
END CATCH
 SET @RowIndex = @RowIndex + 1;
END;
END
// KILL SQL INACTIVE SESSIONS FOR NAV APPLICATIONS
// ONLY FOR SLEEPING SESSIONS – OLD NAV CLIENTS
a very Simple SQL Stored Procedure to ‘kill’ sql inactive sessions
idle (sleeping) for 10 minutes related to “Nav Client” applications
*** it’s necessary to identify correct “application name” ***
*** ex: program_name LIKE N’%NAV client% *** or %NAV Web%
DECLARE @v_spid INT
DECLARE c_Users CURSOR
FAST_FORWARD FOR
SELECT SPID
FROM master..sysprocesses (NOLOCK)
WHERE spid>50
AND loginame <> ‘DIR%’
AND program_name LIKE N’%NAV client%’
AND status=’sleeping’
AND DATEDIFF(mi,last_batch,GETDATE())>=10
AND spid <> @@spid
OPEN c_Users
FETCH NEXT FROM c_Users INTO @v_spid
WHILE (@@FETCH_STATUS=0)
BEGIN
PRINT ‘Killing ‘+ CONVERT(VARCHAR,@v_spid)+’…’
EXEC(‘KILL ‘+ @v_spid)
FETCH NEXT FROM c_Users INTO @v_spid
END
CLOSE c_Users
DEALLOCATE c_Users
———————————————————-
OTHERS NICE SOLUTIONS (NAV LOGIN IS REQUIRED)
Olof Simren Solution
Kill Sessions in Dynamics NAV (from NAV Page)
Source https://www.olofsimren.com/kill-sessions-in-dynamics-nav/
Gunnar Solutions
ADO NET Solution
Kill Idle Connections
Source http://www.dynamics.is/?p=471
Poweshell Solution
Stopping Idle Microsoft Dynamics NAV Services
Nice Sessions Killing !

Let NAV BEEP! (with .NET Interop “SystemSounds Class”)



beep

“It is known that in Role Tailored Based environment BEEP C/AL function is not supported”  https://msdn.microsoft.com/en-us/library/dd301405.aspx?f=255&MSPPError=-2147217396

… nice old post (but actual) with linked sample from NAV TEAM about utilization of .NET Interop (in this case with “SystemSounds Class from System.Media”) to simulate the obsolete BEEP Function)  https://msdn.microsoft.com/en-us/library/ms143809.aspx

Source: Let NAV BEEP! (with .NET Interop and Microsoft Dynamics NAV 2009 R2) – Dynamics NAV Team Blog

A Visual SQL Query Designer – Code project (SQL From NAV Part III)


A Visual SQL Query Designer – Code Project (SQL From NAV Part III)

If some of your customers asks you to gain access to SQL data directly from NAV (as other systems with integrated query editors..) can use this nice tool to run SQL queries (or others T-SQL statements…).
This tools recalls some typical features of SQL Studio Management studio like Visual SQL Query Designer; currently is available in 2 releases (VB.NET and C #), i tested the C# solution that works well.

Tool is under “CPOL Licence”

“Source Code can be modified to create derivative works”, …  you can modify the solution’s source to create derivates. For example you can create a class for NAV .NET integration or create an addin for RTC Client, the tool also can be used directly by calling him from Wshell in NAV (.EXE); you can however also pilot the numbers of records extracted etc. etc. .. nice tool anyway.

Tool features

“This article describes the implementation of a QueryDesignerDialog class that allows users to create SQL queries based on a given OLEDB connection string.

query1

query2

query3

Code Download

Source http://www.codeproject.com/Articles/43171/A-Visual-SQL-Query-Designer

NAV 2013 SQL Query Codeunit Tool (SQL From NAV Part II)


NAV 2013 SQL Query Codeunit tool

i found this nice demo tool by Dynamic Manufacturing Solutions on Mibuso.

Source http://mibuso.com/downloads/nav-2013-sql-query-codeunit
nice tool, based on .NET integration (AD), usefull if you don’t like Powershell.

Purpose of this tool
“Simple codeunit allows you to execute an SQL query to populate any NAV (temporary) table using one call.  Test page is included to demonstrate functionality with a simple query.”

… Nice tool (with demo page & codeunit objects) tu run SQL Statements directly from NAV using .NET integration (ADODB); you can run SQL Select statements and any sql commands (if you have rights).

Modifications

I did some modifications to load a generic table using dynamics fields.. example field1. field2, fiels3 etc. and possibility lo load a saved SQL Statement from “.SQL” Script file or from BLOB field in “SQL Commands Table” etc. ..

Example of utilization
..you can retrieve Intercompany “Contacts” running a simple “SELECT with UNION SQL statement”…
.. also you can publish this page “as a web service” to retrive\expose data from multi-sources database using only a web service connection!

SQL RUN COMMAND

SIMPLE QUERY
SELECT SUM(Quantity) AS QuantityTot, [Item No_], [Location Code], [Lot No_], [Serial No_]
FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

MORE COMPLEX QUERY WITH HAVING FILTERS
SELECT SUM(Quantity) AS Field1,  [Item No_] as Field2, [Location Code] as Field3,
[Lot No_] as Field4, [Serial No_] as Fileld5 FROM dbo.[CRONUS Italia S_p_A_$Item Ledger Entry]
GROUP BY [Item No_], [Location Code],[Lot No_],[Serial No_]
HAVING ([Location Code]<>” AND (SUM(Quantity)) >0)

SQL STATEMENT TO “CREATE A NEW TABLE”
CREATE ACTIVITY 2 TABLE
CREATE TABLE [dbo].[CRONUS Italia S_p_A_$Activity_2](
[timestamp] [timestamp] NOT NULL,
[Code] nvarchar NOT NULL,
[Description] nvarchar NOT NULL,
CONSTRAINT [CRONUS Italia S_p_A_$Activity$10] PRIMARY KEY CLUSTERED
(
[Code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ANY SQL COMMAND EX  BACKUP…..

etc. etc.

Great tool!

Source http://mibuso.com/downloads/nav-2013-sql-query-codeunit

Retrieve Data from .NET Dialog with .NET integration


Retrieve Data from .NET Dialog with .NET integration

You can simply retrieve data from .NET Dialogs using NAV .NET Integration.

Example: Object 50002 Get Value from .NET Input Dialog

On Page Action, declare .NET variables

DotNet variables are linked to standard System.Windows.Forms class.

Open Page

Click on Button

Input Data > insert “TEST”

Retrieve Data from .NET Command Prompt

Source C/AL Code

OBJECT Page 50002 Get Value from .NET Input Dial

{

OBJECT-PROPERTIES

{

Date=06/06/16;

Time=10.49.25;

Modified=Yes;

Version List=NAV9;

}

PROPERTIES

{

CaptionML=ENU=Get Value From .NET Input Dialog;

ActionList=ACTIONS

{

{ 1101340000; ;ActionContainer;

CaptionML=ITA=-;

ActionContainerType=NewDocumentItems }

{ 1101340001;1 ;Action ;

Name=Get Value From .NET Dialog Input;

Promoted=Yes;

PromotedIsBig=Yes;

PromotedCategory=Process;

OnAction=VAR

PromptInput@1101340000 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.Form” RUNONCLIENT;

PageBordStyle@1101340001 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.ButtonBorderStyle” RUNONCLIENT;

PageStartPos@1101340002 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.FormStartPosition” RUNONCLIENT;

lblInputData@1101340004 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.Label” RUNONCLIENT;

txInputData@1101340008 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.TextBox” RUNONCLIENT;

Confirmation@1101340011 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.Button” RUNONCLIENT;

DialogResult@1101340012 : DotNet “‘System.Windows.Forms, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’.System.Windows.Forms.DialogResult” RUNONCLIENT;

BEGIN

//Creating .NET Page

PromptInput := PromptInput.Form();

PromptInput.Width := 500;

PromptInput.Height := 300;

PromptInput.StartPosition := PageStartPos.CenterScreen;

//Creating Controls on Page

lblInputData := lblInputData.Label();

lblInputData.Text(‘Enter DataText:’);

lblInputData.Left(70);

lblInputData.Top(70);

PromptInput.Controls.Add(lblInputData);

//Adding Labels and text boxes

txInputData := txInputData.TextBox();

txInputData.Left(200);

txInputData.Top(70);

txInputData.Width(180);

PromptInput.Controls.Add(txInputData);

//Adding Confirmation Button

Confirmation := Confirmation.Button();

Confirmation.Text(‘OK’); //OK

Confirmation.Left(200);

Confirmation.Top(150);

Confirmation.Width(150);

Confirmation.DialogResult := DialogResult.OK;

PromptInput.Controls.Add(Confirmation);

PromptInput.AcceptButton := Confirmation;

// Getting data from prompt dialog

IF (PromptInput.ShowDialog().ToString() = DialogResult.OK.ToString()) THEN

MESSAGE(txInputData.Text); //TEST *** Print message retrieved from prompt

PromptInput.Dispose();

END;

}

}

}

CONTROLS

{

}

CODE

{

BEGIN

{

//Retrieve Data from .NET Dialog Input

//with .NET integration

}

END.

}

}