IT452 Unit 10 Final Project Misty Tutoring Business

IT452 Unit 10 Final Project
In this project, you will create an end-to-end solution to meet a business need. This project is worth 200 points.

Scenario
Your good friend, Misty, has a tutoring business in Southern California. It is quite successful. A client will call with a tutoring need, and Misty will look on her Excel spread sheet of tutors for those qualified in the tutoring area the client needs, and who live near the client.
Misty now has approximately 20 freelance tutors registered with her. It has become quite difficult for her to tell what tutors are qualified and live near the client. You have told her you will develop a way for her to get such a list.
She has sent you the TutorList.xls file with the current list of tutors. (This file is available in Doc Sharing.) The Subject column has values of A (tutor is proficient in English and history), B (tutor is proficient in math and science), or C (tutor is proficient in all basic subjects). Hint: You need to create a database that consists of the data in these to files. The DateQualified column has the latest date the tutor was interviewed and checked out by Misty. In addition, she has provided the current term’s client list (Clients.xls). You will need to use BULK INSERT to load the data from the sheets into the tutoring database and write queries that find tutors in the same zip code as the client and provide the tutor in the same zip code in a report. Misty should be able to select the zip code as a parameter from the tutors in the report and the zip code as a parameter from the clients as a parameter to execute the reports.
Misty also requires that she should be able to interact with a Web page, because she does not want to know how to “run database software”. This will require creating a web based report that she can access via a web link.
The first thing you should do is list the steps in your plan. What will be the major steps you need to take to accomplish your goal? The first step will be to install SQL Server 2008 Express with Advanced Services on Misty’s computer. (She has agreed to that part of the plan.) The last step will be to create a report that asks for parameter values for a stored procedure or query at run time. This does not literally need to be done but should be a step in the plan if you advise her that it is needed on her machine.
For the purpose of this project, you will use your computer to work out the steps and create the report.
The first item to place in your final project document is your outline of major steps that need to be accomplished. Label this section:

1. Outline – 20 points
<Put your plan step list here.> (The outline will be worth 20 points.)
Then, for each step after the first (the installation step), make a section in your project, e.g.

2. <Whatever Step 2 is in your plan outline> – 80 points
In that section, state how you accomplished that step and/or give the code you used. Enough detail should be given so a knowledgeable person could completely recreate what you did. Show at least some of the results you obtained when you tested to see whether your step had been accomplished. (For
queries, 3 or 4 lines of output, and stating the number of lines returned is sufficient.) If a screen shot is the best way to demonstrate the results, include it in your project. Please use ALT+PrintScreen so that only the in-focus or active window, not the entire desktop, is copied.
[Note: You are starting with step 2, because step 1 of your plan is the installation, and it will be assumed that you know how to do that.]
[Hint: One of your steps should be the creation of a stored procedure that uses a variable for the zip code and determines which tutors live in the same zip code as the client. But, this step will be carried out in #3 below so you might want to letter your steps to avoid confusion.

3. Create the query or stored proc. that will produce the result set for the report – 40 points (Creation and Alter)
Create Procedure – 20 points
Alter Procedure – 20 points
The query / stored procedure that generates the result set for the report will be worth 20 points each. You then will also Alter the Stored Procedure one time and execute it. You should determine that it gives the desired results before configuring your report. Hint: The SELECT query portion of the stored procedure will be the query you use in your report datasource when you get to the portion of the design wizard (if you use the wizard instead of manually creating everything).

4. [The last step.] Configuring the Report and the Report Parameters – 60 points
Query for Report 20 points
Configuring Parameters – 20 points
Configuring/Designing 2 reports – 20 points
The final 60 points will be for the last step in your plan – configuring the report and the parameters for the report. The tutor subject choices should be A, B, or C. The date selection should be from the DateQualified values, with a default value of the earliest DateQualified. The customer zip code must be one of those in the TutorList. [Hint: The latter two parameter items require creation of additional data sets, one for the DateQualified values, and one for the zip code values. Hint: Each of the datasets should use a SELECT query as well to provide the data for the parameter. Look at what you did in Project 9 as an example. The DateQualified data set will have datetime data type. Each parameter will become a drop down on your report.
You should place four screen shots under this final step in your project document.
1. For the parameter that specifies Misty’s choice of a tutor that resides in the same zip code as the client show one such client/tutor example.
2. For the parameter that specifies Misty’s choice of minimum DateQualified, show how you have configured the Available Values for that parameter.
3. Show the Report Data pane of BIDS, showing all parameters and all data sets with their fields.
4. Deploy the report. Run the first report that does not contain parameters for zip code 91016. This one should show only the tutors available for that zip code. Run the second report with parameters for a customer zip code of 91803 for the report with parameters showing which client they qualify to work with, what their skill set is, and date equal to the earliest DateQualified value. Paste the screen shot of this report into your project document.
Because the entire project involves sequential steps, if you become hung somewhere in the middle and cannot proceed, you may send details to your instructor, and receive a hint in exchange for an appropriate point “cost” or deduction. If you cannot figure out what query you need to create the report, the instructor will send it to you, on request from you, for a “cost” of 20 points so you can move on.
Your screen shot of the report without parameters should look similar to this on the Report BIDS screen. It should be for ZipCode 91016 regardless to subject and qualified date:
Your screen shot of the report without parameters should look similar to this on the Report Manager screen:
Your screen shot of the report with parameters should look similar to this on the Report BIDS screen It should be for ZipCode 91803 regardless to subject and qualified date:
Your screen shot of the report with parameters should look similar to this on the Report Manager screen: