What is SSRS and what does it stand for?

SQL Server Reporting Services (SSRS) is a server-based report generating software system made by Microsoft and used as a solution for companies who need to build custom reports from a variety of data sources, such as SQL databases and other external sources, that gives administrators the ability to share reports to users based on access permissions and user groups.

What are Data-Driven Subscriptions in SSRS?

From Microsoft.com:

A data-driven subscription provides a way to use dynamic subscription data that is retrieved from an external data source at run time. A data-driven subscription can also use static text and default values that you specify when the subscription is defined.

To put it simply, a data-driven subscription in SSRS is a scheduled report that automatically pulls data from external sources and compiles the data based on the parameters defined by the administrator.

For example, large organizations typically use data-driven subscriptions to distribute reports to a list of vendors, employees, stakeholders, customers and subscribers.

Moving data back and forth through an organization where systems vary and the dataset is large can very often be extremely complicated and time-consuming at best. SSRS helps by connecting these external data sources and compiling the required reports into an easily readable format that delivers the right information to the right people based on user permission and group access.

What is SSRS used for?

SSRS can be used to prepare and deliver various interactive and printed reports. With SQL reporting services combined with SQL Server, firms have a unified solution for both report design and data warehousing. SSRS can help you to create tabular, graphical and free-form reports from relational, multidimensional and XML based data sources. The reports can also be published and accessed on demand. SSRS also has a built in scheduling tool to perform basic report deployments via email, file share, or SharePoint.

SSRS can be used in different ways and with other systems to deploy business reports. SQL reporting services can be used with business process management tools such as SQL-RD to automate SSRS reports, move data across various databases, and even drive tasks based on events.

The Payables Management department can use SQL server reporting services to generate vendor summary analysis, vendor cash requirements reports and all transaction history details. The Payroll unit may use it for generating and updating employee wage and work hours report, pay history reports and for maintaining earnings register. For the manufacturing units, SQL Reporting Services manages Picking Reports and item standard costs changes history.

Types of SSRS Reports

SSRS SQL Reporting Services

There are many useful types of SSRS reports, each with its own unique way of pulling and displaying data. While an SSRS report can have functionality and characteristics from more than one category, below is the main SSRS report types:


Benefits of Reporting in SSRS

There are seven main benefits of SSRS for reporting:

  1. Easy access to report formats: The reports can be accessed easily from within Microsoft Dynamics GP. There is also a one click access from the user’s personalized ‘My Reports’ list of frequently used formats. In terms of generating the report, SSRS supports a variety of formats including PDF or Excel. Additional formats can be used via plugins or an external tool such as SQL-RD.

  2. Drilldown action: The user can quickly access essential information with the ability to drill down within the reports.

  3. Chart options: SQL reporting services offer various report layout options including pie chart, line chart and bar chart capabilities. These can be used to highlight key information and enhance business presentations.

  4. Customized Filtering: With SQL server reporting services the users can filter report data using dynamic parameters.

  5. Flexible report views: SQL reporting services allow for a collapsible report view to expand sections, reducing complex reports to manageable proportions.

  6. Sub-reports: Users can create sub reports with a main report and also the main report to one or more sub reports through a set of parameters.

  7. Table View: This report layout option quickly presents the data in a table format for better viewing and report distribution across different units of a business.

Many will find that SSRS has a bit steeper of a learning curve, especially for users that transitioning from Crystal Reports. However the benefits of a unified data warehouse and reporting solution add a greater amount of flexibility. Eventually you will have the need to expand SSRS’ feature set by relying on plugins and external tools to add more report formats, distribution methods, and advanced scheduling. If you are beginning to transition to SQL Server as your reporting system, with a bit of learning, you will not be disappointed!

Drawbacks of Reporting in SSRS

Steep learning curve that requires users to know SQL and SSRS-specific functions. Without a highly technical person on your team or an IT department, using SSRS to its full potential will be largely out of reach.

