MS SQL Reporting Services
To discuss or ask questions about this article, select the discussion tab above.
Overview
Microsoft SQL Reporting Services (SRS) is a free add-on to MS SQL Server. It was first introduced for SQL Server 2000 as an additional install CD. With SQL Server 2005, it is now included as an option during a typical install. Depending on the edition you use, you can develop your reports using the Business Intelligence Development Studio (BIDS). BIDS is a full integrated development environment for building and debugging data integration, OLAP, data mining, and reporting solutions.
In many aspects, developing reports in SRS is like developing reports in MS Access. The interface is similar. The underlying programming capabilities is similar. But where SRS really shines is on the server side. After creating a report in Business Intelligence Development Studio, the report is published to the SRS server. That report is now available via the reporting web interface or via any application that makes the correct call to the SRS server. From the web interface, users can either run the reports interactively, or schedule to have a report delivered via email on a recurring basis.
Starting with SQL Server 2005, Reporting Services includes a reporting feature called Report Builder. Report Builder is a client-side application that you can use to create and design ad hoc reports. Using Report Builder, you can select data and design reports without having to understand how and where the data is stored. Nor do you need to know any complex programming languages in order to create reports. You simply need to be familiar with the information stored in your databases.
Using Report Builder, you can create table, matrix, or chart reports. To get started, select a report layout template and then simply drag and drop the fields that you want into the design area. You can then manipulate your data by filtering, grouping and sorting, or working with formulas. Save your report to the report server, and then you can manage it just like any other report on the server. Or, if you want, you can export the report to your local computer as a different file type, such as a TIFF, PDF, Excel, or HTML file.
Service Desk and Reporting Services
If you are familiar with developing Service Desk Reports in Crystal or (especially) Access you will feel comfortable in Reporting Services. CA provided the DLLs to convert Unixtime to human readable format. This type of conversion is not included with Reporting Services. However, Reporting Services uses Visual Studio as the base for development - this is good, just remember that reports need to make time & date format conversion - for queries and results as well as time zone adjustments.
Reporting Services provides a historical snapshot feature on your reports. If used in conjuction with Service Desk data purge/archive, this feature creates a long lasting archives of data, formatted in the manner that you wish. Depending on your situation, this can come in handy for SOX compliance & audit. Historical snapshots are scheduled by you and you define the format (PDF? CSV? etc.)
Service Desk Data Security
When developing reports, take consideration of your target audience and Service Desk data security. Reporting Services can connect to many types of data sources. You can retrieve Service Data via ODBC or Web Services.
When you make an ODBC connection, you are bypassing Service Desk security - data partitions and access types. You can restrict the audience in Reporting Services by placing specific access rights on a report (or a folder containing reports). Depending on your situation, this is could be a high maintenance or unwanted task.
Connecting to Service Desk via Web Services will maintain data partitions and access type restrictions. Your report needs to send Service Desk login data and will need to store the Session ID (SID) for the user. After this has been accomplished Reporting Services will need send the query, so it is a bit more complex.
