Question:

Assignment Overview

Using the classroom discussions last week and the knowledge you gained from last week's assignment, list the AdventureWorks most profitable products in a report, not a query. A product's Total Profit is calculated as the Total Sales for the product minus the Total Cost of those sales. Total Profit, Total Cost, and Total Sales are not fields in a table they must be calculated in your SQL statement. In this assignment you will print the relevant ERD, the SQL query, and the Report results to PDF files and submit the PDF files to this assignment in Blackboard. You will need to figure out which tables and fields you need to answer create the report. You will need to design the query that provides the required information, and you will need to create and execute the report that shows Product Number, Quantity Ordered, Total Sales, Total Cost, and Total Profit, sorted in descending order by Total Profit. You will only need to print the first five pages of your report to a PDF file.

Instructions:

1. After opening SQL Server, go to the AdventureWorks database in the left pane, expand the database, right click on database diagrams, click new database diagrams, and explore the ERD for AdventureWorks until you can identify the tables necessary to develop the query.

2. Right click on AdventureWorks database in the left pane, click new query, then in the new blank window right click and select Design Query in Editor. When the list of tables pop's up, and select the tables required to design the correct query.

3. Choose the fields necessary to create the query.

4. Once you have the fields in the query and the framework has been created by query designer (previous steps), modify the query to create the required calculated fields, add the grouping, and the sorting statements.

5. Open SQL Server Data Tools, click File, New, and Project. Then select Report Server Project Wizard, type a report name, and click OK.

6. Start the wizard, connect to the server and the AdventureWorks database, then go back to SQL Server and copy the query that you have already gotten to work and paste it into the query string box. Continue the wizard, selecting report options, and including all required fields on the report. After completion, press preview to see the results are as you expect.

7. Go then to the report design tab and add a heading "AdventureWorks Most Profitable Products" and also add a report footer including "Page n of m" where n is the current page number and m is the total pages.

8. Print the ERD, Query, and Results each to a PDF file that you submit on Blackboard.