Couple of years back, I had to do something like this. I created a stored proc that could be called to run the report and mail it. First, you set up a subscription for the report on the report server and then use the stored proc with the report path.
This was not perfect; it has at least two problems/issues: a) if I had two subscriptions on a report, this would have failed, or picked up a random one (not sure which). b) this does not give you control over who to mail it out to etc. That is pre-defined in the subscription. But, for my purposes, this was good enough.
If you develop something more robust/flexible, and if you can share it, that would be appreciated.
CREATE PROCEDURE [dbo].[GenerateReport]
-- To generate a report based on a subscription if the path of the report is given.
-- This is the report to be generated.
-- example: @ReportPath '/MyReports/AverageCost'
DECLARE @subscriptionId UNIQUEIDENTIFIER;
DECLARE @msg VARCHAR(255);
SELECT @subscriptionId = rs.SubscriptionId
FROM ReportServer.dbo.ReportSchedule RS
INNER JOIN ReportServer.dbo.Catalog C ON ( C.ItemID = RS.ReportID )
WHERE Path = @ReportPath ;
IF (@subscriptionId IS NULL)
INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 0);
SET @msg = 'No scheduled report was found for path = ' + @ReportPath;
INSERT INTO dbo.DIANGOSE_ReportRuns VALUES (GETDATE(), @ReportPath,@subscriptionId, 1);
The dbo.DIANGOSE_ReportRuns is my debugging/logging table.