How to Create and Edit a Report in SQL Server Reporting Services
Add an SSRS Report to a Project in BIDSTo create a report manually in Business Intelligence Development Studio (BIDS):
- Open BIDS. Select File > Open > Project/Solution.
- Locate and open the previously created Report Server Project.
- In the Solution Explorer, right-click the Reports folder and select Add > New Item.
- 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.
Add a Data Source and Dataset to the ProjectA 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:
- On the Report Data tab, select New > Data Source. Give the data source a meaningful name.
- Select the "Use shared data source reference" radio button.
- Select the previously created SQL Server data source from the drop-down menu. Click OK.
- On the Report Data tab, select New > Dataset. Give the dataset a meaningful name.
- Select the data source you added in the steps above.
- 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:
- Click OK.
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
Add Data Elements to the ReportNow that you have defined your dataset, you can begin to build the report itself, starting with a table of products, title, and page number:
- Drag a Table item from the Toolbox tab onto the surface of your report.
- From the Report Data tab, drag and drop the data fields Category, Color, Product, Name, and List Price into columns in the empty table.
- Drag a Textbox item from the Toolbox tab onto the surface of your report, above the table. Type "AdventureWorks Products" in the textbox.
- On a blank area of the report, right-click and select Insert > Page Footer.
- Drag a textbox item into the footer. From the "Built-In Fields" section on the Report Data tab, drag "Page Number" into the textbox.
- Click the Preview tab above the report area to see the report populated with data.
Format the Report and ExportThe 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.
- Click the Preview tab.
- Click the "Export" icon above the report.
- Select your preferred file format.
- Select the location and name for your file. Click Save.