Archive

Posts Tagged ‘subscription’

SSRS #54 – Use @timestamp to create unique file name in Reporting Services Subscription

June 8, 2011 Leave a comment

RS Subscription seems to be a constant topic in my recent blog posts. I have summarized many different ways you can use RS Subscription, especially the Data-driven Subscription.

In this post, I’ll pass on a tip about using parameter @timestamp in a non-data-driven subscription to create a dynamic/unique file name for File Share Delivery.

Original question

Here is the original question from a reader:

Hello, I need to increment the saved file with a datetime instead of a number. In Reporting Services subscriptions I have checked the" increment file names as newer versions are added" checkbox as Overwrite Options- but it is adding a number to the end of the file. Is there a way to set this up in the subscription interface in SSRS to add a datetime?

Here is my answer

Hello,

I did some research and found a couple of useful tips. I’ll pass it on to you. I have not tested it yet.

With just regular Subscription, you can add a parameter @timestamp to the end of the File Name. This will add the time the report was saved to the file name, and create unique file names.

Other parameters such as @ReportName and @TimeExecuted will not work. They will just appear as @ReportName and @TimeExecuted in the file name.

Sherry

(Note: I forgot the link to the original blog post.)

Results

I have not tested the tip, but fortunately the reader reported back with good news.

Adding @timestamp to the end of the filename worked! Thank you!

I will still recommend using Data-driven Subscription for ultimate flexibility. For example, you cannot format the parameter @timestamp in a regular RS Subscription.

SSRS #43 – Problem with undocumented SQL procedure AddEvent when kicking off a Timed Subscription

April 15, 2011 1 comment

In my SSRS #42 post, I blogged about how you can kick off a timed subscription at the end of your ETL process.

Before you go off to implement this for your reports, I want to blog an issue with the SQL procedure AddEvent, which I used to trigger the timed subscription in the SSRS report.

Apparently this procedure is one of the many “undocumented” procedures from Microsoft.

As my blogging habit, I’ll copy some of the email communications (revised).

Issue

From: Li, Sherry
Sent: Wednesday, April 06, 2011 7:58 AM
To: ARCHITECTURE
Subject: Link to Timed Subscription Failed

Dear Architecture Team ,

I have a timed subscription on the dev reporting server. Here is the link to the subscription.

http://companyURL/Reports/Pages/Report.aspx?ItemPath=reportPath%2freportName&SelectedTabId=SubscriptionsTab

A link to the report is included in the subscription. However, this link (see below) never worked. Here is the error I got.

Would you help on this?

Thanks,

image

Response

From: The Architecture Team 
Sent: Wednesday, April 13, 2011 11:01 AM
To: Sherry
Subject: RE: Link to Timed Subscription Failed

I looked at the AddEvent SP on MSDN and this came up: http://social.msdn.microsoft.com/Forums/is/sqlreportingservices/thread/ddc439dc-0129-408f-876e-d0ae5dc86a70

“The stored procs are not designed for use by end user programs.  They are not documented or supported.  Have you tried using the SOAP endpoint instead?”

Note that the response from Microsoft is to not use those stored procedures.  The recommendation is to use the SOAP methods instead.  I know SSIS has a web services control to help you.  That would be my recommendation.

Thank you,

Architecture Team

Root Problem (my guess so far)

The link in the automatic email looks like this:

http://myreportURL&rs%3aStoredParametersID=wqjmgv45iazjnbj1bsg&rs%3aParameterLanguage=en-US

Apparently, the StoredParameterID is generated by the AddEvent procedure, and a copy of the report with this StoredParameterID cannot be accessed (or never exist, or never saved) on the reporting server.

Recommendation (from the Architect)

From: The Architecture Team 
Sent: Wednesday, April 13, 2011 12:11 PM
To: Sherry
Subject: RE: Link to Timed Subscription Failed

 

I don’t have any samples.

You can find information about using the Reporting Services web service here:

http://msdn.microsoft.com/en-us/library/ms152787%28v=SQL.90%29.aspx

and the SSIS Web Service control here:

http://msdn.microsoft.com/en-us/library/ms140114%28v=SQL.90%29.aspx

Thank you,

Architecture Team

No solution yet. I will need to continue my search. Stay tuned.

SSRS #42 – Kick off the Timed Subscription Right After Your ETL Process is Finished

April 5, 2011 Leave a comment

You have your SSRS report online, but business users want to receive the report as Excel file attachment in an email as early as possible in the morning.

Creating the Timed Subscription on the reporting server is simple. Even if you’ve never done it before, it’s simple enough that you don’t need instructions to do that.

Kicking off the timed subscription in your ETL process or in a SQL Agent job, however, is not a very well known task. The assumption here is that you have a daily ETL process already in place to refresh the data for your report. Now the challenge is to kick off that SSRS subscription as soon as your ETL process is finished.

Here are some simple steps.

Step 1: create a subscription for your report on the reporting server.

Here are two subscriptions I’ve created for my report, each subscription having different report parameters.

image

  Here is one of the subscriptions. Note that the schedule is a dummy schedule, because the subscription will be not be kicked off by clock. It will be kicked off from your ETL process or from a SQL agent job step.

image

Step 2: To kick off the subscription, here is the command you would use.

EXEC [myRSServer].reportserver.dbo.AddEvent @EventType=’TimedSubscription’,@EventData=’myScriptionID1′
EXEC [myRSServer].reportserver.dbo.AddEvent @EventType=’TimedSubscription’,@EventData=’myScriptionID2′

myRSServer: this will be your reporting services server

myScriptionID1: this is the subscription ID for the timed subscription you’ve created in step 1. In step 3 I’ll show you how to find the subscription ID.

If you have the linked server set up for your reporting services server, you can just execute the above SQL command in a SQL agent job right after your ETL job step. If you don’t need the linked server, you can include the above commands right within your SSIS package.

Step 3: run this query to find the subscription IDs

image

Armed with the above knowledge, you are now ready to automate your SSRS reports and deliver them right to your business users’ inbox with refreshed data daily.

%d bloggers like this: