ChristianSteven BI Blog

Everything You Need To Know About SSRS Subscriptions

Written by Christian Ofori-Boateng | Jun 10, 2020 5:57:07 PM

Automate, Schedule & Deploy SSRS Data Driven Subscriptions

Hundreds of companies of varying verticals and sizes rely on SQL Server’s reporting system. Known as SQL Server Reporting Services (SSRS), it is used to design and generate reports on a scheduled or adhoc basis. SSRS has a basic scheduler designed for simple automated report distribution, but what if your business requires complex event driven and data driven subscriptions? What if you not only needed to automate SQL reports, but you require tasks to be automated such as database updates or system alerts? In the first part of this blog, I will discuss how SSRS automated report generation, Data Driven Subscriptions, and business process automation using PBRS can solve this problem.

Automated Report Generation

PBRS goes beyond the report scheduling capabilities of SSRS to offer a comprehensive business process management engine. This automation scheduler can drive reports based on changes in a database, the existence of files, or based on time. PBRS can data-drive SSRS reports regardless of your edition of SSRS. Moreover, PBRS’s data driven and dynamic features enable report parameters to be auto populated from databases at runtime and sent to a dynamic list of recipients. This means that rather than creating multiple subscriptions for an SSRS reports, the SQL Server admin only needs to create a single Data Driven Subscription or Dynamic Subscription.

Business Process Management

With PBRS, you can do more than automate SSRS reports. Business process management is a key component of PBRS’s functions. As an automation scheduler, PBRS can perform simple tasks such as running programs or moving/deleting files, as well as complex tasks such as triggering SSRS packages, running SQL Scripts, and updating databases. PBRS can even execute queries, format them (in excel, csv, etc), and then deliver the exported query to a specified destination!

PBRS Case Study

Combining report scheduling and task automation enables users to create complex workflows. PBRS has been employed by numerous companies to automate processes such as order fulfillment, sales dispatch, and automated invoice delivery. Zintel, a leading communications firm in New Zealand, relies on PBRS Formerly SQL-RD) for their call reporting. PBRS enables them to deliver thousands of reports with uniquely customized formats. With this enhanced report delivery system, Zintel not only saved over $100,000 dollars in time and development costs, but overall customer service quality has been improved.

PBRS provided reliable and automatic delivery of over 50,000 reports a month at customers’ preferred frequency and format. Zintel has established a unique, differentiated service that adds value, making it easier to acquire and retain customers.

With PBRS operating these processes, businesses can spend more time adding value to their internal and external customers. Firms with a focus on both cost reduction and efficiency often turn to PBRS not only to automate SSRS reports, but to optimize all parts of their business.

See how we can help your organization today.

Get Features PDFStart Free Trial

Learning And Understanding The Different Types Of SSRS Subscriptions

SQL Server Reporting Services (SSRS) is a very powerful Business Intelligence tool which can simplify your company’s business reporting needs. Customers deploy SSRS on their own premises for creating reports and then delivering them to the decision-makers in their organization. In scenarios where multiple users are using the SSRS reports with different needs, automation and subscriptions become a necessity.

SSRS Report Subscriptions

An SSRS subscription is a report snapshot generated on some pre-defined scheduled time and delivered to the recipient by the report server. SSRS allows you to create two types of subscriptions, i.e., standard subscriptions, and data-driven subscriptions. Having this kind of flexibility in SSRS subscriptions caters to all businesses and helps in their decision-making.

When to use Standard SSRS Subscriptions

Standard SSRS subscriptions are created and managed by individual users. A standard subscription consists of static values that you cannot change during subscription processing. Standard SSRS subscriptions produce one instance of a report. This report is then delivered to a single shared folder or to the e-mail addresses specified in the subscription. The report format and data do not vary. When the SSRS report uses parameters, a single value for each parameter is used to generate the report.

When to use Multi-value Parameters in SSRS Subscriptions

With Data-driven SSRS subscriptions, the system gathers information at run-time by querying a data source that provides values which are used to specify a recipient, report parameters, or application format. You can use data-driven SSRS subscriptions if you have a significantly large group of recipients or if you want a different report output for each recipient.

