This tutorial gives you an overview and talks about the fundamentals of SSRS.
What is SSRS?
SQL Server Reporting Services is a reporting tool that comes free with SQL Server 2012. With SSRS you can produce formatted reports with tables of data, graphs, and charts. These reports are hosted on a server and they can be configured to run using parameters supplied by the user. When the report runs, it displays current data from the source database, XML file or other data source.
In addition to users running reports on-demand, reports can be scheduled to run at certain times or they can be published manually. Reports can be published to a web site, integrated into a Windows application or form part of a SharePoint site.
When might reports be useful?
Consider a medical research facility where patients are recruited on to various clinical trials. The staff in the clinic create a database record for each patient when he or she agrees to be part of the trial, and then clinic receives a payment from the drug company based on the rate at which it finds willing participants.
A typical scenario in the past might have involved the clinic emailing the drug company with the total number of participants in the trial on a weekly basis, maybe with details of any patient who dropped out of the trial for medical or personal reasons, the amount of drugs used, and any adverse events encountered. The time taken to collate and send this data in the correct format would take up valuable time in the clinic.
If the clinics were recording data in a database, SSRS could be used to produce on-demand reports in a pre-defined format that could then be either exported as a PDF, Excel, etc and emailed to the drug company, or the drug company could be given access to a reporting server so that they could run the report at any time, and get up-to-date data without anyone from the clinic lifting a finger.
SQL Server Data Tools (SSDT)
SSDT is a Microsoft application which is based on Visual Studio, but is configured for use with Microsoft’s Business Intelligence line of products such as SSRS, SSIS (SQL Server Integration Services) and SSAS (SQL Server Analysis Services). SSDT is free with SQL Server, and is installed as an option during SQL Server installation.
To check if SSDT is installed, you can either open the Start Menu and search for “data tools” in the search box, or navigate to Start > All Programs > Microsoft SQL Server 2012 > SQL Server Data Tools. If SSDT is not installed, you should run the SQL Server 2012 installation again and select this option.
Solutions and Projects
Every report must be contained in a project, and every project must be contained in a Solution. You can think of a solution as being a convenient way to group similar projects together and a project as a convenient way to group similar reports together.
For example, you could create a set of reports for the Sales manager and locate these in a project called Sales Reports. Then you could create a set of reports for the Human Resources manager and locate these in a project called HR Reports. These reports might use different databases, but they are all intended for a similar audience i.e. ‘Management’. Therefore, these projects can be part of a ‘Management Reports’ solution. Grouping them in this way makes it easier to organise your work, and also to use source control software for checking in and backing up work.
Data Sources and Datasets
When you are building reports, it’s likely that you will want to design more than one as part of the same project. For example, you might be asked to produce a summary report that allows the user to select an item which automatically opens another report that shows further details on that item.
Although these reports will show different data, it’s highly likely that they will use the same database. To avoid duplicating the connection information and to keep things tidy and manageable, you can create a database connection at the project level and then use this same connection to create datasets in all your reports.
Data Sources vs Datasets
It is important to understand the difference between data sources and datasets.
A data source contains details about the database server you will be connecting to, the login to use and the database to use.
A dataset contains the specific query that will be used to fetch data for a particular report.