Exercise 1: Table report

This exercise is solved together with the lab instructor.

In the checked-out repository, locate file reportserver.sln and open it with Visual Studio. This is an empty Report Server project.

The Report Server project consists mainly of Report Definition (.rdl) files, that define the data sources (queries) and a template, which, when rendered, produces the final result: a report. These reports can be installed to a Report Server and executed there, providing the users with up-to-date data.

In this lab, we will not use the Report Server. This is mainly because the configuration would require administrative privileges that we do not have in the labs. Therefore, we will preview the reports in Visual Studio.

Create the first Report Definition file

1. In the Solution Explorer right-click Reports and choose Add > New Item.

   

2. Choose the Report type from among the listed templates. Call it Sales Orders.rdl, then click Add.

   If adding the new report file fails

   In certain Visual Studio and Report Server project versions adding this new report file might fail. If this happens, follow these steps instead.

   1. Download this empty rdl file.
   2. Save the file with the correct name to the reportserver folder of your repository (this folder already exist).
   3. In Visual Studio right-click Reports then choose Add > Existing Item, and browse for this file.

3. Open the report file to get the Report Designer view. Here, the new .rdl file is displayed in the Design view.

   

   This is our development view. The Report Designer has two views: Design and Preview. A panel called Report Data (on the left) is also opened. Here, we can define data sources. If the data sources are set, we can create the report on the Design tab and check how it would look like on the Preview tab.

Configuring the data source

A data source defines where our data comes from. This will be the SQL Server database created before.

1. Using the Report Data pane, click New > Data Source. The name shall be "AdventureWorks2014".

   

2. Choose Microsoft SQL Server as the connection type and click the button to the right of connection string to configure the database access

   • Server name: (localdb)\mssqllocaldb
   • Authentication: Windows Authentication
   • Select or enter database name: AdventureWorks2014

3. Click OK to close the dialog. Then re-open the Data Source settings from the Report Data panel by right-clicking on the newly created data source, opening its properties, and then going to the Credentials page. The following checkbox has to be checked:

   

Configuring a data set

The next step is the configuration of a dataset. Practically, this means executing a query in the database.

1. Using the Report Data pane, click New > Data Set. Call the dataset "AdventureWorksDataset". Select the data source created before from the dropdown, then apply the following settings:

   

2. Copy the following query.

   SELECT
   soh.OrderDate AS [Date],
   soh.SalesOrderNumber AS [Order],
   pps.Name AS Subcat, pp.Name as Product,
   SUM(sd.OrderQty) AS Qty,
   SUM(sd.LineTotal) AS LineTotal
   FROM Sales.SalesPerson sp
   INNER JOIN Sales.SalesOrderHeader AS soh
         ON sp.BusinessEntityID = soh.SalesPersonID
   INNER JOIN Sales.SalesOrderDetail AS sd
         ON sd.SalesOrderID = soh.SalesOrderID
   INNER JOIN Production.Product AS pp
         ON sd.ProductID = pp.ProductID
   INNER JOIN Production.ProductSubcategory AS pps
         ON pp.ProductSubcategoryID = pps.ProductSubcategoryID
   INNER JOIN Production.ProductCategory AS ppc
         ON ppc.ProductCategoryID = pps.ProductCategoryID
   GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderNumber,
            pps.Name, pp.Name, soh.SalesPersonID
   HAVING ppc.Name = 'Clothing'
   

   Click Refresh fields when ready.

   We have a Query Designer where the query can be created with visual aids. We will not use it, but it is available.

   Click OK to close the dialog.

Table report (5p)

Now that we have our connection to the database and the query that will supply the data, let us create a report. A report is basically data from the database displayed in a table or with diagrams.

1. Open the Toolbox pane. You can do this from the View menu.

2. From the Toolbox choose Table and draw a table on the big, empty and white canvas on the Design tab:

   

