You sort data in one of two ways:

How to sort data through the query

How to sort data in BIRT

The instructions in this section assume that you already inserted data in a report.

BIRT and some databases sort string data according to UCS2 code point values, so uppercase letters precede lowercase letters. For example, “Z” appears before “a.” The following list of values is an example of how BIRT sorts string data:

     ANG Resellers
     AV Stores, Co.
     Alpha Cognac
     American Souvenirs Inc
     Anna's Decorations, Ltd
     abc Shops

Most of the time, report users prefer to view a list of names in simple alphabetical order, without regard to capitalization. To display string values in case-insensitive alphabetical order, use JavaScript’s toUpperCase( ) or toLowerCase( ) function to convert the values to all uppercase or all lowercase before sorting.

The following expression is an example of a sort key expression that you specify on the sorting page of Property Editor:

   row["CUSTOMERNAME"].toUpperCase()

Using this expression, the previous list of values appears alphabetically as:

     abc Shops
     Alpha Cognac
     American Souvenirs Inc
     ANG Resellers
     Anna's Decorations, Ltd
     AV Stores, Co.

When you create a group, BIRT’s default behavior is to group data by a single value, such as a customer name, an order ID, or a date. In the detailed orders report shown earlier in this chapter, each customer name starts a new group, and each order ID starts a new group within the customer group.

Sometimes, it is more useful to group data by a specific interval. A sales report, for example, can present sales by quarters, rather than by dates. Similarly, you can group data in a shipping report by weeks or months, rather than by dates.

Compare the reports in Figure 12-10 and Figure 12-11. The report in Figure 12-10 groups shipping information by dates.

The report in Figure 12-11 groups the same shipping information by weeks.

As the reports show, grouping by interval provides the following benefits:

The following sections describe in more detail how to group string, numeric, and date-and-time data by intervals.

Grouping string data by intervals

When you group string data by interval, the interval that you specify is a prefix of a particular length. For example, if a customer group sorts customers by name, you can group customers by the first letter of their names, or the first two letters, or the first three letters, and so on.

You typically group by the first letter to group names by letters of the alphabet. In a customer list, for example, you might want to group all customers whose names begin with A under the heading A, all customers whose names begin with B under the heading B, and so on.

You can group by multiple letters to group items whose names contain special prefixes for classification or categorization. A computer parts vendor, for example, might use the prefix ME for all memory chips, CP for CPU boards, MO for monitors, and so on. In this case, creating a computer parts list that groups names by the first two letters lends itself to logical groupings by part type.

Figure 12-12 shows the results of grouping names by the first letter, the first two letters, and the first three letters. Lines separate the groups.

Figure 12-12. Results of grouping string data by intervals

How to group string data by intervals

1. Create a group using the instructions in “How to group data,” earlier in this chapter.

2. Set the Interval field in the group editor to Prefix.

3. Set Range to the number of characters by which to group.

Grouping numeric data by intervals

When you group numeric data by intervals, you group by a range of numbers. For example, if an order group sorts orders by numeric ID, you can group the orders by intervals of 10, 50, 100, 1000, and so on.

The interval that is best for any set of numeric data depends on the range of numeric values. If the numbers range from 100 to 200, it makes sense to group in intervals of 10s. If the numbers range from 100 to 1000, you might want to group in intervals of 100.

Figure 12-13 shows the results of grouping numbers by intervals of 10, 100, and 1000. Lines separate the groups.

Figure 12-13. Results of grouping numeric data by intervals

Groups are calculated from the first value in the data set. If the first number is 1070, and you use an interval of 10, the first group contains values from 1070 to 1079, the second group contains values from 1080 to 1089, and so on. In the example report that shows a grouping interval of 1000, the numbers 2005 and 2030 are not in a separate group, which is what you might expect, because the first group contains numbers from 1070 to 2069. The second group contains numbers from 2070 to 3069.

Rather than using the first data set value as the starting, or base, value for determining the grouping of numbers, you can specify a different base value to group numbers in more predictable ranges. Compare the two reports in Figure 12-14.

Figure 12-14. Results of grouping with no base value and a base value of 1000

A base value of 1000 provides better results than a base value of 1070 when grouping by intervals of 1000. Rather than grouping numbers in groups of 1070–2069 and 2070–3069, the second report uses more logical groups of 1000–1999 and 2000–2999.

How to group numeric data by intervals

1. Create a group using the instructions in “How to group data,” earlier in this chapter.

2. Set the Interval field in the group editor to Interval.

3. Set Range to the desired grouping interval.

4. If you want to specify a starting value to use for calculating groups, select Use fixed base value for interval, and specify a number.

Grouping date-and-time data by intervals

