Computer_data analysis

BIS345: Data Analysis for Business

Lab 4 Part 2: Creating Simple Reports

Overview

Using the AdventureWorks database, you have been requested to create a report. The report will introduce reporting features such as using groups and a calculated field. The wizard will be used to create the report.

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:

You will log into the iLab Citrix Server, access SQL Server Business Intelligence Development Studio 2008 and create the reports using Report Server and the AdventureWorks database.

When SQL Server BI Studio opens, you will be prompted by a security warning. Click yes and place the checkmark on the do not ask me again.

If this is the first time you have used this software, there will not be a list of previously opened projects. To create a new project, select File -> New Project.

The New Project dialog box will appear. Select Report Server Project; be sure to select a specific location to save your project file. Use your last name and the lab number in the project name:

When the new project opens, it will look similarto the following.Look at the Solution Explorer – it is showing two folders – Shared Data Sources and Reports:

Now we are ready to create the report.

Creating the Report

A report has been requested that will show a full list of products offered by the company. Products are divided into categories and each category is further subdivided into subcategories. For example, the category Bikes consists of subcategories such as Mountain Bikes, Touring Bikes, etc.

Your report needs to list the products in each subcategory. Include the subcategory name, product name, and number along with the list price and standard cost. Add a calculated field to the report to calculate the difference between the list price and standard cost.

In the dataset, the subcategory name will be repeated for each product in that subcategory. On the report, the subcategory name should not be repeated.

The report must have an appropriate title, it must display the current date, and it should include page numbers. The finished report will look like this:

We will use the wizard to create this report.

Step 1

To add a new report, right clickthe Reports folderin the Solution Explorerand select Add New Report. The Report wizard will begin and step you through the process of creating this report.

Report Wizard Step 1 is a Welcome Window.

Click Next to continue.

Step 2

Report Wizard Step 2 – Select the Data Source. If this is a new project, you will create a new data source.

Enter a meaningful name for the datasource – a suggestion is to use the same name as the project for shared data sources.

To create the connection string, click the Edit button.In the Connection Properties window, enter the server name provided to you by your instructor.

Ensure Windows authentication is selected.

When you have entered a valid server name, you will be able to access the bottom half of this window where you can see a drop down for ‘Select or enter a database name’. Enter the Server Name bis345sql2008. Click on the dropdown and select the BIS345_AdventureWorks database. If you do not get a list of database names in the Connect to a database section of the screen, then your server name is incorrect; you will need to re-enter theserver name bis345sql. Your screen should look like the following:

Click OK to return to the Report Wizard Select the Data Source screen; it will now look like this:

Make sure you check the ‘Make this a shared data source’ check box in this step. It is more efficient to share a data source between multiple reports in a project.

Click Next.

Step 3

Report Wizard Step 3 – Design the Query will appear.

In this window, you can type the SQL into the query string window or use the Query Builder option. We will use the Query Builder.

Select theQuery Builder buttonto open the next window showing the generic query builder.

This is the Query Designer:

There are two query designers – generic and graphical. The default layout is the graphical query designer. You can click on the Edit As Text button on the top left of the toolbar to switch to the generic designer.Using the graphical designer is similar to building a query in Microsoft Access using Query by Example.

These instructions will guide you through the use of the graphical designer.

Step 4

Now we are ready to design our query. What fields do we need? What tables are those fields in?

To build this report we need the subcategory name from the ProductSubcategory table; and product name, product number, list price, and standard cost from the Product table. We must first add tables to our query. Click the Add Table buttonon the top right of the toolbar or right click in the top pane and select Add Table.

Double click each table you will need in the query - ProductSubcategory and Product. Then close the Add Table window.

The designer adds the tables and shows the relationship between them; it also starts to build the SELECT statement automatically:

Step 5

Now we need to select the fields we want. In the top pane, place a check mark beside each field you want in the query: Name in ProductSubcategory; Name, ProductNumber, ListPrice, and StandardCost in Product. Scroll down in the Product table to see the ListPrice and StandardCost fields. In the second pane, you will see the columns that have been selected for the query. Both the ProductSubcategory table and the Product table have a field called Name – and we selected both fields; we can’t have two fields in the query with the same name. The Query Builder will give the second Name column an alias of Expr1 – you will see this in the Alias column beside this field. Use the Alias column to change this to ProductName; give the first Name column an alias of SubCategoryNameand give the second Name column an alias of ProductName. This process isn’t required, but will be helpful when we are designing the report.