Allowing users to select multiple values from a parameter list is a great value-added feature in SSRS. The capability to enhance SSRS reporting with multi-value parameters provides users with more flexibility. Analysts can bring more efficiency into business intelligence reporting by not having to run a different report for different parameter values separately.

ChristianSteven Software’s PBRS is a dynamic automation tool for scheduling and delivering your SSRS reports. Many customers find PBRS very intuitive and easy to use because it was written specifically for a Microsoft SQL Server. PBRS is also very simple to integrate and number one choice for your business reporting needs. Don't wait to get started. Start your free trial today!

Benefits of SSRS Data Driven Subscriptions

SQL Server Reporting Services (SSRS) allows your company to view data that improves your Business Intelligence. Unfortunately, most of the licenses are expensive and narrow in scope. Even worse, they give you an ‘SSRS data driven subscription not available’ notice. Our SSRS Data Driven Subscription software, SQL-RD, allows you to use this feature even on a SQL Server Standard Edition license.

Viewing and Sending Reports

Our SSRS Data Driven Subscriptions allows you to communicate your reports with shareholders and clients without exposing your underlying database. Each report can be tailored to fit a specific group, relaying the relevant information in an easy-to-read format. You can also allow your admins and designated users to run reports on their own, empowering them to access the data they need to succeed. Get the right information into the right hands quickly and seamlessly with our SSRS data driven solution.

Stunning Formats

When it comes to presenting information, you need formats that capture the attention of your audience. Our SSRS data driven software is designed to present all your data easily and effectively. You have the freedom to customize your reports as needed, adding color and style to fit your preferences. Make your reporting stand out to your users so that they can absorb and use the information seamlessly. Combined with dynamic filtering and search capabilities, reporting has never been easier.

Quick Collaboration

Each report can be customized to view certain data (using parameters and inserts), which can then be shared with the people who need to be informed. Using customized reports guarantees that you send the right information to the right people, without having to sort through the white noise. Automate report delivery in SSRS and send the reports to the printer, fax, folder, FTP, SMS, DropBox, Sharepoint & email in a number of formats including Excel, Word, PDF, Txt, CSV and more. Our SSRS solution contains powerful system, event triggered, data-driven and business process workflow functions which make an instant impact on the efficiency of your business.

An SSRS Data Driven Subscription saves you time and money by allowing you to view all your reports in one space. Make reporting easy and effective with beautiful customizable reports, and send the reports to the people who want them.

Get Features PDFStart Free Trial

PBRS Can Serve As An Advanced SSRS Report Manager

Not only is PBRS a handy tool for scheduling SQL Server Reports, it can be a great way to manage SSRS report subscriptions. Many PBRS users have enjoyed the ability to schedule SSRS reports with expanded options such as report encryption, additional formats, and more destinations such as SharePoint & Dropbox. In addition to the advanced subscription distribution features PBRS has to offer, it can also act as an SSRS report manager. Let’s look at three ways PBRS can help SQL Server admins manage their SSRS subscriptions.

SSRS reports from multiple environments can be managed in a single interface

As PBRS is in an external tool to SQL Server and SSRS, admins can set up report subscriptions that pull reports from multiple report service URLs. Why would you need multiple Reporting Services environments you say? Many firms have segmented reporting organizations, with separate databases, and disparate systems. PBRS enables the “unification” of these systems’ reporting without the need to fully integrate them. In other cases, some firms have separate SSRS instances for each of their clients. With PBRS, all report instances can be managed from a single interface. As an added bonus, reports from multiple environments can be batched and scheduled to be sent to recipients.

Multiple versions of SSRS can be managed from one location

Various parts of the firm using different versions of SQL Server? Rather than having to rewrite or migrate reports to updated versions, or manage each version separately, PBRS can manage the various report subscriptions from its own system. Moreover, multiple subscription versions of the same exact SSRS Report can be created in PBRS. Meaning that the same SSRS report can be run with different settings (i.e. parameters, database credentials, output format), without the need to edit the SSRS report, or create a new subscription in SSRS.

Additional management layers allow for more flexibility