When you group date-and-time data by intervals, you group data by time periods, such as hours, days, weeks, months, and so on. Grouping by time periods is useful for reports that display information that has a time or schedule focus, such as weekly shipping schedules or quarterly sales figures.

The reports in Figure 12-15 show the results of grouping dates by weeks, months, and quarters. The lines separate the groups. Weekly groups start on Mondays, monthly groups start on the first date of the month, and quarterly groups are January 1–March 31, April 1–June 30, July 1–September 30, and October 1–December 31. If you group by year, the groups start on January 1 and end on December 31.

Figure 12-15. Results of grouping date-and-time data by intervals

If you do not want to use the default start value for date-and-time groups, you can specify a different base value to group dates in different ranges. For example, if an organization’s fiscal year is October 1 to September 30, and you want to group ten years worth of data into yearly groups by fiscal year rather than by calendar year, you can specify a base value such as 10/01/1995. The report will display the data in the following groups: October 1, 1995–September 30, 1996; October 1, 1996–September 30, 1997; October 1, 1997–September 30, 1998; and so on.

How to group date-and-time data by intervals

1. Create a group using the instructions in “How to group data,” earlier in this chapter.

2. Set the Interval field in the group editor to one of the time period values, such as Year, Month, Week, Day, or Hour.

3. Set Range to the number of units to include in each group. For example, if you selected Week as the interval, specify 2 as the range to group data in two-week periods.

4. If you want to specify a different starting value to use for calculating groups, select Use fixed base value for interval, then specify a date.

When you create a group, the default setting specifies sorting the group values by the grouping field in ascending order. For example, if you create an order ID group, the default setting is to sort order ID values in ascending order. You can, however, sort the group values by a different field. For example, rather than sort group values by order ID, you can sort by order total.

Sorting at the group level is different from sorting at the detail row level. When you sort at the detail row level, you specify the sorting criteria on the Sorting page in Property Editor. When you sort at the group level, you specify the sorting criteria through the Groups page.

The report in Figure 12-16 groups sales data by product code. The group header, highlighted with a grey background, displays the product code and the sales total. The product code group uses the default sorting, which sorts by product code in ascending order, such as S10_1678, S10_1949, S10_2016, and so on.

Figure 12-16. Results of sorting group values in ascending order

The report in Figure 12-17 contains the same data as the report in Figure 12-16, but rather than sorting by product code, it sorts by the product sales total in ascending order.

Figure 12-17. Results of changing the sort order of groups

By changing the field on which groups are sorted, you can choose the information to emphasize. As Figure 12-17 shows, the report can group sales data by product code but list them in order of sales total, which is often a more useful way to present sales information.

How to sort data at the group level

When you create multiple groups, the order in which you create them determines how the report groups data. Before you create groups, think about their order. For example, if you want to group data by state, then by city, create the groups in that order. In other words, state is the table or list’s first, or top-level, group, and city is the second, or inner, group.

The reports in Figure 12-20 show the results of creating the state and city groups in different orders. The first report shows the output when the state group is the top-level group. The second report shows the output when the city group is the top-level group.

Figure 12-20. Results of creating groups in two different orders

Data in the first report is organized logically. The report shows each state in alphabetical order, then the cities are sorted alphabetically within each state. On the other hand, data in the second report is sorted by city first, resulting in repeated state headings that are organized in seemingly random order.

Figure 12-21 shows the report design for the first report. The state field appears in the group header 1 row, and the city field appears in the group header 2 row.

Figure 12-21. Report design grouping data by state, then by city

You can create groups using any of the following procedures. The procedure you use depends on your preference and whether you are creating all the groups for the first time or adding groups to an existing group structure.

All the techniques display the Edit Group dialog box, where you define the properties of a group. If you inadvertently create groups in the wrong order, you can easily change the order of the groups. You do not have to delete and recreate the groups.

The Groups page in Property Editor shows all the groups that you create for a particular table or list. You must use this page, shown in Figure 12-24, to change the order of groups. You can also add, edit, and delete groups using this page.

You cannot change the order of groups by moving data elements in the layout editor. This action affects only the display position of the data values. Compare the report designs in Figure 12-22. The state and city data elements are transposed by dragging and dropping in the layout editor.

Figure 12-22. Report designs with transposed state and city elements

Now, compare the corresponding report output, shown in Figure 12-23. The first report shows data sorted by state, then by city. The second report displays the city values above the state values. The report data, however, is still sorted by state first, then by city.

Figure 12-23. Reports showing the effects of transposing state and city elements

How to change the order of groups

If your report contains groups, you typically add a descriptive heading that appears at the beginning of each group to identify the data within the group. When you create a group, BIRT Report Designer automatically inserts a data element in the group header row to serve as the group heading. This data element displays the values of the field on which the group is based. For example, if you group data by state, the data element displays a state name for each group.