Use the Sort Order column to sort first in SubCategoryName order and then within each subcategory, in ProductName order. Select the SubCategoryName Sorted Order column and enter a value of 1. Likewise, select the ProductName Sort Order and enter a value of 2. This process will create your Order by clause and denote the order in which your data will be organized.

Step 6

Run the query to test it – click on the red exclamation markbuttonon the top center area of the toolbar. The results will appear in a grid in the bottom pane.

When you are satisfied with the query, right click the results pane and select Clear Results. This process will free up used memory.

Click OK to close the graphical designer and continue with the Report Wizard.

We will now be returned to the Design the Query dialog in the wizard. This window will now display the SELECT statement from the query we just created.

Click Next to continue.

Step 7

Report Wizard Step 4 -Select the report type. For this report, we are using Tabular. This will give us a table layout.

Click Next.

Step 8

Report Wizard Step 5 – Design the Table.In this screen, you will decide how you want the fields in the report to appear.

We want the subcategory name to act like a group and only appear once for each new group. To perform this action, place SubCategoryName into the Group section of the Displayed Fields.

Put all of the other fields into the details section. The screen will look like the following:

Click Next to continue.

Step 9

Report Wizard Step 6 – Choose the Table Layout. Leave all options as they are.

Click Next to continue.

Step 10

Report Wizard Step 7 – Choose the Table Style. Select an appropriate style. In the image below, Slate has been selected.Additionally, all color and font choices can be changed later after the report has been created.

Click Next.

Step 11

Report Wizard Step 8 – Completing the Wizard. Give the report an appropriate name to identify it; include your last name in the report name e.g. FrazerLab4Part2. Don’t worry if the connection string in your report summary doesn’t exactly match the one in the image.

Click Finish.

You will see your report in the Layout tab. It currently consists of a textbox with the report title and a table containing rows and columns.

Step 12

To see what the report looks like, click the Preview tab.

Step 13

As you can see from the preview, you need to make some adjustments. Return to the Design tab. Click any cell in the table to select the table. When the table is selected, the column headers and row indicators are visible. The table has 3 rows – a header row containing text for column headings; a group header row that will display the name of the subcategory, and a detail row that will display the values from each row in the data set. The group header and detail rows contain expressions that begin with an equal sign and include the actual field name from the data set.

( Column Header ) ( Row Indicator )

( Group header row ) ( Heading row ) ( Detail row )

Step 14

Adjust column widthsby moving the mouse over the line to the right of the column header; when your mouse turns into a double headed arrowclick and drag to change the width as needed.

Step 15

Merge the cells that contain the column headings for the 2nd and 3rd columns. These columns are labeled product name and product number. Begin by selecting both cells by clicking and dragging across the cells, thenright click and select Merge Cells.Replace the text in the merged cell with ‘Product’.

Center the text in the cell by setting the TextAlign property in the Property Sheet.

Step 16

Change the heading text in the first row as needed. The wizard uses the field names as suggested column headings and you may need to adjust this value. Click into the cell and make your changes.Note you do notneed anequal sign if the cell will display text only.

Step 17

We need a calculated field to show the difference between the list price and the standard cost. Add a column to right of last columnby right clickingthe heading of the last columnand select Insert Column -> Right.

Now we need to build the expression that this calculated field is based on. Right click the detail cell of the new column and select Expression.

An Edit Expression dialog box opens that you will use to build the expression. An expression starts with an equal sign. Select Fields in the column on the left. Note how all of the fields in the data set will be listed in the left hand column. Double click a field to add it to the expression in the top pane. It will be added in the format: Fields!FieldName.Value where FieldName is the actual name of the field in the data set. Double click the first field, enter a space, enter a minus sign, enter another space and double click the next field so the expression looks like the following:

Click OK to close the Edit Expression dialog.

Give this column a meaningful heading(e.g. Difference).

Step 18

Each of the number fields need to be formatted for currency; right click the textbox for ListPrice, selectText BoxProperties.Select the Number tab and set the Category to Currencyand click OK.

Repeat this step for the standard cost field and the calculated field.

Step 19

The report title currently shows the name of the report. We need to change this and also include today’s date on a second line.

Right click the textbox for the report title, select Expression. Build the following expression:

