SSRS Sample Report Creation Using Business Intelligence Development Studio (BIDS)
The first step in creating a report is to add a new report to our project.
In the earlier section on Projects and Solutions, we created a blank solution and added a Report Server project to the solution. In the previous section we added a new report by stepping through the Report Wizard. The BIDS Solution Explorer shows our Reports project along with the Shared Data Source and ReportWizardExample that we created in the previous section:
Right click on the Reports node then select Add, New Item which will display the Add New Item – Reports dialog; fill in the dialog as shown below:
Click the Add button to add a new report to your project. Your new report will be displayed in the Report Designer. Let’s spend a minute to review the Report Designer before we continue with our task of creating a new report from scratch.
There are three parts of the Report Designer which you will be using:
- Design Surface – palette where you layout your report
- Report Data – allows you to define Data Sources, Datasets, Parameters and Images; allows you to access a group of built-in fields like Report Name, Page Number, etc. You will drag and drop items from this area onto the design surface.
- Toolbox – contains the Report Items that you drag and drop onto the design surface; e.g. Table, Matrix, Rectangle, List, etc.
Whenever you add a report to your project or open a report the design surface will be displayed. After adding a report you will see the following blank design surface:
You can display the Report Data and Toolbox areas by selecting them from the top-level View menu if they aren’t shown. The Report Data and Toolbox are used a lot so I like to position them to the left of the designer. The Report Data area is shown below:
In the screen shot above Report Data and the Toolbox share the same area of the screen; click on the tab at the bottom to switch between them. The Toolbox contains the following elements that you will drag and drop onto the design surface:
Note in the heading of Report Data and the Toolbox there is a push pin icon. Clicking this toggles between showing the tab and hiding it by putting a button that you can hover over to display the tab.
You can customize what you see in the report designer as well as position it however you like. Click on the Report Data or Toolbox heading and you can drag it around and position it.
At this point let’s continue on to the next section and create a Shared Data Source.
|Create a Shared Data Source|
We discussed the Shared Data Source in the earlier section on using the Report Wizard to create a new report. The Data Source contains the information that Reporting Services needs to retrieve the data that we want to render on our report. A Shared Data Source is one that can be used by any report in the same project. In this section we will create a Shared Data Source.
To create a Shared Data Source click on the New button in the Report Data area then select Data Source from the menu as shown below:
The Data Source Properties dialog will be displayed as shown below:
The first thing to do is to provide a name; enter AdventureWorksLT in the Name textbox. Since we already defined a Shared Data Source in the earlier section on using the Report Wizard to create a new report, click the Use shared data source reference radio button and select AdventureWorksLT from the dropdown list. The Data Source Properties dialog is shown below:
At this point we are done. If you need to create a new Shared Data Source, you would click the New button and complete the Shared Data Source Properties dialog. This is essentially the same thing we did back in the Report Wizard section of the tutorial.
We can now see our Shared Data Source in the Report Data area as shown below:
We are now ready to continue on to the next section and create a Data Set.
|Create a Data Set|
A Data Set contains a query that Reporting Services uses to retrieve the data for our report. This query could be a SQL statement like we used in the Design the Query step of the Report Wizard section; it could also be a stored procedure that we execute. In this section we will define a new Dataset by using the same query that we used earlier in the Report Wizard section.
To create a Dataset right click on the AdventureWorksLT Shared Data Source that we created in the previous section and select Add Dataset from the menu as shown below:
The Dataset Properties dialog will be displayed as shown below:
The first thing to do is to provide a name; enter Main in the Name textbox. Since we only have one Shared Data Source in our project, it will be selected automatically in the Data source dropdown. To define our query we could click the Query Designer button and do it graphically or we could type in the query as we did in the Report Wizard section. Instead click the Import button which will initially display the familiar Open File dialog; navigate to the report that we created earlier in the Report Wizard section of the tutorial as shown below:
Click OK to display the Import Query dialog as shown below:
The above dialog displays the Datasets and their queries from the report. Our earlier report has only one Dataset so just click the Import button. If the report had multiple Datasets, you could choose the Dataset from the list on the left.
The Report Data area now shows our new Dataset and the list of available fields as shown below:
We are now ready to continue on to the next section to configure a Table for our report layout.
|Design Report Layout|
The Toolbox contains a number of Report Items that you drag and drop onto the design surface when creating a report. The Table provides the traditional report layout where you render data in a fixed number of columns and a variable number of rows. In this section we will use the Table to define the same report layout that we used earlier in the Report Wizard section.
To add a Table to the design surface, simply click Table in the Toolbox then drag and drop it onto the design surface. The design surface should look like this:
Recall from our previous step where we created a Dataset; the Report Data is shown below:
Drag and drop the ProductCategoryName and Sales fields (one at a time) onto a column of the Data row of our Table as shown below:
Click the Preview tab to render the report; a portion of the rendered report is shown below:
Click on the Design tab and focus on the Row Groups at the bottom as shown below:
We want our report to show the ParentProductCategory broken down by the ProductCategory. Click the arrow on the (Details) line shown above then click Add Group, Parent Group; fill in the Tablix group dialog as shown below:
The Table now looks like this:
Right click on the gray border above the empty column and select Delete Columns from the menu. Click on the Group1 cell and change the text to Parent Product Category. Click and drag the gray border above the column headings to make them a little wider. Click the Preview tab to render the report:
At this point we are almost done; we need to add Parent Product Category totals, drilldown capability to show/hide the Product Category details, a grand total, and some general cosmetic cleanup. Click on the Designer tab to continue.
To add the Parent Product Category totals, hover the mouse over the empty cell in the Sales column on the ParentProductCategoryName row; a little icon will appear; click the icon and select Sales from the popup menu. You will see the expression [Sum(Sales)] as shown below:
To add the drilldown capability, right click on the gray border on the bottom row in the designer and select Row Visibility from the popup menu. Fill in the Row Visibility dialog as shown below:
The above settings will initially hide the Product Category details and add the plus sign icon next to the Parent Product Category to toggle the drilldown. The name Group1 was assigned when we added the Parent Product category group. Click the Preview tab to render the report:
Click the Design tab then click the arrow on the Details line inside the Row Groups area; select Add Total, After from the popup menu. You will now see a row after the detail row; edit the blank cells on the total lines to specify a description as shown below:
Finally for the cosmetic changes, drag and drop a text box above the table and provide a heading for the report. Click in the Sales cells and specify C0 (i.e. Currency with no decimals) as the Format property in the Properties window (click View the Properties on the top-level menu if you don’t see the Properties window). Click Preview to see the completed report:
This completes the section on creating a report from scratch. We are now ready to continue on to the next section to discuss the options for deploying reports.
In the prior sections we used Business Intelligence Development Studio (BIDS) to create some sample reports. BIDS has a Preview capability which allowed us to render our report in the development environment. When we have completed a report we need to deploy it so that other users can run it.
In this section we will show how to deploy reports using the following methods:
- Business Intelligence Development Studio (BIDS)
- Report Manager
In SQL Server 2005 Reporting Services you could also use SQL Server Management Studio to deploy reports. This functionality has been removed in SQL Server 2008.
You can also deploy reports to a SharePoint document library when you configure Reporting Services in SharePoint Integration mode. We are not covering SharePoint Integration in this tutorial.