Using the , you have been requested to create two reports. The first report will use report parameters; the second report will be based on a chart. 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, start SQL Server Business Intelligence Development Studio and create a new Report Server project. Refer to 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. Products go through cost adjustments, and each change in cost is detailed in the ProductCoststory table. You have been asked to produce a report detailing these product cost changes along with the start date of each change. The report will display the product name and number. Rather than display the full list of products, the user will select the category and subcategory of products to display. This report will utilize parameters 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. 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. Right-click the in the Solution Explorer and select . Make sure is selected; give your new report a name that includes your and indicates the and . Follow the directions in Part 1: Step 2 detailed in Week 5 Lab Reports with Drilldown in the Week 5 iLab tab if you need to review creating a new report. The report will list product cost details, and it will allow the user to select a particular category and then, from within that category, select a subcategory so that the report only displays the products in that subcategory. To do this, it makes use of both report and SQL parameters. Each report parameter is based on its own dataset, so this report will need one to list the categories, one to list the subcategories, and one to list the product details in that subcategory. The will contain the category name and category ID. The set will contain the subcategories for a particular category. This dataset will include a parameter based on the category ID. The dataset will be filtered based on the value of this category ID. The will contain details from the Product table and the ProductCoststory table; the product name and number fields are in Product while the start date and standard cost are in ProductCoststory. This dataset will also use a parameter. Finally, the product rows will be filtered based on a specific subcategory ID. When your new report opens, it should contain the . If it does not, open the Report Data panel using the . Create a new dataset and name it . Click on the button next to datasource and select : Click and select the Query designer. Click the tool or right-click the top pane and select Add Tables. We only need one table, . Select the and . We will it in . Test the SQL and click twice to return to the main window. Click on the and select . Name this dataset and . Switch to the . Click on the button. We need the table. From this table select the , andthe . We only want to select subcategories for a specific category ID. the : The parameter name begins with the @ symbol and must be meaningful, often taking on the name of the field being tested. For this report, we will shorten it. In the , the expression Notice the WHERE clause and the funnel added beside the field name in the top pane. Run the query to test it. You will be asked to supply a value for the parameter. The category ID for Bikes is 1; enter in the Value column for the parameter: The query will then display the results in the results pane displaying only the subcategories in the Bikes category: Click and then again to complete the dataset creation. Click on the and select Name this dataset , and use the shared data source. Switch to the . Click on the button. We need the table and the table. Select the , , and fields from ; select the and from . according to . The parameter for this dataset is based on . In the column . Run this query to test it. You will need to enter a value for the parameter. Road Bikes has a subcategory ID of 2. Here is a partial result list from the results pane: Your datasets should look like this in your screen: Click on the tab on the report canvas. This report will be based on the free-form layout. . We will place other report items into the list control. The list control will repeat for each row in the ProductList dataset. List control From the , the field and drop it the ; the field and drop it the . Put them in the top left area of the list: Dragging the fields from the ProductList dataset will tie the List control to this dataset. We will need to add some text beside these two fields to let the user know what they contain. beside the Name textbox; enter beside the ProductNumber textbox; enter From the , select : SSRS is smart enough to know there are two parameters in the data sets (SubCategoryList and ProductList) and lists these for us. For each parameter, we need to specify the value for its properties. Double-click on a parameter to access the properties. For , the to be . The prompt is displayed to the user of the report and should be something meaningful. The values for the CategoryID parameter come from a query. In the tab, select the radio button. Under , make sure is displayed. For , list the . This is the value we need to return when the user makes a choice. For field, select . This is what we want the user to be able to see and make a choice from. Repeat these steps for SubCategoryID: Prompt: Sub Category From query radio button DataSet: SubCategoryList Value field: ProductSubcategoryID Label field: Name Our report is not yet completed, but we can see how it is going. Select the tab. from the drop downs at the top of the screen. There will be a dropdown for Category and a dropdown for Sub Category. Sub Category is not available until Category has a value: Parameter drop downs: Make your selections from the dropdown and click on to generate the report. You may need to adjust the size of the textboxes. Return to the Layout tab. Return to the design layout and add two text boxes to act as column headings; one for Start Date and one for Standard Cost. Add some formatting to the headings: Some products will have multiple price changes. We want each price change detail to appear without repeating the product name and number details. To achieve this we must add a group to the first list. Select the from the and right click. Choose : Click the button and then click the button. In the category you will see fields, select it and in the values box you will the ; select the field and click to complete the process. From the toolbox, . Place this list below the headings: Inside this second list and below each heading, drag and drop the and the . To fine-tune the placement of these two fields, select both by clicking one of the fields and holding the shift key while clicking the other field. Now use the
tools on the toolbar to align them and adjust their size. Point to each tool for the tooltip to describe the tools purpose; you can also click and drag each field until the blue alignment lines appear. Preview the report. You may need to adjust spacing between controls, size of controls, bolding to make certain fields stand out, and so forth. Add a report title that includes the date. Refer to Week 4 Part 2 Lab on Simple Reports for a refresher on how to include the date. Format the standard cost to display currency. Right-click the standard cost textbox and select Text Box Properties. Select the number tab and click on the currency option from the category box: The start date is currently displaying both the date and time, but we need to display just the date. Right-click the and select . Select the ; click on the from the category box, then the date format you want to see: Click to return to the report. Use the in the toolbox to of the first list control to separate each product. You will be submitting a screen shot 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 the Lab 6 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. (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.) ////////////////////////////////////////////////////////////////////////////////// Create a chart that compares total product sales for each category in the years 2002 and 2003. 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. Is the chart based on adequate data? Is there something lacking? Can you make meaningful deductions? Based on these results, what other data would you recommend should be considered for further analysis? The report will look like this: Add a new report to the project. Use ,and in the report name. What fields are needed? What tables are these fields in? The problem asks for the total sales in each category for the years 2002 and 2003. We will need category names, which can be found on the ProductCategory table. How do we get the total sales? We will have to sum the line item total for each category and for each year but only return the results for years 2002 and 2003. The line item total is in the SalesOrderDetail table; the order date will need to be used for the year, and it is in the SalesOrderHeader table. So there we have three tables: . Can all these tables be joined or do we need extra tables in order to join them? Refer to the partial ER diagram. Add a new dataset. Give it a name and make sure it is using the shared data source. Switch to the graphical query designer. Add the tables needed for the query. NOTE: As you add all of the tables, the query designer will build the join clauses for you, but it may add in a few extra AND statements. These are not necessary, but you can leave them as they are. Select the from , from and from . The OrderDate field contains the actual day and month as well as the year of the order. We only want the year part of the date. Edit this field to use the DatePart function and return just the year: When you tab out of this column, the table name and schema will be appended to the field name. Give it an alias in the Alias column of SaleYear. We need to . Click on the on the toolbar. We will group by Name and then SaleYear. Select ; give this column an alias : We only want the total sales for years 2002 and 2003, so we need to . In the column, enter: A HAVING clause will be added to the SELECT. . It should return only there are four categories, so we will get the total sales over each of the two years for the four categories. Clear the results pane and click twice. Click on the design layout tab. This report is based on a chart. tool in the toolbox and drag it onto the report: You will be prompted to choose a chart type. For this lab, we chose a . The X-axis will display the categories; drag the from the dataset and drop it of the chart that says . The data series will be the SaleYear; drag the from the dataset and drop it of the chart that says . The chart will display total sales; drag the field from the dataset and drop it of the chart that says . Preview the chart, and you will see that a few adjustments are needed. We need to add some labels to both the x-axis and the y-axis, and we need to move the legend. To apply formatting to the chart, right-click the and select the appropriate properties: and select Enter a Name for the legend. Change the layout to display the legend in a row rather than a column and use the position graphic to place the legend at the bottom center of the chart. Press to accept the changes. Right-click the axis titles and select . Enter a title and format the title as you see fit. Do the same for the other axis title. Preview the report: As you can see, the numbers on the y axis are too large to be meaningful. Rather than display these numbers in millions, we will display them in tens. To do this, we will have to divide our data values by 1 million. Return to the design layout tab. Click the chart region so that you can see the data, series, and category fields. the on the chart. Select In the Edit Chart Value dialog that displays, click the . In the text box, you will see the expression =Sum(Fields!TotalSales.Value). We need to adjust this expression to divide by 1,000,000. You can type the change into the text box or click on the button and open an Expression dialog: Edit the expression: Click to return to the Edit Chart Value dialog. Click again. Preview the report and you will see the change to the y axis: We can . We would like to display from 0 to 40 on this axis. Return to the properties for the chart; and select . On , enter for the and for the : Click . If 40 is not displaying on the chart, you may need to size your chart to give it a larger area. Add an appropriate title above the chart. 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 6 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. (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, place your mouse where you want the screenshot to be placed and then right click and press Paste. The screenshot will appear in your Word document after a few seconds.) Write your analysis of the results after this screen shot. It must be a minimum of one paragraph (3 to 5 well formed sentences) with no spelling or grammatical errors. Is the chart based on adequate data? Is there something lacking? Can you make meaningful deductions? Based on these results, what other data would you recommend that should be considered for further analysis?