SQL Server Reporting Services Lab 1

TASKS IN THIS LAB

To create a report server project 2 To create a new report definition file 2 To set up a connection 2 To define a Transact-SQL query for report data 3 To add a Table data region and fields to a report layout 4 Preview the report you have built to this point 5 Format the date field 6 Format the currency field 6 Format the header rows and table columns 7 Group data in a report 7 Add totals to the report 8 Add a grand total to the report 9

In this lab we create a basic table report based on the AdventureWorks2014 database. To create the report we use the Report designer tool with characteristics and functionality universal among products from multiple reporting applications vendors.

PART I - CREATING A REPORT PROJECT
Create your first report 1. Start SQL Server Data Tools.

2. On the File menu, point to New, and then click Project.

3. Select the Report Server Project.

4. For Name, type BasicReport. For location, you can leave in the default location or you can choose a location of your liking.

5. Click OK to create the project.
Create a new report definition file 1. In Solution Explorer, right-click Reports, point to Add, and click New Item. 2. In the Add New Item dialog box, click Report.

3. For Name, (at the bottom of the dialog box) type SalesOrders.rdl and then click Add. The Report Designer opens and displays the new .rdl file in Design view.

The Report Designer is a Reporting Services component that runs in SQL Server Data Tools (SSDT). It has two views: Design and Preview. You design the report in Design view and you see the data in Preview view.
PART II: SPECIFYING CONNECTION INFORMATION
Now we need to define a data source, which is connection information the report uses to access data from either a relational database, multidimensional database, or other resource.
To set up a connection 1. In the "Report Data" pane (to upper the left of your screen), click New and then click Data Source….. Or right click “Data Sources” and select “Add Data Source”

2. For Name, type AdventureWorksDW2014.

3. Make sure Embedded connection is selected and the Type is Microsoft SQL Server.

4. In Connection string, click Edit. In the dialog box that comes up:

In the Server name type or paste the name of your own database server. Or you can click the drop down menu for the available database servers to appear. You need to connect to the SQLSERVERLAB on your machine. The full name for my machine is DEMERP-XPS15\SQLSERVERLAB.

In the Log onto the server group, select Use SQL Server Authentication. Enter your sa userID and password.

In the Select or enter a database name list, select AdventureWorksDW2014 and then click OK.

5. Click OK. The data source AdventureWorksDW2014 is added to the Report Data pane.

PART III: DEFINING A DATASET FOR THE REPORT
After we define a data source, we need to define a dataset. A dataset is a collection of fields from the AdventureWorksDW2104 database. We can use the query designer to design the query. For this tutorial, we will use SQL to create a query that retrieves sales order information from the database.
To define a Transact-SQL query for report data 1. In the Report Data pane to the upper left part of your screen, click New, and then click Dataset…. The Dataset Properties dialog box opens.

2. In the Name box, type AdventureWorksDWDataset.

3. Click Use a dataset embedded in my report.

4. Make sure the name of your data source, AdventureWorksDW2014, is in the Data source text box, and that the Query type is Text.

5. Type, or copy and paste, the following Transact-SQL query into the Query box.
SELECT ProductKey, ModelName, ProductLine, Class, StandardCost, ListPrice, ReorderPoint
FROM dbo.DimProduct
WHERE (ListPrice IS NOT NULL) AND (Weight IS NOT NULL) 6. Click the Query Designer button. The query is displayed in the text-based query designer. You can toggle to the graphical query designer by clicking Edit As Text. View the results of the query by clicking the run (!) button on the query designer toolbar. You might need to enter your "sa" user id and password.
You see the data (273 records) from the DimProducts table.
Click OK to exit the query designer. 7. Click OK to exit the Dataset Properties dialog box.

PART IV: ADDING A TABLE TO THE REPORT
After the source fields have been defined, you can start designing the report. You create a report layout by dragging and dropping fields that you want to include in your report to the design surface.
Items that contain repeated rows of data from underlying datasets are called data regions. A basic report will have only one data region, but you can add more, for example, if you want to add a chart to your tabular report. After you add a data region, you can add fields to the data region.
Add a Table data region and fields to the report 1. Let us start adding controls to the report. Controls for the report can be tables, text boxes, images, charts, etc. To do this we need to drag and drop items from the toolbox. The Toolbox may appear as a tab on the left side of the Report Data pane. If the Toolbox is not visible, from the View menu, click Toolbox.

2. In the Toolbox, click Table, and then drag this table control on the design surface of the report. The Report Designer draws a table with three columns in the center of the design surface.

3. In the Report Data pane, expand (if it is not already expanded) the AdventureWorksDataset dataset to display the fields.

4. Drag the ProductKey field from the Report Data pane to the first column in the table. When you drop the field into the first column, two things happen. A data header for this field is created as well as a data control which will display the date data for each record in the report.

5. Drag the ModelName field from the Report Data pane to the second column in the table.

6. Drag the ProductLine field from the Report Data pane to the third column in the table.