Somewhat limited ability to display data graphically. Although, Microsoft has recently updated this in the lastest release and is now incorporating SSRS into Power BI in order to marry the interactive graphical display of Power BI reports with the paginated reports found in SSRS.

SSRS means you have another server to maintain. Microsoft SSRS requires a Windows server hosting environment, which adds to the complexity of IT support.  

Limited automation or mass reporting features. SSRS was built more as a self-service reporting tool — not a report automation tool. While the Enterprise edition does have mass reporting capabilities, the feature set is small in comparison to what other tools are able to accomplish with SSRS.

For example, even with the Enterprise addition, there are limitations:

  • No event-based scheduling
  • No way to password protect exported reports
  • Cannot automatically combine Excel workbooks and PDFs

These limitations dramatically change how easily you can distribute reports across your organization. For example, what if you needed to send reports based on specific events e.g. database record has changed, new order has been created, unread email has been received, and have these reports sent directly to Printer, DropBox, Fax, Slack and FTP? What if you need to send certain reports to a specific set of users via  Google Sheets? What if you need to email 50 Excel based reports, but need the Workbooks combined instead of emailed separately? This cannot be done, even in Enterprise.

And these features do not even exist in Standard.

Fortunately, SQL-RD can accomplish all this and more, even with a Standard SSRS license. For a full list of features and to download your free 30-day trial, click here.

Costs increase with number of users. While SSRS is a great product and does a great job streamlining data across organizations, the biggest reality is the enormous investment it requires to realize its full potential.

Microsoft uses two pricing models for SSRS: Standard and Enterprise.

SSRS Pricelist on Microsoft's website

As you can see, the listed price for Enterprise edition starts at $14,256 — but is this the actual price you pay?

If you drill into the fine print, you’ll see that the costs are actually quite a bit more.

As the fine print states, “Editions sold in the per-core licensing model are sold as 2 core packs.” Your $14,256 investment is actually a minimum of $28,512 per year in licensing costs — assuming your server has only 2 cores.

If you refer to Microsoft’s SQL Server 2017 licensing guide, you’ll see that installing on a server will more than likely require at least four licenses.

This brings your yearly costs from $28,512 to $57,024.

Of course, you can skip these costs and opt for the per user license, but keep in mind that the Standard Edition is missing nearly all of the features that allow some level of automation and completely eliminates data-driven subscriptions.

How to Automate Report Distribution in SSRS

Do not let the costs from persuading you from seeing SSRS as a worthwhile reporting tool for your organizational needs. Its ability to connect disparate data sources and effectively compile and send data to the right users at the right time give it enormous value to CEOs and stakeholders with a need to have absolute control over the dissemination of data across their company.

There is a straightforward solution to minimizing the costs and dramatically expanding the features and functionality of SSRS, even if your organization only runs SSRS Standard Edition. If your organization already uses Enterprise, you can still easily expand the functionality to every user in your organization.

 

SQL-RD® by ChristianSteven Software is a dynamic, flexible, function-rich and intuitive automation tool for scheduling, exporting, distributing and delivering your Microsoft® SQL Server Reporting Services (SSRS) Reports to unlimited users.

The need to deliver reports in the best format for your audience is critical to streamlining data consumption and putting the right information in front of the right people at the right time. Organizations can quickly realize the benefits of SQL-RD by automating repetitive reporting tasks — allowing administrators to create and distribute reports without error and without the costly support time.

SQL-RD is able to send scheduled reports by:

  • Printer
  • Fax
  • Folder
  • FTP
  • SMS
  • DropBox
  • Slack
  • Google Sheets
  • SharePoint
  • Email

The available formats include Excel, Word, PDF, Txt, CSV, PNG and more.

SQL-RD is the leader in SSRS automation and report delivery, containing powerful system, event triggered, data-driven and business process workflow functions which will make an instant impact on the efficiency of your business.

Over 1,000 companies in more than 46 countries trust ChristianSteven Software for their report automation needs. Download your free trial of SQL-RD today.

START YOUR FREE TRIAL