What is a Data-Driven Subscriptions (DDS) in SSRS? Let’s start with the definition for a Standard Subscription, which is a time based delivery of a report. The user subscribes to a report to be delivered on a schedule. For example, the daily sales report can be prepared in off-peak hours during the night and emailed to the user at a predefined time. There are several advantages of using a subscription: a) server resources can be better utilized during off-peak hours; b) less stress for the server during the normal hours; c) the user doesn't have to constantly log into the system to review the same report; d) the user can review the report at any time/including offline/, which implies better productivity.
How can we create a subscription in SSRS? In the BI Portal, the user can right-click on a SSRS report, select the “Subscriptions” menu option, and click on the “Create Report Subscription”. Here is a snapshot:
The popup dialog box (shown below) allows the user to configure the subscription. Most of the configuration options are intuitive and self-explaining. For example: a) Description (i.e. Daily Sales Report Delivered at 4am via Email); b) Delivery Method: Email or File Share; c) Recipients email addresses; d) Rendering Format (i.e. PDF, Excel, Word, Image, HTML, etc.); e) Delivery Schedule (i.e. Time, Frequency, Private or Shared); f) Report Parameters values; and more. The options are well documented in the SSRS on-line help.
The BI Portal extends the default subscription capabilities by offering the option of report delivery based on a condition or business rule. The condition is defined either by a) using a statement (which can be configured by selecting the data source and providing the statement); or b) using a report (the UI allows for report selection) – in both cases, the statement and the report needs to return a single cell, which evaluates to either “0/1” or “True/False”). If the value is “1/True”, the report will be sent. This is a powerful feature, which allows the user to receive reports ONLY when a certain condition is met – for example: if the Sales are down 10 percent from last week, then the report will be sent. This will eliminate a lot of unneeded reports, saving the server resources in preparing them and user time in reviewing them.
The final step is for the user to save the new subscription and optionally, test it by clicking on the “Run” button. Hint: all the user subscriptions are listed in the left panel; each subscriptions has a different icon indicating its type. If the icon contains a database image, the subscription is data-driven. The user can also click on the “Refresh” button and check the status of the selected subscription in the right panel at the top “Status” label (i.e. after Running the subscription manually)
The DDS is a variation, which allows the distribution of a given report to multiple users while customizing its content for each user. The user needs to provide a SQL statement, which retrieves a list of users as well as their preferences for the report customization/rendering. Any of the options listed under the Standard Subscription (and described above) can be customized. For example: the SQL statement “select 'User1' as [To], 'PDF' as RenderFormat union select 'admin' as [To], 'Excel' as RenderFormat” can be used to create a DDS, which will target “User1” and “admin” as the recipients, and the report will be rendered in PDF and Excel formats for each user correspondingly. Think of the DDS as a preliminary step, during which the SSRS engine executes a statement and retrieves a record set from a relational database; for each record, the engine create on-the-fly standard subscription using the values in the different fields (i.e. [To], [RenderFormat], [IncludeLink], etc.). Note: For options which are not included the statement, the engine will use the default option provided in the standard subscription definition. The user needs to provide a mapping between the resulting set columns and the subscriptions options.
How can we create a DDS in SSRS? The steps are the same as for a Standard Subscription, but in the context menu the user needs to select “Create Data-Driven Subscription”. The popup dialog and settings are the same as before, except for “Data-Driven Parameters” extra button at the bottom (highlighted in Red in the snapshot below). This button allows the DDS specific options to be configured; clicking it will display the dialog below. The user first needs to select what Data Source will be used for executing the SQL statement/query and retrieving the users’ specific settings. The Query/SQL statement is provided next; it defines what fields/columns will be returned as well as the actual data (which usually comes from a relational DB table). The “Get Fields” button will execute the statement against the “Data Source” and will retrieve a list of fields/columns; this list will be available in the combo boxes in the next section. (Note: The dropped down combo box in the snapshot displays the list of returned columns after clicking on the “Get Fields” button.) The user then needs to provide the mapping between the returned fields/columns/ and the DDS settings/options/. In the example below, only the “To” and “Render Format” settings are mapped/set/ - this basically says: for each record in the returned set, use the “To” column value to get the recipient list, and the “RenderFormat” column value to specify the user preferred report format (i.e. PDF, Excel, etc). The final step is for the user to save the subscription and optionally test it by hitting the “Run” button and “Refresh” to check for errors.
To summarize the DDS execution flow: the SSRS engine uses the selected Data Source to execute the specified “Query”. The returned records are iterated, and for each record, the specified mapping is used to “fill in”/configure/ the currently created subscription – i.e. the “To” column will give the recipients list, the “RenderFormat” column will provide the current user preference, and so on. IMPORTANT: the Data Source MUST NOT require any user input (such as credentials) or any configurations coming from the assumption of an active user; the data source (DS) is used WITHOUT a current user or any user interactions. The engine should be able to open the DS connection directly in “lights-out” fashion. In other words, specify in the DS definition the user credentials – do NOT use “current user identity” settings since there is no current user during the DDS execution!
Everything so far is standard DDS functionality. The BI Portal adds the “Report Delivery Condition”, which is a very powerful feature in controlling when a report will be sent out to a given user; this option is covered above and it’s behavior is the same with DDS. A good example will be the distribution of the “Daily Sales” report to all the sales managers in a company; if the business rule for sending the report is the same one from above, only the managers with declining sales from last week will get the report!
The information so far is a coverage of the Report Subscription and the DDS topics. The blog is about Custom Security and DDS. This is where the BI Portal solution shines.
The DDS is a very powerful feature for the Windows-Domain Enterprise Environment but it’s not very Internet friendly. In other words, it does not work well with Custom/ASP.NET Membership/ security, The BI Portal extends the DDS and makes this feature available for Internet Users. I have covered in a previous blog how our solution offers a custom security with a Single-Sign-On (SSO) for SSRS, SQL, and SSAS. This functionality is extended to cover the DDS as well. When a Data Source is define with the ESS Data Source Extensions for MS SQL and SSAS (and OLEDB Sources), there is an option, which says that “Credentials are not required” – this enables the SSO identity and its flow into the DDS.
When the DDS is executed, each user is impersonated when the data source connection is opened, and the data is filtered using the user’s identity! This customizes the report content to match the security clearance of each user. (Note: the alternative solution are a) to create multiple data sources (with different credentials) and matching reports (not a DDS solution); or b) to use “Expression Based” connection string, or c)… in any case, the resulting solution is very complicated and difficult to support and extend)
Here are some examples of the BI Portal DDS solution at work. The snapshot bellows shows several pre-defined subscriptions; the top three are DDS’ (with the DB image in the icon). All of them have been created with the steps described above. The BI Portal Data Source Extensions are used in the reports so SSO can be used during the DDS. Two users are used in the tests: “User1” and “Admin”. Both MS SQL and SSAS data sources are used by the subscribed reports to demonstrate the data filtering capabilities for the different users.
Let’s start with the “GIS Report” report, which uses the SSAS as a data source ; custom security is in place. When I click the “Run” button, I receive two reports: one for “User1”; and another one for “Admin”. Here is a snapshot of these reports. A you can see, “User1” gets the report as a PDF file and can only see “US and Canada”, while “Admin” can see all the regions and prefers the Excel format. One Data Source, One Report – multiple users served.
The second report is the “Sql Security Test”, which uses the MS SQL as a data source and custom security with SSO. Running the subscription results in two emails with embedded report. Here is a snapshot of the reports. Again, each user only sees the records, for which s/he’s been given security clearance.
The DDS feature requires the Enterprise Edition of SSRS. The BI Portal solutions brings this feature to the MS SQL Standard and Express/Free/ Editions. One of our clients requested the DDS concept but with custom reports; the BI Portal offers ad-hoc reports based on SSAS. We offered a custom service, which prepares and sends ad-hoc and other custom reports (i.e. by “custom”, I mean non-SSRS reports). Eventually, we discovered that the DDS boils down to executing a SQL statement against a Datasource and retrieving a record set. Then for each records (after making the mapping adjustments), we can fire an event in SSRS and accomplish the same functionality offered by the standard DDS. Since we don’t have any SSRS version dependencies, our DDS implementation supports all version of SSRS including the free edition. In other words, we bring DDS to everyone.
In summary, the BI Portal solution offers custom/asp.net/ security for the Data-Driven Subscription in SSRS. With a single identity (aka SSO), the user can access data in both MS SQL and SSAS. There is no need for the administrator to create multiple data sources and reports – one for each security requirement. The data is securely served on the Internet with filtering done for each user or assigned role. The DDS takes advantage of the custom security and the SSO. With one report and one data source, unlimited number of users with different security needs can be served.