Report Builder is a stand-alone authoring environment for creating Reporting Services paginated reports outside of Visual Studio.
Report Builder provides the capability to design, execute and deploy SQL Server Reporting Services reports. The user interface has been streamlined to allow business users to become proficient at authoring reports very quickly.
In this lesson, I will demonstrate how to:
- Getting Started
- Report Wizards
- Building a Report from Scratch
- Deploying Reports
- New Report
- New Dataset
The New Report option allows you to choose a wizard or a blank report as your starting point. The wizards walk you through creating a report in a sequence of steps. The blank report allows you to handle all aspects of creating the report. For beginners the wizards are a great choice; experienced users may prefer the blank report.
New DatasetA dataset defines the data that you want to render on your report.Alternatively you can define a dataset within a report (an embedded dataset) but it is a best practice to use shared datasets.
OpenThe Open option displays the familiar file open dialog allowing you to locate and open a report. Report files have an extension of .RDL (report definition language).
RecentThe Recent option allows you to choose a report to open from a list of recently used reports.
Report WizardsCreate a table report with the Table or Matrix wizard. There are two modes: report design and shared dataset design. In report design mode, you specify data in the Report Data pane and the report layout on the design surface. In shared dataset design mode, you create dataset queries to share with others. In this tutorial, you will be using report design mode.
- In the left pane, verify that New Report is selected.
- In the right pane, select Table or Matrix Wizard.
- Specify a Data Connection in the Table Wizard
Note: You can download the database I am using in this demonstration from this link SQL Server DB
Create an embedded data source
- On the Choose a dataset page, select Create a dataset, and then click Next. The Choose a connection to a data source page opens.
- Click New. The Data Source Properties dialog box opens.
- In Name, type Demo a name for the data source.
- In Select a connection type, verify that Microsoft SQL Server is selected.
- In Connection string, type the following text, where <servername> is the name of an instance of SQL Server:
- The message "Connection created successfully" appears.
- Click OK.
- You are back on the Choose a connection to a data source page, with your new data source selected.
- Click Next.
Create a Query in the Table Wizard
- On the Design a query page, the relational query designer is open. For this tutorial, you will use the text-based query designer.
- Click Edit As Text. The text-based query designer displays a query pane and a results pane.
- Paste the following Transact-SQL query into the blank
Sales.Customers.contactname, Sales.OrderDetails.productid, Production.Products.productname, Sales.OrderDetails.unitprice, Sales.OrderDetails.qty, Sales.Orders.shipcity, Sales.Customers.country
FROM Sales.Orders INNER JOIN
Sales.Customers ON Sales.Orders.custid = Sales.Customers.custid INNER JOIN
Sales.OrderDetails ON Sales.Orders.orderid = Sales.OrderDetails.orderid INNER JOIN
Production.Products ON Sales.OrderDetails.productid = Production.Products.productid
- On the query designer toolbar, click Run (!).
- The query runs and displays the result set for the fields SalesDate, Subcategory, Product, Sales, and Quantity.
- In the result set, the column headings are based on the names in the query. In the dataset, the column headings become the field names, and are saved in the report. After you complete the wizard, you can use the Report Data pane to view the collection of dataset fields.
- Click Next.
Organize Data into Groups
- On the Arrange fields page, drag productname to Values.
- Drag qty to Values and place below Product.
- qty is automatically aggregated by the Sum function, the default aggregate for numeric fields. The value is [Sum(Quantity)].
- Select the arrow next to [Sum(Quantity)] to view the other aggregate functions available.
- Drag orderdate to Row groups.
- Drag country to Row groups and place below orderdate .
- Click Next then Next Then Finish to complete the wizard. You have created the following report
- Click the Run button on the ribbon to preview the report