CODEWITHSUNDEEP
sqlvisual-studioreporting-servicesssrs-2012ssis-2012
Addy
Addy

Reputation: 36

Create multiple SSRS reports if there is any new update in database

we are generating SSRS reports and have 1 parameter for ID, by entering ID manually it will generate SSRS report.

Requirement: If there is any new ID in the database, or any update in the database, we want to create SSRS report for that particular ID.

we run this reports every week, and for ex if there is update for 15 ids, we want 15 new SSRS reports for those particular IDs.

Upvotes: 0

Views: 72

Answers (2)

Jesse
Jesse

Reputation: 873

Based off your example, you could, with each run of the report store the ID's you have generated the report for in a table. Then when you re-run it the next week it finds all the newId's and runs for them. Or you can do a similar query based off a timestamp of report last ran and create date on a record, finding records created after the last report run.

Not knowing what the report does, rather than run it once for 1 id, run it for many ID's and use page breaks.

Or cycle through each ID, adjust the criteria in the subscription meta data in the reportServer database, then execute the SQL Agent job the subscription creates to generate the new report with the new ID.

Upvotes: 0

NewGuy
NewGuy

Reputation: 1030

You could create a trigger or procedure that truncates and inputs the changes into a "changes" table, run this on the desired schedule.

Create a data driven subscription that reads from that "changes" table.

After the Insert Statement execute the ReportCommand script that will trigger the report to run.

SELECT  DISTINCT b.job_id,
'EXEC ReportServer.dbo.AddEvent @EventType=''TimedSubscription'', @EventData='''
    + CAST(a.SubscriptionID AS VARCHAR(40)) + '''' AS ReportCommand ,
    e.Name,
    b.name AS JobName ,
    a.SubscriptionID ,
    e.Name ,
    e.Path ,
    d.Description ,
    d.LastStatus,
    d.LastRunTime
FROM    ReportServer.dbo.ReportSchedule a
    JOIN msdb.dbo.sysjobs b ON a.ScheduleID = b.name
    JOIN ReportServer.dbo.ReportSchedule c ON b.name = CONVERT(VARCHAR(50),c.ScheduleID)
    JOIN ReportServer.dbo.Subscriptions d ON c.SubscriptionID = d.SubscriptionID
    JOIN ReportServer.dbo.Catalog e ON d.Report_OID = e.ItemID
WHERE  e.Path LIKE '%path%'

EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='123214134-13242-4890-A3D3424-D23423D17E6CBD'

Upvotes: 2

Related Questions