In an earlier post I introduced the SQL Server umbrella and briefly discussed a number of products that make up the SQL Server stack. One of those products and a key element of the SQL Server family is SQL Server Reporting Services (SSRS). SQL Server Reporting Services is Microsoft’s enterprise reporting platform.
SQL Server Reporting Services compliments the other components that sit under the SQL Server umbrella. Your reports can be developed to present data from both the SQL Server relational databases and Analysis Services cubes. However it is important to note that the data source doesn’t have to be a SQL Server database or analysis services cube. It can be any data source that SSRS can connect too using OLE DB or ODBC connections. I have many clients who run other database platforms such as oracle but utilise SSRS as their reporting solution.
Competitor products and tools available on the Market that compete with SSRS include but are not limited to
- Crystal Report
- Business Objects
The report life cycle can be broken down into three phases and depending on the job you do and what responsibilities you have will influence the phases in the life cycle that you are involved with. It might be you are involved in all phases end to end, in some sort of dev-ops capacity that is you write reports, deploy reports and manage reports. However it’s not uncommon,, depending on your job role for you only to be involved in one or perhaps two of the life cycle stages. It might be that you are developer writing and authoring reports or DBA deploying and managing them.
Authoring
Authoring is the first stage in the reporting life cycle, after collecting user requirements including what data needs to be presented; in this stage you go about putting your report together. This will involve:
- Defining the data source – specify where the data to be included in the reports lives
- Define the data set – write the query that will retrieve and extract the data from the data source
- Layout the report define the look and feel
The resultant document is written in Report Definition language (RDL) which is XML document that contains the report metadata. Just a note no actual data is contained in the report. It's metadata on the report itself. The data is retrieved from the data source when the report executes.
There are two tools that can be used for authoring reports
- Report Design – which is integrated into SQL Server Data Tools
- Report Builder – which is standalone application
You can actually write in any tool that produces a correct RDL document
Management
When the reports have been written they get deployed to a central reporting server, which will allow you to make the reports available to those users with permissions to view them. SSRS has a robust role based security model allowing you to lock down your reports and implement a robust security model. Once deployed there are many other management tasks that you might need to complete including setting up execution and delivery schedules
The tools involved in the management of SSRS include:
- Report Manager – Which is a web application. This web application can also be used to view reports
- Reporting Service Configuration tool which is used to manage the setup of SSRS
There are some command line tools too. RS.exe can be used to run scripts against the report server which allows you to automate and programmatically control things like report deployment and auditing.
Delivery
How reports are delivered will very much depend on how your SSRS instance is installed and setup. If you are using Sharepoint integrated mode you can view and consume your reports on demand through Sharepoint. If you are using a native instance then you can view reports on demand through the Report Manager Web portal. Different report formats are supported. You might have two users who consume the same report in different formats. One might have the report rendered in an excel format and another user might consume the report in pdf. There are several report formats available ‘out of the box’. User can also setup subscriptions to reports allowing them to automate the report delivery. E-mail is probably the most common type of report delivery mechanism but a file-share location can also be used a second delivery option.
This is a short introduction to SQL Server Reporting Services. Learning Tree course 140 provides a hand-on look at reporting services from report authoring through to deployment and management. We have done a great deal with Learning Tree and we are offering seats on the 140 SSRS course running at the London Education Centre staring on the 2 August at just £750 plus VAT. If you are interested please email training@gethynellis.com for more information
