Data-Driven Subscription with SSRS

Business alerts in GP are a good notification mechanism. However, SSRS available with GP is a notch above Business alerts when it comes to alerting key stakeholders critical information.

Following is an illustration of how to mimic GP's business alert like behavior with an already created SSRS report and a data-driven subscription.

Here is the CREATE SQL of the SQL view that drives the SSRS report in GP that I am going to add subscribe to.

This report looks at all vendor address line 1 values that exceed 32 characters in length.

Picture 1

CREATE VIEW dbo.vPositivePayVendorAddressLengthCheck
AS

select        vnd.VENDORID    [VENDOR ID],
            vnd.VNDCHKNM    [CHECK NAME],
            ADR.ADRSCODE    [Address Code],
            adr.ADDRESS1    [ADDRESS1]
FROM    PM00300 ADR WITH (NOLOCK) INNER JOIN
        PM00200 VND    WITH (NOLOCK)    ON
        (    ADR.VENDORID    =    VND.VENDORID    AND
            (ADR.ADRSCODE        =    VND.VADDCDPR OR
             ADR.ADRSCODE        =    VND.VADCDPAD OR
             ADR.ADRSCODE        =    VND.VADCDSFR OR
             ADR.ADRSCODE        =    VND.VADCDTRO))
WHERE    LEN(adr.ADDRESS1)    >    32

Picture 2

Picture 3

 

 

 

 

 

 

Picture 4

 

 

 

 

 

 

 

Picture 5

Next, paste the SQL from the view in the first step but by replacing the select with select distinct as shown below

and click on validate.

 

 

 

 

 

 

 

Picture 6

 

 

 

Click on next and make sure you have the parameters @To and @CC from the above page populated as shown below

Click on finish and you now have a data-driven subscription. The email with the excel report will be sent only if there are rows in the view in Step 1.

You can recreate this subscription by replacing the view here with your view and the subscription SQL with your View and recipients list.

Thanks for reading my post and do not hesitate to leave a comment or a question.