Task 1:

Set up and query the data source

Before you start to design a chart, you must create a report design file for the chart, then set up the data source and data set that the chart uses. These tasks are discussed in detail in earlier sections of this book and in a tutorial in which you build a sample report. This tutorial builds on that knowledge, explaining how to select the data from the database that you use to build a sample pie chart.

1	Choose File →New→Report and name the new report design Chart.rptdesign. Choose Finish.

2	Build a data source for the report design file using the sample database, Classic Models.

3	Build a data set for the chart. Use the following data set name:

ChartData

Use the following SQL SELECT statement:

 

SELECT Products.ProductLine,

sum(OrderDetails.QuantityOrdered)

FROM OrderDetails,

Products

WHERE Products.ProductCode=OrderDetails.ProductCode

GROUP BY Products.ProductLine

ORDER BY Products.ProductLine

This SELECT statement gets values from the ProductLine column in the Products table, groups the results by product line, and calculates the sum of the order quantities for each group.

4	Preview the results of the query to validate it. If you created the SELECT statement correctly, you should see the data shown in Figure 14‑1.

Figure 14‑1

Previewing the data set

The first column lists product-line names. The second column shows the total orders for each product line. By default, BIRT uses sequential numbers to name generated columns, such as the sum column named 2 in Figure 14‑1.

5	To rename the sum column to something more descriptive, choose Output Columns from the list in the left pane. Use the following text for the alias for the 2 column:

TotalOrders

Use the following text for the display name for the 2 column:

TOTALORDERS

6	To close Edit Data Set and open the layout editor, choose OK.