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,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.
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
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,Once I confirmed ScheduledID is equal to job's name in SQL Server Agent, there were some concerns before I started to develop query:
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
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:
-- =============================================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:
-- 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
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]3. Deploy execution command in JavaScript.
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
No comments:
Post a Comment