BIS-345: Data Analysis for Business
Lab 7 Questions:

Lab 7 Student Answer Sheet
Student Name:
Using Lab 7, provide copies of your SQL statements, query results and reports as specified in each section listed below.
Part A Territory Sales Dataset (6 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:

Part B: Territory Sales Report (10 points)
a. Paste your report here:
Part C: Territory Sales Report Interpretation (5 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. You should answer the following question based on these results.
• What other data would you recommend that should be considered for further analysis?

Part D: Category Sales Dataset (6 points)
a. Paste your SQL Statement here:
b. Screen print your results and paste here:

Part E: Category Sales Report (10 points)
a. Paste your report here:

Part F: Category Sales Report Interpretation (6 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. You should answer the following question based on these results.
What other data would you recommend that should be considered for further analysis?

Lab 7: Creating matrix reports
Overview
Using the AdventureWorks database, you have been requested to create two matrix style reports.
The AdventureWorks database contains many tables; we will be using only some of those tables in this 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 and create a new Report Server project. Refer to the ‘Week 4 Part 2 Lab on Simple Reports’ to review creating a new Report Server project. Be sure to use your last name and the lab number as part of the name for the new project.

Part 1: Territory Sales Totals by Year and Quarter Report
Create a matrix report showing territory sales totals by year and quarter. Provide an 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 considering for further analysis?
Here is an example of the finished report:

We will not be using the 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. Follow the directions in Part 1: Step 1 detailed in “Week 5 Lab on Reports with Drilldown” if you need to review creating a shared data source. Give your data source the same name as your project.
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. Follow the directions in Part 1: Step 2 detailed in “Week 5 Lab on Reports with Drilldown” if you need to review creating a new report.

Step 3: Create the dataset
This report requires both the names of the country and the name of the sales territory. The country name is in the CountryRegion table; the sales territory name is in the SalesTerritory table. These two tables can be related on the CountryRegionCode field. The total sales will be based on the SubTotal field in the SalesOrderHeader table; this field contains the total due for all the items on this order. There is also a TotalDue field that is the sum of the subtotal, tax, and freight amounts. We will need to sum the SubTotal field. The SalesOrderHeader also contains the order date field. The matrix report shows total sales per year and per quarter, so we will have to extract the year part of the date field. What SQL function will we need? We will also need to determine the quarter in which this date falls. What SQL function will we need to do this?
The SalesOrderHeader table is related to the SalesTerritory table on the TerritoryID field.
The three tables you will utilize are SalesOrderHeader, SalesTerritory, and CountryRegion.

Step 4: Add the fields to the dataset
Select the Name field from the CountryRegion table and the Name field from the SalesTerritory table. Give each of these an appropriate alias.
Select the SubTotal field from SalesOrderHeader.
The Year and Quarter fields will be based on functions.
To extract the year, use the DATEPART function utilized in the Week 6 iLab. Give this field an appropriate alias. The DATEPART function can be used to extract the quarter; however, the report shows the letter Q beside the quarter number (it could also show Qtr). This is text that must be added either here in the SQL statement or in the report itself. We will add it in the SQL statement. The DATEPART function returns an integer data type, which cannot be combined with the string “Q” without converting it. We will use the DATENAME function instead as this function returns a string. Build the following expression:
‘Q’ + DATENAME(QQ, OrderDate)
When you tab out of this column, the schema and table name will be added to the field name.
We need to sum the SubTotal field; select the Group by tool in the toolbar. Select Sum in the Group by column for SubTotal. This field must now be given an alias; give it a more meaningful alias.
Sort the output in order of Year, Quarter, CountryName, and TerritoryName. Change CountryName Sort Type to Descending. The United States is the only country with sales territories, so we will list this country first by sorting the country name in descending order.
Run the query to test it.

The designer will look like this:

Press OK twice. 
Step 5: Laying out the Report
Select the Layout tab.
From the toolbox drag a matrix control onto the report:

A matrix control differs from a table in that both rows and columns can be expanded. This feature is implemented as part of the Visibility features, just as in previous labs, and it is not automatically part of the matrix but must be added.

Step 6: Adding fields to the matrix
The Year and Quarter fields will go in the Rows area, whereas the country name and territory name fields will go in the Columns area and the sales total field will be in the Data cell.
Select the Data Report panel.
Drag the Year field and drop it onto the Rows cell.

Drag the Quarter field and drop it just to the right of Year; be careful not to drop it on the Data cell. If you look closely as you are dragging the field, you will see the border of the Rows cell change as you move over the cell to guide you where to drop the field. The matrix now looks like this:

 
Drag the CountryName field to the Columns cell.
Drag the TerritoryName field to the Columns cell and drop it just below the CountryName field, but not in the Data cell. Again, pay attention to the border of the Columns cell.

You can (and will have to) adjust the column widths and row heights.
Now the matrix will have the fields in the Rows and Columns:

The last field is the SalesTotal. Drag and drop it onto the Data cell.
If you preview the report, all the values will be in the correct locations, but we need to change the visibility to add the drill down effect.

Step 7: Add Drilldown
If you will remember from previous weeks, drilldown functionality is added to groups. By dragging and dropping fields into the Rows and Columns cells, groups were created automatically. The groups are listed under the Row Groups and Column Groups at the bottom of the report canvas. You will see that four groups have been created –two for the Rows and two for the Columns.

//////////////////////////////////////////////////////
We want to expand the Year field to see the quarters for each year. This means that we will control the visibility of the group for quarters. Right click on the Quarter group and select Group Properties…

Select the Visibility tab. Set the Initial Visibility to Hidden, check the “Display can be toggled by this report item:,” and select Year from the dropdown.

Click OK to return to the Matrix Dialog.
We will expand the CountryName field to see the sales territories for that country (if there are any). That means you will have to change the visibility for the TerritoryName group. Edit this group, select Visibility and set the appropriate options.

Preview the report. The groups can be expanded, but we need to do some formatting to make the report more readable.
 
Step 8: Format the report
All the columns are too wide. Adjust them each to more appropriate widths.
The rows may also need to have their height adjusted.
Set the formatting for the sales amount to display commas around the thousands and two decimals. We don’t need the “$” symbol at this time.
Change the text alignment of the territory name to be right aligned.
Add an appropriate background color to either individual cells or the entire row.
Add an appropriate report title.

Step 9: What to submit
Run the report. Right click the report in the Solution Explorer and select Run to open the report in a separate window. 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 up the Lab 7 Answer Sheet and complete the required steps. Make sure to include a screenshot of your report.
3. Preview your report by right-clicking the report in the Solution Explorer and selecting run.
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 should be considered for further analysis?
(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 Category and Year Report
Create a matrix report showing total sales amounts and the total quantity ordered for each category and subcategory per year and month. Provide an 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 considerimg for further analysis?
Here is an example of the finished report:

Step 1: Create a new report
Right-click the Reports folder in the Solution Explorer and select Add -> New Item. Make sure Report is selected. Give your new report a name that includes your last name and indicates the lab number and part number. Follow the steps in “Week 5 Lab on Reports with Drilldown” to review creating a new report.

Step 2: Create the dataset
This report requires the category and subcategory names; the category name is in the ProductCategory table while the subcategory name is in the ProductSubcategory table. These tables are joined on the ProductCategoryID field. The sales amount and the quantity sold values are in the SalesOrderDetail table. The LineTotal field will give us the sales amount, and OrderQty will give us the quantity sold. Both of these values will be summed. The matrix columns will show the year, and for each year, each month. In Part 1 of the lab, we used the SQL statement to return the year and quarter. In this part, we will add the OrderDate field once to the query and then take care of the columns in the report. This field is in SalesOrderHeader table.
So far, we know we need the Product, ProductCategory, ProductSubcategory, SalesOrderHeader and SalesOrderDetail tables. Can these tables be joined, or do we need extra tables? Look at the ER diagram.

Step 3: Add the fields
From the ProductCategory table select Name. From the ProductSubcategory table, select Name. Give both of these fields meaningful aliases, such as CategoryName and SubCategoryName.
From the SalesOrderDetail table, select LineTotal and OrderQty.
From the SalesOrderHeader table, select the OrderDate.
We need to sum the LineTotal and the OrderQty. Select the Group By tool. Use Sum in the Group By column for these fields. Give them meaningful aliases.
Sort the rows in CategoryName order and then in SubCategoryName order.

Step 4: Laying out the report
Click the design Layout tab. Drag a matrix control onto the report.

From the report data tab, drag CategoryName and SubCategoryName to the Rows cell. Remember to watch the border shape so you drop the second field into the Rows column and not the Data column.
The matrix will now have two row groups:

 
For Columns, we need the Year and for each year, the Month name. This time, we need to add the column groups manually instead of dragging and dropping the fields into the Columns cell.
You will see the two groups for Rows. There is one group already created by default for Columns. Select the ColumnGroup, right-click, and select Group Properties…. Give the group a more meaningful name; call it colYear. In Expression, enter the following:
=Fields!OrderDate.Value.Year

Click OK twice to return to the main workspace. Right-click in the column groups section to add a second column group. Select Add Group -> Child Group…. Give it the name colMonth.
 

 
In the dropdown for Expression, click Expression to open the expression builder. Build the following expression:

Click OK to return to the group properties. Click OK to return to the report canvas.
The matrix is now showing two column cells, but the first column cell looks empty. We have edited the first group in the Column, but we did not specify what to display in the text box. Right-click this cell and select Expression:

 
In the Edit Expression we will build the same thing we used as the expression for the group:
=Fields!OrderDate.Value.Year

Click OK.

The last thing to do is name the Columns. When you drag fields from the dataset and drop them onto the matrix, the textboxes are given names, but our textboxes will have generic names. Select the first Column group by clicking and dragging to highlight the <> text in the Year cell and right-click, select Placeholder Properties and in the Label Property, change it to txtName.

Select the second Column group showing months and do the same, but use the label name txtMonth.
 
Drag the TotalSales field to the Data cell; drag the TotalQty field to the Data cell and drop it to the right of TotalSales.

Adjust the column widths and row heights.
Preview the report.
There is still work to be done! We need to add the drill down; however, look closely at the columns. The year and month columns are not ordered. The SQL statement is ordering the rows on the category and subcategory fields only. We could change this to also order on the date, but we can also sort in the report.

Step 5: Sort the Dates
The sorting is going to be added to the groups that have been created. We need to edit the column groups. Select the first Column group, colYear. Click Group Properties, select the Sorting tab, and click Add. In the Expression dropdown, select the OrderDate field.

Click OK to return to the report layout.
Repeat this for the colMonth group; it will also be sorted on the OrderDate field.
Depending on the number of rows to be sorted, it may be faster and more efficient to use the ORDER BY clause on the SQL statement to sort the rows.

Step 6: Add drilldown
We need to be able to expand the categories to see the subcategories, so we will change the visibility of the subcategories group. We also want to be able to expand the years to see months in each year, so we will change the visibility of the month group.
Select the SubCategoryName group and right-click to select Group Properties. Select the Visibility tab. Set the Initial Visibility to Hide. Check the “Display can be toggled by this report item:” checkbox, and select CategoryName from the dropdown.

Click OK to return to the report layout.

Edit the colMonth group; select the Visibility tab. Set the Initial Visibility to Hide. Check the “Display can be toggled by this report item:” checkbox and select the txtName cell from the dropdown.
Note: You may need to select the txtName cell and view the cell name in the Properties Panel. To perform this action, select the txtName cell and go to the Properties Panel. Proceed to the General -> Name section and note the name. This represents the value you will need to select to create the drilldown.

Click OK to return to the report layout.
Preview the report.

Step 7: Format the report
Add a report title.
Format the sales amount for number: commas around the thousands and two decimal places.
Add a background color to the individual rows or cells.
Change the text alignment of the year column to left.
Change the text alignment of the month name column to center.
It may be easier to see the columns of figures if we draw a line on the border between each month. Select the heading cell containing the Total Qty text and the field cell; set the border style of the right border to solid:

 
Step 8: What to submit
Run the report. Right click the report in the Solution Explorer and select Run to open the report in a separate window.

Run the report – right click the report in the Solution Explorer and select Run to open the report in a separate window. 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 up the Lab 7 Answer Sheet and complete the required steps. Make sure to include a screenshot of your report.
3. Preview your report by right-clicking the report in the Solution Explorer and selecting run.
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 should be considered for further analysis?
(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.)

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