Create SSRS Data Driven Subscriptions on Standard Edition

  • bernard.beckett

    SSC Veteran

    Points: 216

    Comments posted to this topic are about the item Create SSRS Data Driven Subscriptions on Standard Edition

  • epowell 61898

    SSC Enthusiast

    Points: 185

    Bernard,
    Good solution - we had the same issue with multiple clients but wanted a user capable driven solution that did SSRS, Crystal and Excel - we found  R-Tag Manager works well for users but that cost is $999 cheaper than the Enterprise addition and users love it - interface is a bit weird but one your used to it it works and you can "batch" solutions.
    I will use your solution for my canned stuff and for clients who wont spend money. 
    Thank you so much for sharing.

  • Thom A

    SSC Guru

    Points: 98646

    Not a great fan of looping through the parameters.

    It's also worth noting that if you have a subscription running, and try to run it again (at least with 2012-) then the old subscription will not be completed. This is a real pain if you have multiple events occur within a very short time period, as if one event starts before another finishes, the earlier report will not be submitted.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • cerulean47

    Valued Member

    Points: 61

    Thanks for this.

    What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.

  • Thom A

    SSC Guru

    Points: 98646

    cerulean47 - Tuesday, October 31, 2017 7:43 AM

    Thanks for this.

    What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.

    You wouldn't be able to do this with a TRY...CATCH. When you run an SSRS report using T-SQL, all you effectively do is mark the subscription to be run; the database engine doesn't do the work. If the report/subscription fails is a completely separate task.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • cerulean47

    Valued Member

    Points: 61

    Thom A - Tuesday, October 31, 2017 8:07 AM

    cerulean47 - Tuesday, October 31, 2017 7:43 AM

    Thanks for this.

    What do you think about wrapping the report execution in a try/catch? I'd want to make sure if the report execution errors out for any reason, we get a chance to safely restore the original parameters.

    You wouldn't be able to do this with a TRY...CATCH. When you run an SSRS report using T-SQL, all you effectively do is mark the subscription to be run; the database engine doesn't do the work. If the report/subscription fails is a completely separate task.

    I see. The timed delay makes me a little nervous. Is it possible to poll to determine if the report successfully started?

  • DeusExDatum

    Ten Centuries

    Points: 1144

    Wrote this for a client years ago, still running today.

    Couple of tips:
    1.) Handle the parameters and extension settings as the whole XML BLOB; don't chunk it up.  Code is much cleaner, and format is guaranteed to remain intact.


    --Not all variables declared for brevity, but you get the idea
    DECLARE
                @extensionSettings XML,
                @parameters XML

       SELECT
            @extensionSettings = ExtensionSettings,
            @parameters = [Parameters]
        FROM dbo.Subscriptions
        WHERE SubscriptionID = @subscriptionID;
    --Set Extension Options
        SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value[1]/text())[1] with sql:variable("@Email")');
        SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="Subject"]/Value[1]/text())[1] with sql:variable("@emailSubject")');
        SET @extensionSettings.modify('replace value of (/ParameterValues/ParameterValue[Name="Comment"]/Value[1]/text())[1] with sql:variable("@BodyString")');
        
        --Set Report Options
        SET @parameters.modify('replace value of (/ParameterValues/ParameterValue[Name="ID"]/Value[1]/text())[1] with sql:variable("@ID")');
        SET @parameters.modify('replace value of (/ParameterValues/ParameterValue[Name="Month"]/Value[1]/text())[1] with sql:variable("@ReportMonth")');
       
        UPDATE dbo.Subscriptions
            SET
                ExtensionSettings = CAST(@extensionSettings AS nvarchar(max)),
                [Parameters] = CAST(@parameters AS nvarchar(max))
        WHERE SubscriptionID = @subscriptionID;

    2.) Don't rely on SQL Agent to execute;  it takes way longer, and job calls are async, so you need extra code deployed to check job status before you can change things and run the next iteration of the report.  You can inject Events directly to SSRS, and your procedure essentially fires all the iterations of the report instead of SSRS, based on the data driven set you put in the proc.

    --Write a loop or cursor around this and include your updates to the subscription

    --Queue the Report for execution with the parameter / extension changes you've made
    EXEC dbo.AddEvent @EventType = 'TimedSubscription', @EventData = @subscriptionID;
        
        --Delay to ensure it is queued
        WAITFOR DELAY '00:00:01.500';
        
        --If the execution is still queued, or running, wait
        WHILE EXISTS (SELECT TOP 1 1 FROM dbo.Event WHERE EventData = @subscriptionID ) OR EXISTS (SELECT TOP 1 1 FROM dbo.Notifications WHERE SubscriptionID = @subscriptionID)
                        BEGIN
                            WAITFOR DELAY '00:00:01.000';
                        END

        --At this point, we would loop, set parameters / extensions on the subscription for the next iteration of the report, and run another AddEvent

    I run about 700 reports a month like this through 2 proc calls (PDF Export to fileshare, and Emailed); takes about 20 minutes and the two reports can run in parallel.  Definitely better than eating Enterprise licensing cost. 😉

    Josh Lewis

  • Ronzo

    Hall of Fame

    Points: 3741

    It is worth noting that if you are using SQL 2016 and above, you can use the built-in STRING_SPLIT function instead of "rolling your own". It is limited to single character separators, but is much faster and a lot less code to write/maintain.


    Have Fun!
    Ronzo

  • bernard.beckett

    SSC Veteran

    Points: 216

    Thom A - Tuesday, October 31, 2017 6:15 AM

    Not a great fan of looping through the parameters.

    It's also worth noting that if you have a subscription running, and try to run it again (at least with 2012-) then the old subscription will not be completed. This is a real pain if you have multiple events occur within a very short time period, as if one event starts before another finishes, the earlier report will not be submitted.

    Hi Thom,

    Thanks for your reply.

    Your comment above has also been catered for in the main stored proc with this piece of code, which will wait for one subscription to end before starting another:

    -- we need to wait for our turn at using the subscription system
        WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')
            WAITFOR DELAY '00:00:30';
            CREATE TABLE ##ReportInUse (ReportID int);

  • bernard.beckett

    SSC Veteran

    Points: 216

    Ronzo - Wednesday, November 1, 2017 10:59 AM

    It is worth noting that if you are using SQL 2016 and above, you can use the built-in STRING_SPLIT function instead of "rolling your own". It is limited to single character separators, but is much faster and a lot less code to write/maintain.

    Hi Ronzo,

    The new SPLT_STRING function is only available in this version of SQL: SQL Server 2016 RC0

  • nikimpatel

    Valued Member

    Points: 64

    epowell 61898 - Tuesday, October 31, 2017 6:05 AM

    Bernard,
    Good solution - we had the same issue with multiple clients but wanted a user capable driven solution that did SSRS, Crystal and Excel - we found  R-Tag Manager works well for users but that cost is $999 cheaper than the Enterprise addition and users love it - interface is a bit weird but one your used to it it works and you can "batch" solutions.
    I will use your solution for my canned stuff and for clients who wont spend money. 
    Thank you so much for sharing.

    Thanks for your help. I like "R-Tag Manager".

  • JSB_89

    Ten Centuries

    Points: 1058

    Bernard,

    Thnx for your solution , we did an upgrade from 2012 Developer to 2016 Standard . I have never did SSRS earlier (Data Driven subscriptions part). So i might need some help with this solution. I will get with you and thnx again for your solution .

  • bernard.beckett

    SSC Veteran

    Points: 216

    @JSB_89, it's a pleasure, and I'll gladly help where I can.

  • This was removed by the editor as SPAM

  • vlampone

    Grasshopper

    Points: 12

    Hi, I am trying to understand the purpose of this workaround Data Driven Subscription. We are upgrading SSRS 2012 Enterprise to 2016 Standard Edition. We often use the data driven for to send the report if it contains data and "end time workaround". Is it possible to add IF/ELSE and CASE statements into this script? Here is the template we use in the data driven query portion. Please let me know your thoughts. Any help would be appreciated. Thanks in advance.

    DECLARE @now DATETIME = GETDATE(), @StartDate DATETIME
    IF DATEPART(WEEKDAY, GETDATE()) BETWEEN 2 AND 6 -- Monday to Friday
    AND DATEPART(HOUR, @now) IN (8,9,10,11,12,13,14,15,16,17,18,19,20) --8am to 8pm

    BEGIN
    SET @StartDate = CASE WHEN DATEPART(WEEKDAY, @now) = 2 -- Monday
    AND DATEPART(HOUR, @now) = 8 -- 8am
    THEN DATEADD(HOUR, -60, @now) -- set it to 8:00pm Friday

    WHEN DATEPART(WEEKDAY, @now) BETWEEN 3 AND 6 -- Tuesday to Friday
    AND DATEPART(HOUR, @now ) = 8 -- 8am
    THEN DATEADD(HOUR, -12, @now) -- Set it back to 8pm

    ELSE DATEADD(HOUR, -1, @now)
    END
    END

    DECLARE @Count INT

    --INSERT FROM QUERY HERE
    SELECT @Count = COUNT(*)

    --Check if the report contains data
    IF (@Count > 0 AND @StartDate IS NOT NULL)
    BEGIN

    SELECT email_address = ''
    ,bcc = ''
    ,StartDate = @StartDate
    ,Enddate = @now

    END

    ELSE
    SELECT email_address = ''
    ,bcc = ''
    ,StartDate = DATEADD(HOUR, -1, @now)
    ,Enddate = @now


     

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply