What is the Problem
SQL Server Reporting Services (SSRS) subscriptions can be sent to e-mail distribution groups or individuals. Distribution groups are desired because you can add or remove people getting the report by maintaining the distribution groups. However, sometimes using distribution groups isn’t practical. So if a person getting a report is replaced or for some other reason you want to change the subscriptions for a person, it is difficult to determine which report subscriptions need to be changed. It would mean you need to open the subscriptions for a number of reports to determine if you need to change them or not.
Background
Information on SSRS reports and their subscriptions is stored in the ReportServer database. The Catalog table stores the information on the report. The Subscriptions table stores the information on subscriptions. The two are related via the report ID from the Catalog table. This is a foreign key relationship defined in SQL Server.
The subscription information is stored as an XML string in the ExtensionSettings field in the Subscription field. The various parts of a subscription, e.g. To, CC, BCC and other values are stored as <parameter> values. See Figure 1 for an example of a complete set of settings as stored in the ExtensionSettings field. So if we want to find subscriptions sent to an individual, we could search the ExtensionSettings fields for that name.
Figure 1
The Solution
To address this problem, I created a LightSwitch application to search the SSRS subscriptions for a specific name. The application will have a screen for searching subscriptions and detail screens for Catalog and subscription entries. I also added the ability to open the report from the LightSwitch application so you can make the changes in the subscription(s). This solution has made it much easier to find and change subscriptions when needed. It eliminates the wasted time of opening a subscription just to find it doesn’t include the name you want to change.
The Application
DataSource Tables from the ReportServer Database
Catalog Table
The only fields in the Catalog table we want to display are the Path (full hierarchical path to report), Name and Description of the report and the Subscriptions collection. We will also want the Subscriptions collection that is created by the relationship to Subscriptions. I found out that if a field is checked as searchable, it is included in a search even if it isn’t displayed on the search screen. Therefore you will want to uncheck Is Searchable on all the other fields. This is particularly important on the LastStatus field because this contains the information about the last subscription sent and may contain people’s names even if they aren’t in the current subscription. As you may have guessed, I found this out the hard way when I had false hits on a name. See Figure 2 for the settings you need to make.
Figure 2
I also added two calculated fields to get a URL to the report. The first field ReportServerLink is basically a constant value that is the URL to the report server. I chose to separate this out from the next field for clarity and ease of change. The second field is LinkToReport which gets the full URL to the report being viewed. See the code for both of these calculated fields in Figure 3. Along with putting the ReportServerLink as a prefix to the path, it does translation of the / and space characters to the way they are specified in a report URL. In order to provide the ability to open this report from the LightSwitch application, I am using a custom Web Address business type developed by Alessandro Del Sole. Search the VS Gallery for “Web Address Type for LightSwitch” to get this custom business type. There are also some other similar business types available in the gallery. The LinkToReport field has to be specified as a Web Address Type.
Figure 3
Subscriptions Table
The only fields in the Subscription table we want to display and search are the Extension Settings and the Catalog collection created by the relationship to the catalog table. Looking at the Catalog and Subscriptions tables, you will notice the relationship between the two is shown in the designer. I also added a calculated field that is the formatted settings. A carriage return-line feed (new line) is placed before each <ParameterValue> string so that each parameter is displayed on a separate line making it much more readable. This formatted field (called FormattedSettings) is what is always displayed on the screens. See Figure 4 for the code for the FormattedSettings field.
Figure 4
Screens
The application has a screen for searching subscriptions and detail screens for Catalog and subscription entries. All screens are read-only so I have removed the edit and save buttons from all command bars. I chose not to allow changes to the subscription since I didn’t fully understand what else may happen when you change a subscription. If you are brave, you could change this to allow editing.
Search Subscriptions Screen
This is a standard search screen – see Figure 5. The formatted settings, Catalog path (as a link) and Subscription ID (as a link) are the fields displayed. Since the formatted settings field is multi-line, I have set the height to 5 lines as shown in the figure. You can enter part or all of an e-mail address to find the subscriptions that include that e-mail address.
Figure 5
Subscription Detail Screen
This is a standard Details screen – see Figure 6. It displays the formatted setting and the catalog path as a link. Since the formatted settings field is a multi-line field I have set the height to 25 lines. The main reason for this detail screen is to be able to see the entire settings.
Figure 6
Catalog Detail Screen
This is a standard Details screen – see Figure 7. It displays the Path, Name and Description field. It also displays the calculated LinkToReport field that is the full URL to the report. This field is set as a WebAddress Viewer business type so it acts as a active hyperlink. Clicking on it will open the report in a browser where you can go to the subscription(s) and make the changes needed. I also included a grid of the subscriptions to this report as a convenient way of seeing that there are multiple subscriptions to the report.
Figure 7
Summary
As you can see, LightSwitch allowed me to build an application that makes my job easier and saves me time. I was able to build the application very quickly (less than half a day) and I needed to do only very limited coding to get the values for the calculated fields. Actually, the hardest part was the research above where and how SSRS stored the report and subscriptions information. Doing this in a standard VB.NET application would definitely have taken longer. Doing this using just SQL Server views and ad-hoc queries would not have provided as much information and been harder to use.
