October 17, 2013 at 6:18 am
Hi,
I am using SSRS 2008 R2,
I had two requirement,
1)How to give the sheet name while exporting to multiple sheets.
2)How to run SSRS report through sql.(Can it automatically export the report in excel).
Please help me this is urgent to me.
Thanks in Advance!!
October 17, 2013 at 7:55 am
avdhut.k (10/17/2013)
Hi,I am using SSRS 2008 R2,
I had two requirement,
1)How to give the sheet name while exporting to multiple sheets.
2)How to run SSRS report through sql.(Can it automatically export the report in excel).
Please help me this is urgent to me.
Thanks in Advance!!
For #1 this blog should do
http://blogs.msdn.com/b/robertbruckner/archive/2010/05/16/report-design-naming-excel-worksheets.aspx
For #2, start by making a subscription for the report to output the with the desired format (Excel). I'm not really sure why you need to execute the report from TSQL once you have a subscription for the report but you need to know the agent job name for the report subscription. You can use JobID value from this query to determine the right agent job name for your subscription. Run it against the ReportServer Database.
SELECT
cat.[Name] AS RptName
, U.UserName
, cat.[Path]
, res.ScheduleID AS JobID
, sub.SubscriptionID AS SubscriptionID
, sub.LastRuntime
, sub.LastStatus
, LEFT(CAST(sch.next_run_date AS CHAR(8)) , 4) + '-'
+ SUBSTRING(CAST(sch.next_run_date AS CHAR(8)) , 5 , 2) + '-'
+ RIGHT(CAST(sch.next_run_date AS CHAR(8)) , 2) + ' '
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN '0' + LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 1)
ELSE LEFT(CAST(sch.next_run_time AS VARCHAR(6)) , 2)
END + ':'
+ CASE WHEN LEN(CAST(sch.next_run_time AS VARCHAR(6))) = 5
THEN SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 2 , 2)
ELSE SUBSTRING(CAST(sch.next_run_time AS VARCHAR(6)) , 3 , 2)
END + ':00.000' AS NextRunTime
, CASE WHEN job.[enabled] = 1 THEN 'Enabled'
ELSE 'Disabled'
END AS JobStatus
, sub.ModifiedDate
, sub.Description
, sub.EventType
, sub.Parameters
, sub.DeliveryExtension
, sub.Version
--, substring(SUBSTRING(sub.ExtensionSettings,DATALENGTH('<ParameterValues><ParameterValue><Name>TO</Name><Value>')+1,DATALENGTH(sub.ExtensionSettings)),1,CHARINDEX('</value>',SUBSTRING(sub.ExtensionSettings,DATALENGTH('<ParameterValues><ParameterValue><Name>TO</Name><Value>')+1,DATALENGTH(sub.ExtensionSettings)))-1) AS Recipients
FROM
dbo.Catalog AS cat
INNER JOIN dbo.Subscriptions AS sub
ON cat.ItemID = sub.Report_OID
INNER JOIN dbo.ReportSchedule AS res
ON cat.ItemID = res.ReportID
AND sub.SubscriptionID = res.SubscriptionID
INNER JOIN msdb.dbo.sysjobs AS job
ON CAST(res.ScheduleID AS VARCHAR(36)) = job.[name]
INNER JOIN msdb.dbo.sysjobschedules AS sch
ON job.job_id = sch.job_id
INNER JOIN dbo.Users U
ON U.UserID = sub.OwnerID
ORDER BY cat.[Path]
, U.UserName
, RptName
Then read this article to figure out how to kick the subscription off from TSQL
October 17, 2013 at 8:14 am
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply