BIS-345: Data Analysis for Business
Lab 5 Questions:
Lab 5 Student Answer Sheet
Student Name:
Using Lab 5, provide copies of your SQL Statements, results and reports for each Section listed below
Part A Product and Category dataset (7 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:

Part B: Product and Category report (10 points)
a. Paste your report here:

Part C: Sales and Territory dataset (10 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:

Part D: Sales and Territory, report (10 points)
a. Paste your report here:

Part E: Sales and Territory Report Interpretation (10 points)
Write your interpretation of the results. The interpretation must be a minimum of one paragraph (3 to 5 well-formed sentences) with no spelling or grammatical errors. Based on these results, what other data would you recommend that the business consider for further analysis?

Lab 5: Part 1: Products by Category/Part 2: Sales by Territory
Overview
Using the AdventureWorks database, you have been requested to create two reports. The reports will incorporate groups, drilldown functionality, and aggregate functions.
The AdventureWorks database contains many tables; we will be using only some of those tables in the course. Below is an ER diagram showing the tables in the AdventureWorks database that we will be using. NOTE: Not all the relationship lines are drawn between the tables in this diagram. To find relationships between tables, you may have to look for matching field names.
Use the Citrix Server to start SQL Server Business Intelligence Development Studio 2008 and create a new Report Server project. Refer to the Week 4 Part 2 Lab on Simple Reports document in the Week 4 iLab tab to review creating a new Report Server project. Be sure to use your last name and lab number as part of the name for the new project.

Part 1: Products by Category Report

A report has been requested that will show a full list of products in each subcategory and category. In this report, you will provide the ability to use drilldown on both the category and subcategory columns. You will also provide a count of the number of products in each subcategory. The report needs to list the product name, product number, list price, and standard cost. Provide an appropriate report title and column headings.
Here is an example of the finished report:

We will not be using the report wizard to create this report. Only one data source is needed for the project, and it will be a shared data source used by both reports.
Step 1: Create a shared data source
Right click the Shared Data Source folder in the Solution Explorer and select Add New Data Source:

In the Shared Data Source dialog box, give the data source a name. You are encouraged to use the same name as your project. Then click the Edit button to build the connection string:

In the Connection Properties dialog box, enter the name of the SQL Server that you have been connecting to for this class; from the Connect to Database section at the bottom of the window, select the BIS345_AdventureWorks database (your version of this database may be named BIS345_AdventureWorks). If you do not see a list of databases in this dropdown, then the server name is incorrect. Your instructor will provide you with the server name for this course. When finished, your screen should look like the following image:

Click OK to return to the Shared Data Source dialog box; the connection string will now be displayed:

Click OK again. You have now created a shared data source and will see it in the Solution Explorer.

Step 2: Create a new report
Right click the Reports folder in the Solution Explorer and select Add -> New Item…

Make sure Report is selected and give your new report a name that includes your last name and indicates the lab number and part number:

Click Add.

Step 3: Create the Data Set
Reports are based on data contained in a data set. The first step in creating a report’s data set is to either create a data source or link to the shared data source we created earlier. The new report should open and display the report data toolbox. If not click on View -> Report Data:

In the Report Data panel click on the dropdown beside New and select Dataset….

In this dialog, you will give the data set a name and specify the data source. The dataset can be given the same name as the report you are creating.
Click on New to select the shared data source that you created previously.

Make sure to select the Use shared data source reference radio button and click on the dropdown to find your shared data source. Complete the selection by selecting the OK button to close the dialog.

Click on the Query Designer… button.

Now you are ready to create the SELECT statement that will select the data for the report. What data do you need? What tables is this information in? Are the tables directly related or do you need a joining table? Review the ER diagram to determine the tables you will need.
Category name is in the ProductCategory table; subcategory name is in the ProductSubcategory table; product name, number, list price, and standard cost are in the Product table.

Step 4: Add tables to the data set
In the Graphical Query Designer, right-click the top pane and select Add Table or use the Add Table tool on the toolbar to add tables to your query:

The tables are listed in alphabetical order in the Add Table dialog. Double-click each of the required tables to add them to the query:

The tables will appear in the top pane of the designer; they can be rearranged if necessary. Make sure they are in the sequence; ProductCategory, ProductSubcategory, and Product.

Note: The SQL pane will show the SELECT statement that is built with each action you take.

Step 6: Add the fields
To add the fields to the SELECT statement, click the check boxes beside each field in the table. The field will be added to the field list grid. All three tables have a field with the same name, which we will utilize in our report. When we select the second field, it will be given an Alias in the Alias column of the field list; this is because we can’t have two fields in a SELECT statement with the same name. Change each name field to something more meaningful. Finally, the table column will show the table from which each field originates. Use the aliases CategoryName, SubCategoryName, and ProductName for each respective name field.

Select the required fields from the product table. The fields should include CategoryName, SubCategoryName, ProductName, ProductNumber, ListPrice, and StandardCost. We would like our report to be sorted in order of category name, then within each category in subcategory order, and then for each subcategory in product name order. Even though we can set the sort order on the report itself, it will display faster if the data is already in the required order, so we will use the Sort Order column to specify how to order the results. CategoryName is our major sort order, then SubCategoryName and then ProductName. The field list pane will look like the following:

 
Execute the query to make sure it works. Click the red exclamation mark on the toolbar or right-click the background on the top pane and select Execute SQL:

The results will appear in the results pane at the bottom of the window. When you are satisfied, you should free memory by clearing the results. Right click the top pane and select Clear Results. Click OK and then click OK on the dataset window. You will be returned to the report design canvas.

Step 7: Layout the report
You will use the design tab to build the report. Make sure you are in the design layout. From the Toolbox, double click the Table button to add a table onto the report. Once you have added the table, your screen should look like this:

Step 8: Add the Category Group
There are several ways to add groups to the report. The easiest way is to drag and drop the fields into the detail row groups.

Click on the report data tab to access the report fields.

Drag the CategoryName field and drop it over the (Details) Row Groups panel. A group header row will be added above the detail row:

Step 9: Add the Sub Category group
Repeat Step 8 to add a group for the SubCategoryName field. Drag and drop the SubCategoryName field between the CategoryName group and Details row group. The table will now look like this:

The header row for the table contains text that you can adjust as needed. Click into the cells and make any necessary changes. Column widths can also be adjusted by using the line separating columns in the column header. Likewise, row heights can also be adjusted.

Step 10: Add fields to the Detail Row
The product fields will be added to the Detail row. By default, the table will only have three columns; add three more columns by right clicking the last column header and selecting Insert Column to the Right or just dragging the field to the end of the last column.
Drag ProductName from the data set and drop it into the detail row of a column; repeat this for ProductNumber, ListPrice and StandardCost. The detail row will contain the field expression enclosed in square brackets ([]), and the table header row will contain a column heading based on the field name. Change the column headings as needed.

Step 11: Add Drilldown
To add drilldown capability, we will need to change the visibility of the appropriate groups. We added two groups, but the Detail row is also considered to be a group.
We want the Detail rows to appear only when the user selects a particular subcategory, so we need to affect the visibility of the detail row group. In the Row Groups panel, right-click the Details row indicator and select Group properties. On the Visibility tab, select Hide and check the Display can be toggled by this report item: checkbox, and then select SubCategoryName from the dropdown:

Preview your report to see the plus sign that is now beside the subcategory names:

We only want the subcategories to appear when the user selects a particular category, so now we will change the visibility of the subcategory group. Right-click the SubCategoryName group header row indicator. Select Group Properties, and make the changes as we did above. Initial visibility should be hidden; check Display can be toggled by this report item: and then select CategoryName from the dropdown:

Preview your report.

Step 12: Add a total
We need to add a count of the number of products in each subcategory. To do this, we will need to build an expression. The expression will include some text along with the count function.
Select the cell where you want the value to appear. In this instance, this will be in the product name column of the subcategory group header row.

With this cell selected, right-click and select the Insert Row -> Outside Group – Above to create the area for the expression.

Right click in the new area and select Expression….

The expression dialog allows you to design an expression, such as a sentence containing a math calculation. In this case, your expression will consist of text and a function; you will need to combine these by using the & symbol. After the equal sign, enter the text “Count of Products: “, enter “&”, then expand Common Functions in the left-hand pane at the bottom of the dialog and select Aggregate. In the middle column, double-click Count.

Now we are ready to specify the field to count. Select Fields in the left hand pane; in the right-hand column, double-click ProductNumber, and close the parenthesis:

Click OK.
Your table will now look like this:

Make any adjustments such as bolding headings, adjusting column widths and row heights, or adding a report header. Add a text box above the table and enter an appropriate heading.

Step 13: Format for Currency
The list price and standard cost numbers should be formatted for currency. Right-click the ListPrice cell in the detail row and select Text Box Properties…:

Select the Format tab, and select Currency. Make sure your setting matches the screen below. Repeat this for the StandardCost cell.

Step 14: Submitting your lab
You will be submitting a screenshot of your report in the layout tab and a screenshot of running the report.
1. Select the layout tab and take a screenshot.
2. Open the Lab 5 Answer Sheet and complete the required steps. Make sure to include a screen capture of your report.
3. Preview your report by right-clicking the report in the Solution Explorer and selecting run.

(To take a screenshot, press CTRL-ALT-PRINTSCRN. Nothing appears to happen on your screen, but this set of keystrokes places a picture of your screen on the clipboard. In Word, just put your mouse where you want the screenshot to go, and then right-click and press Paste. The screenshot will appear in your Word document after a few seconds).
 
Part 2: Sales by Territory Report

Produce a report that shows the total sales for each territory. Allow the user to drill down through the territory to see the total sales amounts for individual categories. You may use the wizard to create the report, but you must ensure that the report is formatted appropriately.
Provide an interpretation of the results in Part 2. The interpretation must be a minimum of one paragraph (3 to 5 well-formed sentences) with no spelling or grammatical errors. Based on these results, what other data would you recommend the business consider for further analysis?
Here is an example of the finished report:

You have the choice of using the wizard as we did in Lab 4 Part 2, or you may create the report the way we did in Lab 5 Part 1. The following steps will lead you through creating the report without using the wizard.

Step 1: Create a new report
Right click the Reports folder and select Add -> New Item…. Be sure that Report is selected, and give your report a name that includes your name, the lab number, and part number. Click Add.

The new report will open at the Data tab, ready to create a new Dataset.

Step 2: Create the Dataset
Create a new Dataset; give it a name to match the report name; then select the Graphical Query Designer. (Refer to Step 3 of the previous report for instructions on how to do this.)
We are using the shared data source, so we are ready to create the data set. What fields do we need? Where will these fields be found? The requirements ask for the territory name, which is found in the SalesTerritoryTable; it also wants the category name found in the ProductCategory table. The sales total will be the result of summing the extended line item total of each sales order detail row. This will be a calculated field based on the SalesOrderDetail table: UnitPrice * OrderQty * (1 – UnitPriceDiscount). If you look at the partial ER diagram at the beginning of this document, you will see the SalesTerritory table, the ProductCategory table, and the SalesOrderDetail table. Are these tables directly related? Unfortunately, they are not. We will have to include other tables in our statement.
SalesOrderHeader contains TerritoryID, which is the primary key in SalesTerritory;
SalesOrderHeader is also related to SalesOrderDetail;
Now, what about the category name? To get this field, we will need to join SalesOrderDetail to Product; then join ProductSubcategory and finally join ProductCategory. So, we will need the following tables:
SalesTerritory, SalesOrderHeader, SalesOrderDetail, Product, ProductSubCategory, ProductCategory.

Step 3: Add the tables and fields
Click the Add Tables tool (or right click the top pane and select Add Table). Add the tables listed in Step 2.

While we need to add six tables to our query, we don’t want fields from all these tables:
Select Name from SalesTerritory and use the Alias column in the grid to give it an alias of TerritoryName. Select Name from ProductCategory and use the Alias column in the grid to give it an alias of CategoryName.
Now we need to enter a calculated field. In the third row of the grid, expand the first column to give yourself room to type. Enter the following expression:
(Sales.SalesOrderDetail.UnitPrice * Sales.SalesOrderDetail.OrderQty) * (1 – Sales.SalesOrderDetail.UnitPriceDiscount)
Give it an alias of Sales. When you tab out of this column, the expression may be adjusted to add the schema name before each table name: Sales.SalesOrderDetail, and so forth.

For each sales amount, we are not displaying any other details, so we need to group the rows based on the territory name and the category name. Click on the Use Group By tool on the tool bar and you will see that a Group By column is added to the grid:

Because TerritoryName is the first row, it will be the first grouping, and CategoryName will be the second grouping. In the Group By for the calculated field, select Sum. Notice the SELECT statement being built for you – it is quite long, but you should understand what it is doing if you read it carefully.

The last thing to do is sort our output in territory name order and then category name order. Use the Sort Order column:

Execute the query to check the results. Click on the red exclamation mark on the toolbar or right-click and select Execute SQL. The results will appear in the results pane at the bottom of the window:

Clear the results to free up memory – right click the window to find this option. Select OK twice to complete the creation of the dataset.

Step 4: Layout the Report
Make sure you are in the Design layout. Drag a table tool onto the report.

This report requires one grouping level based on territory name. Drag the TerritoryName field from the Report Data toolbox and drop it on top of the Details section in the Row Groups panel.

From the report data tab, drag CategoryName into the detail row of the second column and drag Sales into the detail row of the third column. Delete the last column.

Step 5: Add drilldown
Change the visibility of the Detail group. Right click the Details Group and select Group Properties….

On the Visibility tab, select Hidden for Initial Visibility; check Display can be toggled by this report item:, and select TerritoryName from the dropdown:

Click OK. 
Step 6: Add a total
The sales total for each territory should appear in the third column; the second column displays some text along with the territory name. To add the total, right-click the cell in the group header row, as it appears below and add a new group row using the same process outlined in iLab 5 Part 1 above.

Right-click the new cell and select Expression. Build this expression: =Sum(Fields!Sales.Value)

Step 7: Add the expression in the Category Name second column
The expression to appear in the blank Category Name cell will consist of some text and the name of the territory. Select the cell in group header row in the second column as pictured below:

After you’ve selected this cell, right click on it and select Expression. Enter this expression:
=”Total for ” & Fields!TerritoryName.Value

Step 8: Format the sales figures
The sales amounts need to be formatted for currency. Right-click the expression in the detail row of the last column and select Text box Properties. Click the format tab and select currency. You should also check Use 1000 separator (,). Repeat this for the aggregate function in the group header row.

Step 9: Finishing touches
Add a text box to the top of the report and give your report an appropriate title. Adjust the formatting by adding text color, background color, bolding, and underlining as you think is appropriate.

Step 10: Preview the report
Preview the report in its own window – right click the report in the Solution Explorer and select run. Note that the following image doesn’t include a title – I’ll leave that up to you.

1. Select the layout tab and take a screen shot.
2. Open up the Lab 5 Answer Sheet and complete the required steps. Make sure to include a screen capture of your report.
3. Preview your report by right-clicking the report in the Solution Explorer and selecting run.
4. Write your interpretation of the results. The interpretation must be a minimum of one paragraph (3 to 5 well-formed sentences) with no spelling or grammatical errors. Based on these results, what other data would you recommend that the business consider for further analysis?
5. Save your Word document.

Step 11: Submitting Your Work
You have now completed Lab 5 Parts 1 and 2. Well done! Submit your Word document to the Weekly iLab Dropbox.