2013-09-21

SSRS - Create batch executing function for the reports which cannot use Data-driven Subscription functionality - V2 (...reviewing, V3 is coming soon.)

Please note that there is a more powerful method I have developed, the new function can meet concurrent requirement while the existing one only allows single-user to execute it. I will update this article as soon as possible.

Here is the scenario:
Users manually run the specific report by using a set of predefined parameters to export reports with different purposes/results. Instead of manually running the report one by one, users want to automatically run it by maintaining those parameters in the table.

The first thought came to my mind is Data-driven Subscription functionality in Reporting Services, unfortunately, the specific report uses too complex dynamic design, which means it use many user-defined parameters to dynamically control report's interface. When I was trying to use the function, I received the following error message:




I tried making a normal subscription, and retrieved relevant subscription data by using the following query:
SELECT    SC.LastStatus,
                   SC.LastRunTime,
                   U.UserName,
                   CL.Name,
                   SC.*
FROM        ReportServer.dbo.Subscriptions SC
                   JOIN ReportServer.dbo.Users U
                     ON U.UserID = SC.OwnerID
                   JOIN ReportServer.dbo.[Catalog] CL
                    ON CL.ItemID = SC.Report_OID
I tried changing some settings and parameter values in the subscription such as File Name, Path, Render Format, Scheduled date and time, etc. After I used the query to check subscription data, I found that there are two columns changed - ExtensionSettings and Parameters.

The information is still not enough to identify how the subscription is invoked. I can guess Report Server executing the job through SQL Server Agent, when I expanded Jobs folder in SQL Server Agent, I saw a lot of jobs which its name is Uniqueidentifier type, I opened one of them to confirm it is a subscription job created from Report Server, for example:



I backed to Report Server database and found another critical table called Schedule, and changed the query as follows:
SELECT    SC.LastStatus,
                   SC.LastRunTime,
                   U.UserName,
                   CL.Name,
                   RS.ScheduleID,
                   SC.*
FROM        ReportServer.dbo.Subscriptions SC
                   JOIN ReportServer.dbo.Users U
                    ON U.UserID = SC.OwnerID
                  JOIN ReportServer.dbo.[Catalog] CL
                     ON CL.ItemID = SC.Report_OID
                   JOIN ReportServer.dbo.ReportSchedule RS
                     ON RS.SubscriptionID = SC.SubscriptionID
Once I confirmed ScheduledID is equal to job's name in SQL Server Agent, there were some concerns before I started to develop query:

1. Transform users' list of parameters into XML format as the values in ExtensionSettings and Parameters of Subscriptions table.
2. Execute subscription job in the query.