Unlike column headings, which are static, group headings change based on the content of the group. If a report groups data by customers, for example, you can display the customer name at the beginning of each group. For a customer list that groups names by the first letter, the titles for the groups are A, B, C, and so on. For a shipping schedule that groups data by weeks, the titles for the groups could be Week of 01/02/06, Week of 01/09/06, and so on.

The reports in Figure 12-25 show examples of headings that change with each group. Bold text highlights the group headings. If you do not use group headings, it is unclear where one group ends and another begins. Remove the A and B headings from the Customers report, for example, and the report looks like a list sorted alphabetically. You can add a line or space between groups to indicate a change in group, but a descriptive heading makes it easier for users to find information in the report.

Figure 12-25. Examples of group headings that change

Displaying group headings in the detail row

Headings that are in a group header row appear above the detail rows. Sometimes, a report looks better if the headings appear in the first detail row of each group. Compare the examples in Figure 12-26. The example on the left shows the group headings, which are order numbers, in a row above the detail rows. The example on the right shows the group headings in the detail row.

Figure 12-26. Two ways to display group headings

To display group headings on the same line as the first detail row, you drop the cell that contains the heading, using the Drop property. You cannot just move the group heading to the detail row, because it would be repeated for every row. To drop a cell, you must observe the following rules:

• There must be an empty cell below the cell that contains the group heading. Otherwise, the cell content overwrites the content in the cell below it.

• If a table contains multiple group header rows, you can drop only cells in the group header row directly above the detail row.

• You can only drop cells in a group header row. You cannot drop cells in a detail or group footer row.

You can drop a group header cell so that it spans the detail rows, or all the rows in the group, including the group footer row. You see a difference only if the group header cell has a border or background color. Compare the examples in Figure 12-27. The example on the left drops the group header cell to the detail rows. The example on the right drops the group header cell to all the rows in the group.

Notice that the cell color in the example on the left extends to the last detail row, whereas the cell color in the example on the right extends to the group footer row. If the cell did not have a background color, the output would look the same whether you set the cell’s Drop property to Detail or All. The order numbers always appear in the first detail row of each group.

Figure 12-27. Reports showing different settings for the Drop property

Figure 12-28 shows the portion of the report design that generates the previous example output.

Figure 12-28. Report design that generates a dropped cell

How to drop a group heading to the detail row

1. Select the cell that contains the group heading you want to drop. The cell directly below it must be empty.

2. On Property Editor, choose General from the list of properties.

3. For Drop, select one of the following values:

   • Detail—. Select this value to drop the group heading so that it spans only the detail rows.

   • All—. Select this value to drop the group heading so that it spans all the rows in the group, including the group footer row.

   These values display a difference in the generated report only if the cell has borders or background color.

4. Preview the report. The group heading appears in the first detail row of each group.

In the first tutorial, you created Customers.rptdesign in a project folder named My Reports. Open Customers.rptdesign using one of the following procedures:

The file opens in the layout editor, as shown in Figure 12-29.

Figure 12-29. Customer report design in the layout editor

Rather than editing directly the report that you created in the first tutorial, save Customers.rptdesign as a new file.

In order for the report to display credit limit data, you must add the CREDITLIMIT field to the data set.

In this procedure, you insert the credit limit field in the existing table.

The report is currently sorted alphabetically by customer name. Recall that in the first tutorial, you specified that the rows in the table be sorted by customer name. In this procedure, you group the data by credit limit in intervals of 50,000. When you group data, BIRT sorts the rows into groups first, then it sorts the rows within each group, assuming that you also specify a sort condition at the table level. As you will see when you complete this task, the data rows within each credit limit group will be sorted by customer name.

Rather than display the first value of each group in the group header, the report is easier to navigate if it displays the credit limit range for each group, as follows:

     0 - 49999
     50000 - 99999
     100000 - 149999

This procedure shows how to write a JavaScript expression to display these credit limit ranges. The procedure also shows how to create a column binding with which to associate the JavaScript expression.

One of the benefits of grouping data is that you can add summary, or aggregate, information at the beginning or end of each group. In this procedure, you display the number of customers in each group and the number of all customers that are listed in the report.

Now that the report displays the correct data, you can focus on improving the report’s appearance. You perform the following tasks in this section:

So far, you have been checking the report output in the BIRT Report Designer previewer. This time, you will use the report viewer to see what the report looks like when it is deployed. The report viewer provides additional functionality, including the capability of navigating to specific sections of a report using a table of contents. When you create groups in a report, BIRT automatically generates a table of contents, using the group values to show the hierarchy of the report.

Rather than display the first value of each credit limit group, the table of contents makes more sense if it displays the same credit limit range values as the report. You accomplish this task by using the same JavaScript expression that you used previously to display credit limit ranges (0 - 49999, 50000 - 99999, and so on) in the group header.