Indeed SSRS’ subscription management system can handle most needs. However, there are many cases where adding PBRS as a SSRS report manager layer can make the experience much more flexible and efficient. In some large organizations access to manage subscriptions is simply not allowed. A group may only have access to a few dozen reports, but they do not have permission to create subscriptions for the reports, or schedule them in SSRS. Using PBRS, reports the department has access to can be managed by that department only. The said department can create subscriptions against that report and send the report to whom they see fit. Users can create their own subscriptions in PBRS as well. They can only create subscriptions for reports they have access to, and can create settings behind that report that the administration allows. This extra layer allows admins to fine tune their management policies, while still being flexible to the needs of departments and individuals.

With its ability to manage SSRS subscriptions from various SQL versions, SSRS reporting environments and increased options in determining distribution methods and formats, PBRS can make subscription administration flexible and easy. Moreover, subscription management can be expanded to more users, without violating the integrity of existing policies. Though PBRS is by no means a replacement for SSRS, it augments and expands SQL Reporting Services capabilities.

Do You Know How To Schedule SSRS Conditional Subscriptions?

We have published a number of guides for scheduling SSRS reports to email, or data driving SSRS reports. Now, let’s review how to schedule SSRS conditional subscriptions. Using Event Based Schedules in PBRS, you can build complex workflows that can trigger RS reports based on events or conditions, for example database record has changed; email has been received; file has been updated, etc. You can monitor for these conditions at certain intervals, or in real-time. If these conditions are met, then it will run specified reports and even BPM tasks. Using PBRS to schedule SSRS conditional subscriptions is actually quite easy. The real challenge is working through exactly how to structure the schedule and the tasks.

Let’s first start off with an example. Robert is the reporting admin for the Accounts team. A mandate has come down from the managing director with a goal to improve cash flow by 25% within the next quarter. What can the Accounts team do to contribute to this goal? Collect invoices faster. Using PBRS, as invoices are due, the Event Based Schedule can send them to customers immediately instead of waiting on the Accounts team to perform their weekly run. Moreover, if invoices are past due, the team can be immediately notified along with the customer. This is where you can schedule SSRS conditional subscriptions with the Event-Based Schedule.

Event-Based Schedule

To set this up in PBRS, first, start an Event Based Schedule. Name this schedule and provide a description. Hit next to continue to the conditions step. The conditions screen is where you will establish the criteria for your reports and tasks. You can have multiple conditions. There are a variety of conditions including if a database record exists/modified, if a file exists/modified if there is an unread email or even data on a port. Since we are looking for due invoices, we will monitor a database for new records. Simply write your query to a table or view. If the event based schedule returns any records (based on your criteria), then the schedule will trigger your task.

Report Properties-Parameters

When you’ve finished creating your conditions, click next. You have the option to trigger a report, an existing schedule or a task. In this case, we will trigger a new. Select the report that must be triggered. Using the table/view PBRS monitors, the report can be run given data found in each record. In the parameters tab, drag and drop the Event Based data inserts into the appropriate fields. In this case, our table would have invoice number parameter, and my table has an InvoiceID field in my view/table. Once done, PBRS will run a unique report for each record returned in our condition; each report will run with associated InvoiceID.

Setting the Destination

Next, set the destination for the report. In this case, we are sending the invoice to the relevant customer that has the due invoice. Just like configuring the parameters, drag and drop the Event Based data insert into the “To” field of our email. This ensures that the specific due invoice will be delivered to the corresponding email address. You can customize the email as well. If the fields exist in the table/view PBRS is monitoring, then you can customize the body of the email with the recipient’s name, or even point of data. For example, if the invoice is overdue, the email can include how many days the invoice is past due, or even how much is owed. Based on your data, the invoice can be sent to additional addresses or different destinations such as Fax. Finish the destinations, then add any additional tasks such as updating the database reflecting that the notification was run.

Now you have a schedule that will constantly watch for invoices that are due or past due. Customers that are more quickly notified about their account status will more than likely pay much more quickly. The faster invoices are paid translates directly to increased cash flow.

 

Get Features PDFStart Free Trial