Skip to content

Exercise 3: Sales personnel report

This exercise is to be completed individually for 5 points.

Let us create a new report about the effectiveness of the sales personnel.

Extending the data set

The dataset we created in the Report Data panel has to be extended with further information.

  1. Open the data set properties by right-clicking on AdventureWorksDataSet in the Report Data pane, click Dataset properties then change the SQL command to:

    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
 , CONCAT(pepe.FirstName, ' ', pepe.LastName) AS SalesPersonName
FROM Sales.SalesPerson sp
  INNER JOIN Person.Person as pepe ON sp.BusinessEntityID = pepe.BusinessEntityID
  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
        , pepe.FirstName, pepe.LastName
HAVING ppc.Name = 'Clothing'

    Don't forget to press Refresh fields before closing the dialog. Close the dialog.

  2. In the Report data expand AdventureWorksDataset (if already expanded, close then re-expand). A new field SalesPersonName will appear.

  3. Right-click the data source AdventureWorks2014 and choose Convert to shared Data Source, and then repeat this with the AdventureWorksDataSet too. These will enable us to use them in a new report.

New report and data sources

We will use the converted and shared data source and data set in a new report.

  1. In the Solution Explorer right-click Reports and choose Add > New Item > Report. The name of this new report should be "Sales People".

  2. Open the new report. There are no data sources associated with the report. Use the Report Data pane to add the existing one:

    • Right-click Data Source and choose Add Data Source

    • Click the Use shared data source reference option and select "AdventureWorks2014".

      Shared data source

    • Right lick Datasets > Add Dataset

    • Click Use a shared dataset and select the existing AdventureWorksDataset

      Shared dataset

Contents of the report

Create a tabular report containing the sales persons and their activity. Group by product category and sales person. Add a total line for each person. Make sure to set appropriate formatting of numbers.

The key is creating the table and grouping as below. The category is the field Subcat.

Recommended groups

The final report should look like this:

Preview

Tip

You should use the Add total > After just like before. But you should do this by clicking on [Subcat] and not[SalesPersonName]! (If you click SalesPersonName to add the total, it will be a "grand total" adding up all persons.)

SUBMISSION

Create a screenshot of the report preview page. Save the screenshot as f3.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 in the table!

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

Back to top