Details of this Paper

BIS345 Lab 5 - with screenshots - Guaranteed A+




BIS-345: Data Analysis for Business;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.;;If you prefer to type your query, switch to the edit as text mode.;;;;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;;;Add table tool;;;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.;SQL pane;;;Field list;;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.;;;Use a more meaningful name for each of these fields.;

Price : $31