SQL Server 2005, 2008 and 2008 R2 all come packaged with SQL Server Reporting Services (SSRS) – an enterprise-level reporting solution. With SSRS, you can create, schedule, publish, and manage a multitude of reports using a variety of data sources. In the following sections, we will create, edit, and export a SSRS report manually.
To create a report manually in Business Intelligence Development Studio (BIDS):
You now have an empty SSRS report open in BIDS.
On one side of the screen, you should see tabs for Toolbox and Report Data. If either of these tabs is not visible, select the appropriate tab from the View menu. The Toolbox tab displays the items available to your report, such as textboxes and tables. The Report Data tab displays the available data elements, which can include result sets from your data sources, images, and built-in report fields (like “Page Number”).
A data source identifies the database (or other data object, such as an XML file) from which you wish to pull data, and the connection properties used to connect to it – such as server name and login credentials. This example uses the previously created SQL Server data source. To add a data source to your report:
A dataset is the specific set of data requested – via a query or stored procedure execution – from the data source. The dataset defines what data is available to display in the report. To add a dataset to your report:
SELECT P.Name ,
FROM Production.Product P
LEFT OUTER JOIN Production.ProductSubCategory SC
ON P.ProductSubCategoryID = SC.ProductSubCategoryID
WHERE ListPrice BETWEEN 0 AND 1000
ORDER BY Category, ListPrice ASC
You can now see the data set and available data fields in the Report Data tab.
Now that you have defined your dataset, you can begin to build the report itself, starting with a table of products, title, and page number:
The Design tab allows many options to improve the report presentation, including:
The size of the report area may impact the way the report is displayed when you export it. For example, if you export to PDF at standard size (8.5 x 11″) portrait layout and your report is too wide, the report will contain extra pages to accommodate the extra width. Use the ruler (right-click and select View > Ruler) to monitor the report width, and modify the report page layout and borders as needed in Report > Report Properties.
To export the report to PDF, Excel, or other file format:
You have created, formatted, and exported a SSRS report. Future installments of this SQL Server Reporting Services series will cover parameters, grouping, data expressions, and more.