![Close [x]](http://dw1.s81c.com/i/c.gif){kind=small}
Create the overall sales report
We will customize the first report template to create the overall sales report by adding a bar chart to visualize the annual sales of all products from the product line of Classic Models Inc.
First, we create dataset that will contain the aggregated sales for each product under
the classic cars product line. Double-click on overall.rptdesign to open it in
the Report Editor. In the Data Explorer view, right-click on Data Sets, then
select New Data Set to open the wizard. Type OverallDataSet for the name field and click Next.
Figure 9. Dataset wizard
We will construct a SQL statement that will retrieve the product name and the annual sales of each product. The annual sales are calculated by multiplying the product price by the aggregated amounted ordered through the year. In the field on the right-hand side (see Figure 9), type in the following query, then click Finish.
Listing 1. SQL query
select CLASSICMODELS.PRODUCTS.PRODUCTNAME,
sum(CLASSICMODELS.ORDERDETAILS.PRICEEACH \
* CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED) as "sales"
from CLASSICMODELS.ORDERS, CLASSICMODELS.ORDERDETAILS, CLASSICMODELS.PRODUCTS
where (CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER)
and (CLASSICMODELS.ORDERDETAILS.PRODUCTCODE = CLASSICMODELS.PRODUCTS.PRODUCTCODE)
and (CLASSICMODELS.PRODUCTS.PRODUCTLINE = 'Classic Cars')
group by CLASSICMODELS.PRODUCTS.PRODUCTNAME
|
You can choose to verify the query by selecting Preview Results.
Figure 10. Preview results
Click OK.
Next, we will use a column chart to visualize our annual sales. We will use a bar chart and flip the axis to make the product names display horizontally. We will customize the chart by configuring a number of cosmetic attributes to give you an idea of what you can do to change the visual effect of a chart. We will also add customized user interaction on the chart so a message box is displayed when the user clicks on a bar.
To create a bar chart: In the Report Editor, select the second row in the second grid component, right-click and select Insert > Chart, select Bar as the chart type, check the Flip Axis option, leave the default selections for the other fields, then click Next.
Figure 11. Select chart type
Next, we will bind the dataset to our chart. Although we flipped the axis, the x axis will still be the product name, and the y axis will be the sales amount. Click Use Data Set and select OverallDataSet from the drop-down list.
Figure 12. Select data
Click the Fx button beside the Category (X) Series field. In the
Expression Builder, select Available Column Bindings in Category Chart –
NewChart, then double-click on PRODUCTNAME to generate
row["PRODUCTNAME"] in the expression field. Click OK.
Figure 13. Expression builder
We will use the sales column for the Value (Y) Series. To do that, you can either follow the second step or just drag the header of the sales column from the Data Preview list and drop it in the Value (Y) Series field and click Next.
Now we will change the cosmetic attributes to make the chart look nice. In the tree view on the left-hand side, select Series. Select Value Series for the Color By field. Click the Series Palette button.
Figure 14. Format chart
We will fill in the bar with a nice gradient color. In the Series Palette Editor, select the drop-down list and click the Gradient button. Select the start color and end color of your choice. Then click the Close button in the lower-right corner to go back to the Format Chart wizard page.
Figure 15. Series Palette Editor
Now we will format our y axis to indicate that the values we are presenting are dollar
amounts. In the tree view on the left-hand side, select Chart Area > Axis >
Y-Axis. Click the button beside the Type field to open the Format Editor,
select Standard, add the $ in the Prefix
field, then click OK.
Figure 16. Edit format
Chart grid lines are horizontal or vertical lines extended from axes. They can make a chart easier to read. We will add vertical major grid lines in this chart. Click the Gridlines button, in the Major Grid section, check Visible, select a style you like, then close the editor.
Figure 17. Grid lines
Now we will hide the legend, since it is not needed in this chart. In the tree view, select Chart Area > Legend, uncheck the Visible option, then click Finish to complete the chart. The current chart size is too small. We need to resize it by selecting the lower-left corner of the chart and dragging the mouse pointer until the chart is enlarged to a reasonable size. Use Ctrl+s to save the report.