Please see the logic and explanation of query as follows:
-- =============================================
-- Author:        Spiner Tsai
-- Description:    For batch executing report subscription of ProjectSummaryReport
-- =============================================
CREATE PROCEDURE [dbo].[uspBatchExexuteReportSubscription_ProjectSummaryReport]
AS
    SET NOCOUNT ON;

    DECLARE @tmp_mprif TABLE
            (
                [Path]                       VARCHAR(300),
                [FileName]               VARCHAR(300),
                ParentProject            VARCHAR(100),
                DateFrom                 VARCHAR(50),
                DateTo                      VARCHAR(50),
                ShowPctCompelte    VARCHAR(10)
            )  

    DECLARE @tmp_PV TABLE
            (
                ID               INT IDENTITY(1,1),
                UseFor       VARCHAR(30),
                Name         VARCHAR(30),
                Value         VARCHAR(100)
            )
          
    DECLARE @Path                           VARCHAR(300),
                       @FileName                   VARCHAR(300),
                       @ParentProject             VARCHAR(100),
                       @DateFrom                  VARCHAR(50),
                       @DateTo                      VARCHAR(50),
                       @ShowPctCompelte    VARCHAR(10),
                @SubscriptionID          UNIQUEIDENTIFIER,
                @LastStatus              VARCHAR(500)
          
    DECLARE @ExtensionSettings    VARCHAR(MAX),
                        @Parameters              VARCHAR(MAX),
                        @ScheduleID             UNIQUEIDENTIFIER
  
    SELECT    @ScheduleID = RS.ScheduleID
                , @SubscriptionID = SC.SubscriptionID
    FROM     ReportServer.dbo.Subscriptions SC
                JOIN ReportServer.dbo.Users U
                  ON U.UserID = SC.OwnerID
                JOIN ReportServer.dbo.[Catalog] CL
                  ON CL.ItemID = SC.Report_OID
                JOIN ReportServer.dbo.ReportSchedule RS
                  ON RS.SubscriptionID = SC.SubscriptionID
    WHERE    U.UserName = 'spinertsai'
                AND CL.Name = 'ProjectSummaryReport'
   
    --I have prepared a webpage in SharePoint that it allows users can add, update, or delete the list of report parameters, and synchronize changes to the table.
    INSERT INTO @tmp_mprif
    SELECT          ReportName
                  , ReportPurpose
                  , [PATH] = Directory
                             , [FILENAME] = 'Project Report - ' + ReportName + ' - ' + ReportPurpose + ' - ' + CONVERT(VARCHAR, ToDate, 120)
                            , [ParentProject] = Projects                                          
                            , DateFrom =  CONVERT(VARCHAR, FromDate, 103) + ' 12:00:00 AM'
                            , DateTo = CONVERT(VARCHAR, ToDate, 103) + ' 12:00:00 AM'
                            , ShowPctComplete = CASE Percentage WHEN 'Y' THEN 'True' ELSE 'False' END
    FROM            Monthly_Project_Report_Input_File
    WHERE         Export = 'Y' --Use this field to control which reports need to be executed.
    ORDER BY   ReportName, ReportPurpose DESC
          
    DECLARE cur_mrif CURSOR FOR    SELECT * FROM @tmp_mprif
                                                                                                          
    OPEN cur_mrif
    FETCH NEXT FROM cur_mrif INTO    @ReportName, @ReportPurpose, @Path, @FileName, @ParentProject, @DateFrom, @DateTo, @ShowPctCompelte

    WHILE @@FETCH_STATUS = 0
    BEGIN
        INSERT INTO @tmp_PV
        VALUES('ExtensionSettings', 'PATH', @Path)
      
        INSERT INTO @tmp_PV
        VALUES('ExtensionSettings', 'FILENAME', @FileName)
        --==================================================
        --The parameter's name included "Show" which is used to dynamically control report's interface.
      
        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowInvoiced', 'True')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'PageBreak', 'None')

        --Transform days formation from "dd" into "d"
        SET @DateFrom = CASE WHEN LEFT(@DateFrom, 1) = '0' THEN RIGHT(@DateFrom, LEN(@DateFrom)-1) ELSE @DateFrom END
        INSERT INTO @tmp_PV
        VALUES('Parameters', 'DateFrom', @DateFrom)

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'DataAreaId', 'TST')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'Baseline', 'FBase')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ModelId', '2010-01')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowSubProjects', 'True')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowSell', 'Total')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowActivityLevel', 'False')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowCosts', 'Total')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowPctComplete', @ShowPctCompelte)

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowHours', 'True')

        INSERT INTO @tmp_PV
        SELECT    'Parameters',
                          'ParentProject',
                          PROJID
        FROM      dbo.udfSplitStringArray(@ParentProject)

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowForecast', 'False')

        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowFinished', 'True')

        SET @DateTo = CASE WHEN LEFT(@DateTo, 1) = '0' THEN RIGHT(@DateTo, LEN(@DateTo)-1) ELSE @DateTo END
        INSERT INTO @tmp_PV
        VALUES('Parameters', 'DateTo', @DateTo)
      
        INSERT INTO @tmp_PV
        SELECT    'Parameters',
                           'Projects',
                           PROJID
        FROM       dbo.AX_Projects
        WHERE    ParentProjId IN (SELECT * FROM dbo.udfSplitStringArray(@ParentProject))
      
        INSERT INTO @tmp_PV
        VALUES('Parameters', 'ShowWIP', 'True')
      
        --SELECT * FROM @tmp_PV
        --==================================================
        --Transform dataset into XML format for ExtensionSettings
        SET @ExtensionSettings =    (  
                                                              SELECT    Name, Value
                                                              FROM       @tmp_PV ParameterValue
                                                              WHERE    UseFor = 'ExtensionSettings'
                                                              FOR          XML AUTO, ELEMENTS
                                                          )
        --Configure fixed setting strings
        --For USERNAME AND PASSWORD, they have been encoded when subscription created, this part affect saving files in designated path/folder with sufficient permission.
        --If designated path/folder has security concern, ask stakeholders create a temporary subscription and use its encoded string.
        SET @ExtensionSettings = @ExtensionSettings + '<ParameterValue><Name>FILEEXTN</Name><Value>True</Value></ParameterValue>'
        SET @ExtensionSettings = @ExtensionSettings + '<ParameterValue><Name>USERNAME</Name><Value>[Encoded User Name]</Value></ParameterValue>'
        SET @ExtensionSettings = @ExtensionSettings + '<ParameterValue><Name>PASSWORD</Name><Value>[Encoded Password]</Value></ParameterValue>'
        SET @ExtensionSettings = @ExtensionSettings + '<ParameterValue><Name>RENDER_FORMAT</Name><Value>PDF</Value></ParameterValue><ParameterValue>'
        SET @ExtensionSettings = @ExtensionSettings + '<Name>WRITEMODE</Name><Value>Overwrite</Value></ParameterValue>'
        SET @ExtensionSettings = '<ParameterValues>' + @ExtensionSettings + '</ParameterValues>'
      
        --SELECT @ExtensionSettings
        --==================================================
        --Transform dataset into XML format for Parameters
        SET @Parameters = (  
                                                      SELECT    Name, Value
                                                      FROM       @tmp_PV ParameterValue
                                                      WHERE    UseFor = 'Parameters'
                                                      ORDER BY ID
                                                      FOR          XML AUTO, ELEMENTS
                            )  
        SET @Parameters = '<ParameterValues>' + @Parameters + '</ParameterValues>'
      
        --SELECT @Parameters
        --==================================================
        DELETE FROM @tmp_PV
        --==================================================
        --Note: Need to grant db_datawriter role to users/groups, in my case, I grant RSExecRole to users/groups.
       UPDATE    SC
        SET         LastStatus = 'Ready'
                    , ExtensionSettings = CONVERT(NTEXT, @ExtensionSettings)
                    , [Parameters] = CONVERT(NTEXT, @Parameters)
        FROM     ReportServer.dbo.Subscriptions SC
        WHERE    SubscriptionID = @SubscriptionID
      
        --Note: Need to grant Execute permission of the stored procedure to users/groups, in my case, I grant RSExecRole to users/groups.
        EXEC msdb.dbo.sp_start_job @ScheduleID
      
        --SQL Server Agent needs time to finish executing one single job, if you execute next job without waiting, the following queues will fail.
        WHILE 1 = 1
        BEGIN
            SELECT    @LastStatus = LastStatus
            FROM     ReportServer.dbo.Subscriptions
            WHERE    SubscriptionID = @SubscriptionID
      
            IF @LastStatus <> 'Pending' AND @LastStatus <> 'Ready'
                BREAK
        END
      
        --I have prepared another web page in SharePoint that it can help users view status of executing result after users click Export button.
        UPDATE     Monthly_Project_Report_Input_File
        SET          LastStatus = @LastStatus
        WHERE     ReportName = @ReportName
                      AND ReportPurpose = @ReportPurpose
        --==================================================
        FETCH NEXT FROM cur_mrif INTO  @ReportName, @ReportPurpose, @Path, @FileName, @ParentProject, @DateFrom, @DateTo, @ShowPctCompelte
    END
    CLOSE cur_mrif
    DEALLOCATE cur_mrif

    --SELECT @ExtensionSettings
    --SELECT @Parameters