3. Switch back to the Report Data pane and expand the AdventureWorksDataset:

   

   If the node is empty or cannot be opened, you need to re-open the data set properties using right-click, then clicking the Refresh Fields button.

4. Drag the Date field to the first column of the table. It should look like this:

   

   The [Date] in the second row shows the expression to evaluate, while "Date" in the first row is the literal header label - we can change it too.

5. Similarly, add Order and Product to the second and third columns. Add Qty as well: drag it to the right side of the last column; the cursor icon will change to + sign, and a blue line at the end of the table will appear. This will add a new fourth column. Add LineTotal similarly into the fifth column.

   

6. The first report is almost ready. Let us check how it looks like using the Preview tab. Note that it might take a while for it to open the first time. It will be faster the second time. Verify that your Neptun code appears in the table content! (If not, you forgot a preparation step. Go back, and repeat the steps!)

   

   We can print or export the report into various formats (e.g., Word, Excel, PDF). However, this report is not very pretty, e.g., the currency is not displayed, and the Qty and date columns are not formatted, etc.

7. Go back to Design tab, right-click the [Date] expression, and select Text Box Properties. Navigate to the Number page, select the Date category, and choose a date format you like.

   

8. Right-click [LineTotal], use Text Box Properties again, and select the Currency option in Number.

   

9. By moving the mouse over the gray boxes at the top of the table header, the cursor changes to resize mode. (Just like you would resize a table in Word.) Use this to resize the entire table, and the columns (Qty and Line Total can be narrower, while the others might need more space).

   Finally, emphasize the header row. Select the whole row (by clicking the gray rectangle on the left end of the row) and click Bold on the ribbon.

   

   If you check the preview, it should look like this:

   

SUBMISSION

If you are continuing with the next exercise, you may omit to create the screenshot here.

Create a screenshot of the report preview page. Save the screenshot as f1.png and submit it with the other files of the solution. The screenshot shall include Visual Studio and the report preview. Verify that your Neptun code is visible!

Upload the changed Visual Studio project and its corresponding files too.

Grouping and total value (5p)

The report we created is very long, and it contains everything without structure. These are retail sales information: the amount of products sold each day. Let us group the data.

1. Go back to Design tab. Make sure that we see the Row Groups pane below the table. If it is not there, right-click the design area and select Grouping in the View menu.

2. Drag the Date field from Report Data to the Row Groups pane above the (Details) row.

   

   The table will look like this:

   

3. Drag field Order into Row Groups between Date and (Details).

   

4. Now there are duplicate columns in the table. Let us delete these. Select the rightmost Date and Order columns by clicking on the gray boxes above them. Delete them by right click and Delete Columns.

   

   Unfortunately, the new Date column format is now lost, but you can set it again as previously.

   Check the Preview now, and see that the table is now ordered and grouped as we specified it.

   

5. Go back to the Design view. Right-click the [LineTotal] cell and click Add Total. This will add a total for each Order (which we used for grouping). There will be no label added to this line. Add one by left-clicking the cell and typing: "Order Total".

   

6. Holding the CTRL key pressed down, click Order Total, and the two cells to the right to select them all. Then set a background color by choosing one from the Format menu.

   

7. Check the preview of the report now.

   

8. Let us create a daily total as well!

   • Go back to the Design view.
   • Right-click on the [Order] cell and click Add Total > After.
   • A new cell (Total) appears below [Order]. Click in it and change the label to "Daily Total".
   • Select the cell and the three right next to it (e.g., by using CTRL and clicking them) and change their background color (Format > Background color).

9. Since there are quite a few orders per day, you may need to scroll down 4-5 pages to check the result in the preview:

SUBMISSION

Create a screenshot of the report preview page. Save the screenshot as f1.png and submit it with the other files of the solution. The screenshot shall include Visual Studio and the report preview, including the lines showing the totals (a turn a few pages if needed to see one). Verify that your Neptun code is visible!

Upload the changed Visual Studio project and its corresponding files too.