I recently found the need to create a custom reporting service subscription manager for my workplace. We currently employ SQL Server Reporting Services to generate and maintain our reports for our end users, and after a little digging around I uncovered the ReportingService2010 web services exposed by SQL SSRS - http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.aspx. I wasn't aware of it at the time, but the way Microsoft SharePoint and so many of these other Microsoft Services interact is all via web services. When SharePoint provides links to manage shared schedules or generate reports, many of the functions they provide access to are just web services enabled by the service you are interacting with. This means that you can access the same data and functions that is provided "out of the box" by many software packages, if you're willing to slap on your own UI and learn how to interact with the appropriate API's. This post is to discuss the latter half of that statement and introduce you to working with the ReportingService2010 SOAP enabled web services.
Let's start here, the ReportingService2010 Class embodies many of the methods and properties used to work with both Reporting Services Native Mode as well as SharePoint Integrated Mode. The examples I'll provide below will be aimed towards a SharePoint Implementation, but in browsing the MSDN documentation, you'll see that many of the methods are the same, the differences are typically in what parameter data you may be passing back and forth.
So let's get started. Before you do anything else to work with your Reporting Service API's, in your Visual Studio Solution generate a web service reference to your SSRS server. The format to connect to your service should be along the lines of: http://_Server_Name_/ReportServer/ReportService2010.asmx?wsdl. Once created, add your namespace to your Using\Imports statement on your class files.
Now that that's done, let's look at some of the common methods you may need to use. Anytime you need to interact with your reporting server, you'll need to authenticate. An easy way to do so is by using the following:
Dim rs As New ReportingService2010
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
What will this do? At runtime, the credentials will take on the credentials of the user running the application. If this is a website for example, this will be the service account assigned to the application pool in IIS. However, if you enable identity impersonation (http://msdn.microsoft.com/en-us/library/aa292118(v=vs.71).aspx), then the DefaultCredentials will be of the user accessing the website.
This can be a very handy feature, but in my implementation, I also found the need to create new subscriptions, or edit\delete existing subscriptions. For something like this, unless all of your users are "power" users in SharePoint (which I assume they're not), you can also specify a particular user account to run under. You can do so by creating a new NetworkCredential object, like so:
rs.Credentials = New System.Net.NetworkCredential("<UserName>", "<Password>", "<DOMAIN>")
This feature was the most important feature I had worked with, because there are times that you *need* to perform actions under a higher privileged account (like create or delete subscriptions), and there are times that you just need to read data for the current user. Keep this concept in mind as you begin to interact with your SSRS implementation.
Note: Herein, any reference to the ReportService2010 object will be referenced as if it has been “defined” as rs.
SSRS - Working with Reports, Subscriptions and Schedules
Let's start simple. Let's say you want to programmatically access a report. Each Report is referred to as a "CatalogItem" object (http://msdn.microsoft.com/en-us/library/reportservice2010.catalogitem.aspx). To get your reports, just call the ListChildren method of your ReportingService2010 object, providing the URL to the SharePoint library that contains your items. Here's such an example:
Dim r As List(Of CatalogItem) = rs.ListChildren("http://mySharePointSite.com/Report Library", False).ToList()
The first parameter is the URL where your RDL reports are located, the second boolean parameter defines whether or not the result set should return recursive items. Otherwise, it's as simple as that. Now you'll have a List of reports where you can determine when the item was created, it's name, description.. all that good stuff.
Chances are you grabbed that line of code, pasted it into your IDE and ran it, and hopefully it worked just fine for you. However, I want to make sure you are aware that the ListChildren method will return results based on the context of the Credentials in your ReportService2010 object. So if user's don't have permissions to an Library, or an object in that Library, they will not receive results for the ListChildren call.
Let's say you want to have access to what Shared Schedules you created on your site, here you can receive a List of all Schedules currently stored using the ListSchedules method:
Dim schedules As List(Of Schedule) = rs.ListSchedules("http://mySharePointSite.com").ToList()
Notice I didn't specify a particular report library or anything like that. Since schedules are stored at the site level, and not within a particular library or anything like that, you only need to specify the location of your SharePoint site where you defined your schedules.
ListSchedules returns Schedule objects, defined here: http://msdn.microsoft.com/en-us/library/reportservice2010.schedule.aspx. The Schedule object offer's a number of very helpful properties including the Last Run Time, the Next Run Time and the Description of the schedule. However, the most important property exposed here is the ScheduleID, a GUID based value that allows you to reuse the schedule at a later date. More on this value later.
Let's say for a moment that we want to determine what parameters a report can accept. This is done with the GetItemParameters(http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.getitemparameters.aspx) method, as shown below. This method will return an array of ItemParameter objects (http://msdn.microsoft.com/en-us/library/reportservice2010.itemparameter.aspx) and contains pertinent information including the display text for the parameter, the prompt text for the parameter, default values (if any) and *acceptable values* (if any)... which is very important when creating new subscriptions using parameters, as the SOAP API's will thrown an exception if a parameter is used with a value that is not within this list of values. To call this method, you just need to pass the report path for your report, including the file name with the rdl extension. You also have the option to specify a "historical" report (incase you want to check a report snapshot), a boolean based "ForRendering" parameter, an array of ParameterValues that can be validated against the report upon calling the method, and a DataSourceCredentials object that can be used to validate any query based parameters. However, if you simply wanted to just get the Parameters for a report, the following code will do so for you:
Dim reportPath As String = "http://mySharePointSite.com/Report Library/SomeReport.rdl"
Dim forRendering As Boolean = True
Dim historyID As String = Nothing
Dim values As ParameterValue() = Nothing
Dim credentials As DataSourceCredentials() = Nothing
Dim parameters As ItemParameter() = rs.GetItemParameters(reportPath, historyID, forRendering, values, credentials)
OK, we can call SharePoint and return some report names and shared schedule information, which is cool, but let's take it a step further. My need to work with these API's was to enable some usability with creating and editing subscriptions. We can access subscriptions using the ListSubscriptions method, which only requires the path to your report library, along with the name of the report you are requesting access for. ListSubscriptions returns a dataset of Subscription objects, which is defined in detail here - http://msdn.microsoft.com/en-us/library/reportservice2010.subscription.aspx The most important property exposed here is the SubscriptionID. Querying your SSRS server using the SOAP API's will require a subscriptionID in most cases, and further down in this post you'll see where\when this comes in handy. If you perform the following, you'll get a List of all subscriptions for the report *based on the user context* - so if you want all subscriptions, use a priveleged account. Otherwise, the subscriptions will be based on who is running the command:
Dim subs As List(Of Subscription) = rs.ListSubscriptions("http://mySharePointSite.com/Report Library/SomeReport.rdl").ToList()
At this point, our List will contain Subscription objects, but what about the data they contain? The Subscription object contains data about who the Owner is, or the Path of where the Report file is located, but that isn't too insightful. If we want to learn more about the specifics of a Subscription, such as who is receiving report data, what its current state is, or who the owner is, among a plethora of other details, we use the GetSubscriptionProperties method. Let's start simple, I'll give you some code to look at, and then explain what this all is.
Dim subID As String = "<Some Sub ID>"
Dim subExtensionSettings As ExtensionSettings
Dim subDescription As String = ""
Dim subActiveState As ActiveState
Dim subStatus As String = ""
Dim subEventType As String = ""
Dim subMatchData As String = ""
Dim subParams() As ParameterValue
Dim subscriptionOwner as String = rs.GetSubscriptionProperties(subID, subExtensionSettings, subDescription, subActiveState, subStatus, subEventType, subMatchData, subParams)
The GetSubscriptionProperties method, which is defined here http://msdn.microsoft.com/en-us/library/reportservice2010.reportingservice2010.getsubscriptionproperties.aspx, returns a string value indicating who the current *owner* is for the provided Subscription. Consider the owner as who the report runs "as" (which was a handy concept for me, as many of my subscriptions do take into consideration who the user is to provide certain data). As far as the other parameters are concerned, these are all output parameters populated based on the result of the API call. So in reality, all you need to retrieve information here is just the SubscriptionID, the rest gets populated.
The true knowledge of the Subscription information is contained within these returned values, of course, so let's walk through them.
subExtentionSettings is an ExtensionSettings object (http://msdn.microsoft.com/en-us/library/reportservice2010.extensionsettings.aspx), which is rather boring as it only contains an array of ParameterValues and an Extension string property that defines what the report subscription is. As an example, for email based reports, Extension is set to "Report Server Email". ParameterValues is a collection of key\value items that contains the basic information about a subscription, including the following:
- TO
- CC
- BCC
- ReplyTo
- Subject
- Comment
- Priority (Low, Normal, High)
- IncludeLink (Boolean)
- IncludeReport (Boolean)
- RenderFormat (The report file type that is attached to the email. Possible values are WORD, PDF, ATOM, RPL, IMAGE, CSV, EXCELOPENXML, HTML4.0, WORKOPENXML, MHTML, EXCEL, and XML)
subDescription is just a string typed description of the subscription, such as "Send e-mail to someuser@mycompany.org".
subActiveState is an ActiveState object(http://msdn.microsoft.com/en-us/library/reportservice2010.activestate.aspx). As indicated in MSDN, this object "Represents information about the active state of a subscription. An ActiveState object is returned by methods that query subscription properties. The active state of a subscription indicates whether a subscription is currently active and valid. If there is a problem with the subscription, the active state of a subscription can be queried programmatically to determine the cause of the problem. More than one error condition can cause a subscription to be inactive. Subscriptions that are inactive are not processed by the report server until the cause of the error is resolved.". When creating a new subscription, an object of this type needs to be passed, but it really is to determine the current "state" of a subscription when a subscription fails for some reason, not so much to provide input parameters.
subStatus represents the most recent status of the subscription. For example, a successful emailed subscription will read "Mail sent to someuser@mycompany.org".
subEventType represents the type of subscription you are querying. There are 2 main types, a Timed Subscription, and a Data Driven Subscription. The EventType parameter will indicate what type of subscription this is.
subMatchData is a string based parameter, but it will provide 2 different types of values, either a GUID or an XML string. The difference here is whether or not you are using a shared schedule for this subscription. If you are, then the GUID value provided is the ScheduleID of the Shared Schedule (meaning you can use this to match up with a ScheduleID from the ListSchedules method discussed earlier). If you receive XML, it is because the subscription is using a "custom" schedule, and the XML defines what days of the week the report "event" should occur, along with what time.
subParams is an array of ParameterValues (much like those defined above in our ActiveState object). These values however are the input values that are used to generate a report. So when you are interacting with a report, and you are requested for a start date, or an end date, or a user ID, or a customer number, etc. these values are all found here. There is one exception to this rule, and that is default values. If a report can accept "default" values, and the value for the parameter was chosen as default when the report is created, then this value will not exist in the ParameterValues array, as it is simply not stored. Let me repeat this, the value isnt stored as "NULL" or "BLANK" or "DEFAULT", it is simply *not there*. If you want to retrieve a list of available parameters for a report, you would use the GetItemParameters method (discussed above).
Creating A New Subscription
OK, with all of the knowledge attained above, the last item I'll cover is creating a new subscription, which basically uses a combination of the properties and objects defined above.
First, your ReportingService2010 credentials object must be a privileged account to create a subscription. In my case, I defined a "service account" on my domain to perform such actions, and allow this service account to create subscriptions on behalf of a user. However, the creator of a subscription is also the default *owner* of the subscription, and as we discussed earlier, the owner is who the report runs *as*. Keep this in mind, as you may want to alter who the owner is after creating the subscription (which is simple!).
Creating a subscription is simple, all you need is the correct data to generate the subscription in your system, namely the report your creating a subscription for, who it's to be emailed to, the attachment type, a description, an event type, the schedule to send the report, and the parameters for the report (all this sound familiar from above?)
My code to generate a new subscription is similar to the following...
'DONT COPY AND PASTE THIS CODE, IT WONT WORK! Keep reading below to understand why and what you need to do..
Dim reportPath As String = "http://mySharePointSite.com/Report Library/SomeReport.rdl"
Dim newSubExtensionSettings As ExtensionSettings = GetEmailSettings()
Dim newSubDescription As String = "Send e-mail to someuser@mycompany.org"
Dim subActiveState As New ActiveState
Dim subStatus As String = "New Subscription"
Dim newSubEventType As String = "TimedSubscription"
Dim newSubMatchData As String = "<Some Shared ScheduleID>"
Dim newSubParams() As ParameterValue = GetReportParameters()
Dim subscriptionID As String = rs.CreateSubscription(reportPath, newSubExtensionSettings, newSubDescription, newSubEventType, newSubMatchData, newSubParams)
As you can see in the comment, that code wont work above for 2 reasons, and it's really because I'm avoiding code bloat here. newSubExtensions is looking to call a custom method called GetEmailSettings. All this does is create a new ExtensionSettings object, and populate the ParameterValue array with the appropriate data. What is the *appropriate* data? Scroll up above, it's a list of TO, CC, Subject, etc. as defined above. These are just key\value pairs for the subscription, nothing more.
newSubMatchData should pass either formatted XML or a ScheduleID of a shared schedule on your SharePoint site. For my application, I just created shared schedules and ensured that all users only had the ability to use a shared schedule and NOT any custom subscription times, just to make maintenance periods that much easier.
Finally, newSubParams calls GetReportParameters, returning an array of ParameterValues. Much like the ExtensionSettings, you just need to generate some key\value data that aligns with the acceptable parameters of your report (found by using the GetItemParameters method, described above.) So my custom GetReportParameters method simply has some logic to iterate through my form, grab the appropriate data, and generate this array of values.
Once you have all this data populated, and you call the CreateSubscription method, SSRS will attempt to generate a subscription. If any parameter values are incorrect, or if any other data seems invalid, you'll get an Exception. If it works, a string is returned, which is your SubscriptionID, and it'll look like a GUID.
The last thing you may want to do (I did it, but it's theoretically optional) is to update the subscription owner, for reasons defined above. Doing so is very simple, just call the ChangeSubscriptionOwner method, like shown here:
Dim subID As String
Dim ownerName as String
rs.ChangeSubscriptionOwner(subID, ownerName)
Told you it was simple!
Updating An Existing Subscription
At the present time, you can't update a subscription (unless I missed it somewhere in the documentation - http://msdn.microsoft.com/en-us/library/ee640242.aspx). So, when I need to update a subscription, I just delete the current subscription, and then recreate a new one. Deleting a subscription is as simple as executing the line below:
rs.DeleteSubscription(currSubID)
That's everything in a nutshell, hopefully this gives you a solid anchor point to develop your own custom report subscription application... hope this helps!
I got a variety of looks and responses as I did this. The more frenzied looks and responses came, obviously, as the date got closer and closer to the number “24”. Most responses were “oh I haven’t even started yet”. A few involved something along the lines of “I need to bake, so hopefully I get that done soon”. But one gentleman I spoke to said “I’m done.. I don’t shop”. Obviously, he was the most peaceful of all respondents.
