There are many posts on how to automatically generate of a PDF receipt and email it by leveraging SSRS. Here is how it went for me.
- Create a report that shows the details required.
- Create a parameters table.
- Subscribe to the report using the parameters table.
- Create a stored-procedure to populate and fire the subscription.
On a version of SQL Server Reporting Services (SSRS) that supports data-driven-subscriptions (DDS) I created a report called ‘SingleInvoice’ with one input parameter ‘invoice number’.
Outside of this report, in preparation for the DDS, I created a data source pointing to the local [ReportServer] database.
Within the [ReportServer] database I created a table called [dbo].[InvoiceParams]
CREATE TABLE [dbo].[InvoiceParams](
[InvoiceNumber] [VARCHAR](100) NULL,
[ToEmailAddress] [VARCHAR](200) NULL,
[CCEmailAddress] [VARCHAR](200) NULL,
[BccEmailAddress] [VARCHAR](200) NULL,
[ReplyToEmailAddress] [VARCHAR](200) NULL,
[IncludeReport] [BIT] NULL,
[RenderFormat] [VARCHAR](20) NULL,
[Priority] [VARCHAR](15) NULL,
[Subject] [VARCHAR](150) NULL,
[Comment] [VARCHAR](150) NULL,
[IncludeLink] [BIT] NULL,
[Active] [BIT] NULL,
[DateInserted] [DATETIME] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT (NULL) FOR [CCEmailAddress]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT (NULL) FOR [BccEmailAddress]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT (NULL) FOR [ReplyToEmailAddress]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT ((1)) FOR [IncludeReport]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT ('PDF') FOR [RenderFormat]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT ('Normal') FOR [Priority]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT ((0)) FOR [IncludeLink]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT ((1)) FOR [Active]
ALTER TABLE [dbo].[InvoiceParams] ADD DEFAULT (GETDATE()) FOR [DateInserted]
GO
To simplify the stored procedure I defined default values for all the columns I would not be dynamically populating.
Next I created a Data-driven subscription on the report with a schedule in the past – so it would never fire. For Destination I chose E-Mail.
Within the subscription I edited the dataset and chose the previously created shared data source [ReportServer].
I added this query before clicking ‘Apply’….
SELECT *
FROM dbo.InvoiceParams
WHERE Active = 1;
Back in the New Subscription form, I completed the Delivery options like this …
Within the user database I created this stored-procedure …
/*==================================================
Author: Richard Smith
Create date: 10 Jul 2020
Description: To Email PDF receipts - demo version
Test: Exec [dbo].[EmailReceipts] 'INV123456789',
'Richard.Smith@company.com'
=================================================*/
ALTER PROC [dbo].[EmailReceipts]
@InvoiceNumber VARCHAR(100),
@ToEmailAddress VARCHAR(200),
@Subject VARCHAR(150) = 'test subject',
@Comment VARCHAR(150) = 'test body',
@SubscriptionID NVARCHAR(260) = '987654321'
/* Report = "SingleInvoice" */
AS
BEGIN
SET NOCOUNT ON;
/* 1 Save the inputs */
INSERT INTO [ReportServer].[dbo].[InvoiceParams]
(InvoiceNumber, ToEmailAddress, [Subject], Comment)
VALUES (@InvoiceNumber, @ToEmailAddress, @Subject, @Comment);
/* 2 Trigger subscription. Which will send the report (+ inputs) to the email-subsystem-queue */
EXEC [ReportServer].[dbo].[AddEvent] @EventType = 'TimedSubscription', @EventData = @SubscriptionID;
WAITFOR DELAY '00:00:10';
/* 3 If no longer in queue, flag as sent */
IF NOT EXISTS (SELECT 1 FROM [ReportServer].[dbo].[Event] WHERE EventData = @SubscriptionID)
UPDATE [ReportServer].[dbo].[InvoiceParams]
SET Active = 0
WHERE InvoiceNumber = @InvoiceNumber
AND ToEmailAddress = @ToEmailAddress;
/* 4 Manage the log */
DELETE FROM [ReportServer].[dbo].[InvoiceParams] WHERE DateInserted < GETDATE()-30;
SELECT * FROM [ReportServer].[dbo].[InvoiceParams] ORDER BY DateInserted DESC;
END;
GO
When executed with an email address and invoice number this stored procedure will send an email to the email address with the PDF invoice attached.
NOTE
To find @SubcriptionID I used this …
SELECT SCH.SubscriptionID
FROM [ReportServer].[dbo].[Catalog] CAT
JOIN [ReportServer].[dbo].[ReportSchedule] SCH
ON CAT.ItemID = SCH.ReportID
WHERE CAT.Path= '/ReportPath/ReportName';
Database Consultant 