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.
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.
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
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.
DataSource Tables from the ReportServer Database
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.
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.
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
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
The application has a screen for searching subscriptions and detail screens for
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.
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.
Catalog Detail Screen
This is a standard Details screen – see Figure 7. It displays the
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.
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.