vbNewLine is a predefined constant that will insert a new line in the heading. The Today function returns the current date. Every time the report is viewed or printed it will display the current date.

Step 20

Insert a page header to display page numbers. Select theReport menu option on the Menu bar; from the list of choices selectAddPage Header to add a page header band at the top of the report. Anything in this area will appear once at the top of each page. Insert a textbox into the page header by selecting the page header, right clicking, and selecting insert -> Text Box. Feel free to alter the text box size to meet your needs. Right click thetext box and select Expression.

Select Built-in Fields in the left column and in the second column you will see a list of global variables that can be used. There is a variable for the page number and a variable for the total number of pages. Double click a variable to add it to the expression. Any text you want in the expression must be placed in double quotes.

Step 21

To turn off the bordersfor a particular row, select the row by clicking on the row indicator.

In the Property Panel, set the BorderStyle property to None.

Use your judgment to change the appearance of the report. The following properties will be useful:

BackgroudColor –changes the background color of the cells.

Color –changes the text color of the text in the cells.

TextDecoration – adds or removes underlining of the text.

TextAlign –specifies the alignment of the text in the cell.

Submitting your lab

You will be submitting a screen shot of your report in the layout tab and a screen shot of the running report.

1. Select the layout tab and take a screen shot.

2. Open up theLab 4 Answer Sheet.

3. Still in Word, put your mouse under the heading information and paste your first snap shot into this Microsoft Word document.

4. Return to your report. To run the report, right click the report name in the Solution Explorer and select Run.

This will open a new window for the report.

5. Take a screen shot of the report in this window. Your screen shot must show at least four of the category groups and their products; it must show all of the columns.

(To take a screenshot, press CTRL-ALT-PRINTSCREEN. 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).

You have now completed Lab 4 Part 2. Well done! Submit your word document to the Weekly iLab Dropbox.

1

Product (Production)

ProductID

Name

ProductNumber

MakeFlag

FinishedGoodsFlag

Color

SafetyStockLevel

ReorderPoint

StandardCost

ListPrice

Size

SizeUnitMeasureCode

WeightUnitMeasureCode

Weight

DaysToManufacture

ProductLine

Class

Style

ProductSubcategoryID

ProductModelID

SellStartDate

SellEndDate

DiscontinuedDate

rowguid

ModifiedDate

ProductCategory (Production)

ProductCategoryID

Name

rowguid

ModifiedDate

ProductSubcategory (Production)

ProductSubcategoryID

ProductCategoryID

Name

rowguid

ModifiedDate

ProductCostHistory (Production)

ProductID

StartDate

EndDate

StandardCost

ModifiedDate

SalesOrderHeader (Sales)

SalesOrderID

RevisionNumber

OrderDate

DueDate

ShipDate

Status

OnlineOrderFlag

SalesOrderNumber

PurchaseOrderNumber

AccountNumber

CustomerID

ContactID

SalesPersonID

TerritoryID

BillToAddressID

ShipToAddressID

ShipMethodID

CreditCardID

CreditCardApprovalCode

CurrencyRateID

SubTotal

TaxAmt

Freight

TotalDue

Comment

rowguid

ModifiedDate

SalesOrderDetail (Sales)

SalesOrderID

SalesOrderDetailID

CarrierTrackingNumber

OrderQty

ProductID

SpecialOfferID

UnitPrice

UnitPriceDiscount

LineTotal

rowguid

ModifiedDate

SalesPerson (Sales)

SalesPersonID

TerritoryID

SalesQuota

Bonus

CommissionPct

SalesYTD

SalesLastYear

rowguid

ModifiedDate

SalesTerritory (Sales)

TerritoryID

Name

CountryRegionCode

[Group]

SalesYTD

SalesLastYear

CostYTD

CostLastYear

rowguid

ModifiedDate

Contact (Person)

ContactID

NameStyle

Title

FirstName

MiddleName

LastName

Suffix

EmailAddress

EmailPromotion

Phone

PasswordHash

PasswordSalt

AdditionalContactInfo

rowguid

ModifiedDate

CountryRegion (Person)

CountryRegionCode

Name

ModifiedDate

Employee (HumanResources)

EmployeeID

NationalIDNumber

ContactID

LoginID

ManagerID

Title

BirthDate

MaritalStatus

Gender

HireDate

SalariedFlag

VacationHours

SickLeaveHours

CurrentFlag

rowguid

ModifiedDate