7. Drag the Class field to the right edge of the third column until you get a vertical cursor and the mouse pointer has a plus sign [+]. When you release the mouse button, a fourth column is created.

8. Add all the rest of the fields this way. Your table should now look like the one in the following image:

9. Let us preview the data in the report to this point. Click the Preview tab. Report Designer runs the report and displays the data from the database. You will notice that the report is asking for a user id and password. Enter your sa user id and password and run the report. It should look like the following:

Embed security information in the data source
If you go back to design view and you try to run the report again, you will be asked for security information again. This is not practical at all since in practice we need to go multiple times between the design and preview screens so that we fine tune the report exactly the way we want. We can enter the security info directly in the data source so that we do not have to provide it every time. To do this: 1. In the "Report Data" pane to the left of your screen click on the "data sources" folder to expand it.

2. Right click on the "AdventureWorksDW2014" data source and the click on "Data Source Properties". A dialog box will come up.

3. In the upper left corner of the dialog box click on "credentials". Click on the radio button "Use this user name and password" and click OK.

4. Preview your report one more time. You see that it is not asking for credentials any more.

Working with calculated fields in the report 1. Now, we will add a calculated field in the report. In the "Report Data" pane to the left of your screen right click the "AdventureWorksDW2014" data set and then select "Add Calculated field". 2. In the dialog box that comes up enter e new field named "NominalProfit" at the bottom of the field rows as in the image below:

3. Then, click on the "fx" button for the NominalProfit field to enter the calculated field expression. A new dialog box will come up.

4. For expression, enter "=Fields!ListPrice.Value - Fields!StandardCost.Value" as in the image below and then click OK. A new field will now be available for our report.

5. In the "Report Data" pane to the left of your screen expand the dataset "AdventureWorksDW2014" and then drag the field "NominalProfit" at the end of the report. Your report should now look like the following:

Preview the report you have built to this point
Previewing a report enables you to view the rendered report without having to first publish it to a report server. You will probably want to preview your report frequently during design time. You might also notice that the report takes more time to load. This is because of the calculated field. Yu always have the option to create the field in the SQL statement on which the report is based and compare loading times.

On the File menu, click Save All to save the report.

PART V: FORMATTING A REPORT
Format the header rows and table columns
We will change the formatting of the header row to differentiate it from the rows of data in the report. 1. Select the whole row of the column headers and from the Format menu, point to Font and then click Bold. For font size select 12 points. 2. Now click on the cell of the intersection of the rows and columns of the report.

As soon as you do that a cross will appear. Put the mouse point on this cross and drag your report to the upper left part of the screen.

This is how your report design should look like:

3. Preview your report. It should look something like this:

4. On the File menu, click Save All to save the report. You can resize the width of the fields so that all data for each product shows in one row.

Format the NominalProfit field to display as currency
The NominalProfit field displays a general number. We will format it to display the number as currency. 1. In report design view, right-click the cell with the [NominalProfit] field expression and then click Text Box Properties.

2. Click Number, and in the Category field, select Currency.

3. Select Use 1000 separator (,).

4. Click OK.

5. Preview the report to see the change to the [NominalProfit] field and then change back to design view.

PART VI: ADDING GROUPING AND TOTALS
Group data in a report

Grouping and aggregating data are fundamental functionalities for any report design tool and as such they are incorporated in SSRS. Grouping records by a certain field allows us to display and summarize information with respect to the grouped field. Let us create a first group for our report. 1. Click the report Design tab if the report is not already in design view.

2. If you do not see the Row Groups pane at the bottom of the report, right-click the design surface and click view and then click Grouping.

3. From the Report Data pane, under the AdventureWorksDW2014 dataset, drag the ModelName field to the Row Groups pane. Place it above the row called (Details). The Row Groups window should look like this:

Preview your report. It should look similar to the following image.

You see that there are two Model Name fields. The first one is the group field and the second the table field where each model name is repeated for each record. Usually, we delete the second because it is redundant for the report since we already see the model name from the group field. * Delete the second model name field. Switch to Design view, right click on the second product line column and select “delete columns.” * Also increase the width of the model name group column so that model names in the report do not wrap. Your report should look now like the following:

Add totals to the report 1. Switch to Design view if you are not already there.

2. Right-click the field [Nominal Profit], and click Add Total. This sums up the total of all the nominal profit values for each group. Now, make this value bold and 11 points so that it can stand out in the report. Your report should look like the following:

3. You can add a background color to the totals row. While holding down the SHIFT key click the first and last cells of the nominal profit sum row (as shown in the image below). On the Format menu, click Background Color, select a green color and click OK.

4. Preview your report. It should look like the one in the following image.

Add grand totals to the report 1. Go back to the report design view. Right-click the Model Name cell, point to Add Total, and click After.

2. Preview your report and navigate to its last page. As you can see we now have grand totals for all the numeric fields. You can delete the ones that you do not need.