Pages

Thursday, 29 March 2012

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

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

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.

Add an SSRS Report to a Project in BIDS

To 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.

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").

Add a Data Source and Dataset to the Project

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:

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:

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.