Back to the maintenance page, if you use server-side ASP.NET button to deploy the stored procedure into the Export button, then the stored procedure should be able to meet basic requirement. However, in my case, I use client-side HTML button to develop it with JavaScript code, the major issue I encounter is that either stored procedure is invoked from TRIGGER or invoked from JavaScript, neither the WAITFOR DELAY function nor WHILE loop works, because SQL Server treat them as a single transaction, except dummy loop such as PRINT clause to force the stored procedure to wait for a specific time. I figure out an alternative method that it can fix the issue:

1. Create a new job named BatchExexuteReportSubscription_ProjectSummaryReport in SQL Server Agent, add new step of Transact-SQL Script type, and write a command: EXEC uspBatchExexuteReportSubscription_ProjectSummaryReport
2. Create a new stored procedure:
CREATE PROCEDURE [dbo].[uspBatchExexuteReportSubscription_AddSchedule_ProjectSummaryReport]
AS
    SET NOCOUNT ON;

    DECLARE @Job_Name                 VARCHAR(100),
                @JobID                      UNIQUEIDENTIFIER,
                @Job_ScheduleID         INT,
                @now                       DATETIME,
                @active_start_date        INT,
                @active_start_time        INT

    SET @Job_Name = 'BatchExexuteReportSubscription_ProjectSummaryReport'
  
    --Initial status of waiting reports
    UPDATE Monthly_Project_Report_Input_File
    SET         LastStatus = 'Pending'  
    WHERE    Export = 1
          
    --Detach old job schedules
    DECLARE cur_mrif CURSOR FOR        SELECT    SJ.job_id,
                                                             SJS.schedule_id
                                                 FROM     msdb.dbo.sysjobs SJ
                                                             JOIN msdb.dbo.sysjobschedules SJS
                                                                 ON SJS.job_id = SJ.job_id
                                                 WHERE    SJ.name = @Job_Name
                                                                                                          
    OPEN cur_mrif FETCH NEXT FROM cur_mrif INTO @JobID, @Job_ScheduleID
    WHILE @@FETCH_STATUS = 0
    BEGIN
        --SELECT @Job_ScheduleID
      
        EXEC msdb.dbo.sp_detach_schedule @job_id=@JobID, @schedule_id=@Job_ScheduleID, @delete_unused_schedule=1
      
        FETCH NEXT FROM cur_mrif INTO @JobID, @Job_ScheduleID
    END
    CLOSE cur_mrif
    DEALLOCATE cur_mrif
  
    --==================================================
    --Add job schedules
    --After testing, msdb needs at least 6 seconds to fire the job
    SET @now = GETDATE()
    SET @active_start_date = YEAR(@now)*10000 + MONTH(@now)*100 + DAY(@now)
    SET @active_start_time = DATEPART(HOUR, @now)*10000 + DATEPART(MINUTE, @now)*100 +   
                        CASE
                                                                   WHEN DATEPART(SECOND, @now) + 7 >=60 THEN DATEPART(SECOND, @now) + 7 - 60 + 100
                                                                     ELSE DATEPART(SECOND, @now) + 7
                                                                  END
  
    --SELECT @active_start_date, @active_start_time
  
    EXEC msdb.dbo.sp_add_jobschedule @job_name=@Job_Name, @name=N'Schedule_1', @freq_type=1, @active_start_date=@active_start_date, @active_start_time=@active_start_time, @freq_subday_type=1   
3. Deploy execution command in JavaScript. 

No comments:

Post a Comment