How to Create and Edit a Report in SQL Server Reporting Services

How to Create and Edit a Report in SQL Server Reporting Services

byJennifer McCown, MidnightDBA Guru- November 24, 2010
Printer Friendly Versions

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.

If you have not already done so, download and install thefree AdventureWorks database, and complete the steps in the articleSQL Server Reporting Services: An Introduction.

Add an SSRS Report to a Project in BIDS

To create a report manually in Business Intelligence Development Studio (BIDS):

1.  Open BIDS. Select File > Open > Project/Solution.

2.  Locate and open the previously created Report Server Project.

3.  In the Solution Explorer, right-click the Reports folder and select Add > New Item.

4.  Select "Report" from the Add New Item window, and name the report (e.g., "MyReport.rdl"). Click OK.

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. TheToolboxtab displays the items available to your report, such as textboxes and tables. TheReport Datatab displays the available data elements, which can include result sets from your data sources, images, and built-in report fields (like "Page Number").

Add a Data Source and Dataset to the Project

Adata sourceidentifies 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:

1.  On the Report Data tab, select New > Data Source. Give the data source a meaningful name.

2.  Select the "Use shared data source reference" radio button.

3.  Select the previously created SQL Server data source from the drop-down menu. Click OK.

Adatasetis 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:

1.  On the Report Data tab, select New > Dataset. Give the dataset a meaningful name.

2.  Select the data source you added in the steps above.

3.  This screen allows you to type or paste your query or stored procedure call; or you can click "Query Designer" to build your query in the graphical design. Paste the following query in the Query text area:

SELECT P.Name ,
ProductNumber ,
Color ,
ListPrice ,
SC.Name [Category]
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

4.  Click OK.

You can now see the data set and available data fields in the Report Data tab.

Add Data Elements to the Report

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:

1.  Drag aTableitem from the Toolbox tab onto the surface of your report.

2.  From the Report Data tab, drag and drop the data fieldsCategory, Color, Product, Name, andList Priceinto columns in the empty table.

3.  Drag aTextboxitem from the Toolbox tab onto the surface of your report, above the table. Type "AdventureWorks Products" in the textbox.

4.  On a blank area of the report, right-click and select Insert > Page Footer.

5.  Drag a textbox item into the footer. From the "Built-In Fields" section on the Report Data tab, drag "Page Number" into the textbox.

6.  Click the Preview tab above the report area to see the report populated with data.

Format the Report and Export

The Design tab allows many options to improve the report presentation, including:

·  Add and delete columns- Right-click the table and select Insert Column > Left (or Right).

·  Change the column widths- Drag and drop column header borders.

·  Change text font and style- Select the fields and apply changes from the format toolbar.

·  Format numeric fields- Right-click the numeric field, select "Text Box Properties", and make changes using the Number option (for example, List Price might be displayed as Currency).

·  Change the report area- Drag the edges of the report to make it larger or smaller, or drag the border of the footer to move it closer to the end of the table.

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:

1.  Click the Preview tab.

2.  Click the "Export" icon above the report.

3.  Select your preferred file format.

4.  Select the location and name for your file. Click Save.

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.