Brett Romershausen – Applications Consultant
Are you looking for a great reporting solution and are not quite sure where to start? You may already have one and simply not realize it. If you have Microsoft SQL Express Advanced, SQL Server Standard Edition or greater, then you have SQL Server Reporting Services (SSRS) available at no extra charge. With Reporting Services, you get a lot of great ways to present the data to the user. The package allows for a mixture of tables, lists, charts, gauges, and maps. A created report can be used stand-alone, as a sub-report within another SSRS report, or as a linked report. Think of all the possibilities! Besides traditional reports, now you can create dashboard style reports that combine charts and/or reports, chart trends, reports with a table of contents, and reports that allow you to drill down into the details. You can also design your own presentations in SSRS, such as a calendar view we created for a healthcare organization that displays data in monthly or biweekly calendar formats. Reporting Services has some neat features and visualizations built-in. Some include:
- Drop down lists and calendar controls for choosing selection criteria
- Column sorting and grouping
- Expand/collapse toggling
- Use expressions to condition column visibility, table values, text and column formatting, building links to other reports or URLs, etc.
- Link to reports from charts
- Create report templates
SSRS reports can be presented directly to the user with desktop apps, web apps, or SharePoint. Another built-in feature is exporting a report to PDF, Word, CSV, XML, and MHMTL. In addition to viewing and printing a report via the provided viewer, you can choose to schedule and email a report in any of these formats. Here are a few other things to consider:
- Choose which product to use to design reports — SQL Server Data Tools or Microsoft Visual Studio. At Keller Schroeder, we typically use Visual Studio, but the Data Tools product is available to you at no cost if you do not own Visual Studio.
- Before the reports can be utilized by the users, they must be published to the SQL Report Server (included with SQL Server) or a SharePoint site.
- If you have complex queries that are better suited for views or stored procedures in SQL Server, this is possible in SSRS.
- In addition to reporting against SQL Server, SSRS can be used to develop reports against Oracle, MySQL, most ODBC compliant databases, and even SharePoint lists.
An important feature of SSRS is the ability to allow users to filter report data based on selection criteria or parameters. You can insert a dropdown parameter list tied to a SQL table. You can configure optional selection criteria, required criteria, and criteria that allow you to select one or many values. These features are based on a few simple settings when defining the parameters, and then you just need to account for them in your query statement.
There are links to some of my favorite free info for Reporting Services:
If you need help getting started or would like to hear how SQL Server Reporting Services can benefit your organization, please contact us at Keller Schroeder.