Developing Reports with Report Wizard

Topics covered in this section

Group Tab
       How to Use Group
       Ordering Your Data
       SpecialFunction
Sort Tab
       How to Use Sort
       Ordering Your Data
       Sorting Groups
       TopN
       Group Filter

Group Tab

Records in a report are usually organized into groups based on certain criteria. With the Group tab, enter the criteria to define multi-level groups. You can also specify the order of groups and order of records.

How to Use Group

To select criteria for the grouping, expand the table directory in the Report fields menu by double clicking the table icon or clicking the plus icon. Next, highlight the field you want as a criterion for the grouping. Lastly, click on the arrow button to place your criterion in the Group by menu. When a row in the group list is selected, you can remove it, change its order and group option. The same procedure applies for the Sort tab.

Note: If the type of the field that is selected to group the report is date or time, it can be added to the Group By panel more than once and you can set the special function for the field.

Ordering Your Data

Ascend and Descend
You will notice an up arrow besides each field selected to be grouped in the Direction column. This means that the fields will be grouped and sorted in an ascending order (A, B, C). Click on the up arrow icon and change it to a down arrow. This means that the fields will be grouped and sorted in a descending order (C, B, A).

Not Sort
If you select the NO Sort icon, your data will not be sorted, and JReport Designer will generate a group break according to the original order.

Special Group
The Special Group icon that is seen in the Direction column is a special way to define how to group your information.

For example, you placed a field named "Region" in the Group tab. This field contains all 50 states in the United States. We want to see the data between Maryland (MD) and New York (NY). In the Special Criteria section we can define this criterion by selecting "between" in the Operator drop-down menu and placing MD in the op1 menu and NY in the op2 menu.

User Defined Grouping Criteria
To add a new grouping feature, click on the Add button. This will bring you to the Group Name. Create a new name for your grouping criteria. Next, select an Operator function to group your data.

The following operators can be used to define how to group your data.

between
not between
> (greater than)
< (less than)
= (equal to)
<= (less than or equal to)
>= (greater than or equal to)
!= (not equal to)

Either op1 or both op1 and op2 will be seen in the Operand menu. Place your criteria here. JReport Designer will automatically place quotation marks on your criteria. Click Add to add more groupings. Click Remove to delete the current grouping, and OK to finish.

Keep values outside of the range in a special group
If checked, JReport Designer will keep those values that are not included in your specified criteria and place them in a special group.

Name for the special group
Give the special group a name here.

SpecialFunction

The SpecialFunction column next to the Direction column is available only when the field to be sorted is of date or time type. Click the drop-down arrow, a list will be displayed. You can select one to specify to group the records by each day, each week, each half month, and so on.

None
Records will be grouped by the selected data/time field. All the records that have the same date/time value will be displayed together as a group.

for each day
Records will be grouped by days. The records, of which the field values are in the same day, will be grouped together.

for each week
Records will be grouped by weeks. The records, of which the field values are in the same week, will be grouped together.

for each bi-week
Records will be grouped by double weeks. The records, of which the field values are in the same bi-week, will be grouped together.

for each half month
Records will be grouped by half months. The records, of which the field values are in the same half month, will be grouped together.

for each month
Records will be grouped by months. The records, of which the field values are in the same month, will be grouped together. .

for each quarter
Records will be grouped by quarters. The records, of which the field values are in the same quarter, will be grouped together.

for each half year
Records will be grouped by half years. The records, of which the field values are in the same half year, will be grouped together.

for each year
Records will be grouped by years. The records, of which the field values are in the same year, will be grouped together. .

Sort Tab

The Sort tab is very similar to the Group tab in appearance. It allows you to organize the information within the group by sorting records. You can select an item to indicate the group for which sort option will be applied.

How to Use Sort

To select a criterion for the Sort tab, expand the table directory in the Fields menu by double clicking the table icon or clicking the plus icon. Then highlight the field you want as a filter for the sorting and click on the arrow button to place your criterion in the Sort by menu.

Ordering Your Data

You will notice an up arrow beside each field selected to be sorted. This means that the fields will be sorted in an ascending order (A, B, C). Clicking on the up arrow icon, you can change it to a down arrow to sort data in a descending order (C, B, A) or you can customize to sort on runtime.

When you want to sort data dynamically by using a parameter, you can also customize the ordering way when running the report. For example, you selected the parameter "PSortby" to sort data, then click the up arrow beside "PSortby", choose and click the icon, you will get to the following dialog:

It allows you to define a parameter to specify ordering your data when viewing the report. Here we define a parameter named RSortby, click OK.

View the report, the parameter dialog pops up.

Click on PSortby to specify the field to sort your data, and click on RSortby to decide in which order to sort: Ascending or Descending.

Sorting Groups

By default, your selected field(s) will be sorted within the whole report. However, you can also select to further sort the groups by DBFields, summaries or formulas (record level).

To sort groups, please do as the steps below:

On the Sort tab of the Group Wizard, select one of the break-by field of the report from the Sort in drop-down list.

Select the DBField, summary or formula you want from the Field panel, and add it to the Sort By panel by clicking the arrow button.

In the Sort By panel, select the arrow to specify how to sort the groups, ascendingly or descendingly.

Click OK on the Group Wizard.

View the report, you will see the groups are sorted according to the value of the DBField, summary or formula you have specified.

Notes:

Multiple sorting fields and multiple sorting types are not supported, say, if you have sort the groups of a report by a DBField, you can not further sort it by another DBField, summary or formula.

When you sort the groups by a DBField, the order of the groups depends on the value of the first record in each group.

TopN

By checking the TopN box and place a number, you can specify that how many records or groups will be displayed in your report. You can also use a parameter whose type is Integer to control the value of TopN.

To display the top N records inside a group:

Launch the Group Wizard. On the Sort tab, select The whole report from the Sort in drop-down list.

Select the sort by criteria you want from the Fields pane, add it to the Sort by pane, and then change the arrow if necessary.

Check the TopN box, and then type the number you want.

To display the top N groups:
The steps for display the top N groups are very similar. The only difference is that, you should select the corresponding group-by criteria from the Sort in drop-down list, instead of The whole report.

Example: To display the top three groups.

In this example, we will design a report to display the best three sales according to their sales amount.

Create a new report named salesreport.cls. Here we use the demo Catalog: Tutorial.cat.

On the Date tab, add Orders and Orders_Detail tables to the data resource.
On the Join tab, select the Orders_Oder ID field from Orders table, drag the highlighted field to Orders_Detail table and onto the Orders Detail_Order ID field.
On the Display tab, add Orders_Order ID, Orders Detail_Product ID, Unit Price and Quantity to the report fields.
On the Group tab, add Orders_Employee ID to the group by criteria.
Click Finish.

Create a Formula "Total Price" to calculate the total price for each Detail Order. Click Insert, and then place this formula in the detail panel.

Create a summary based on formula "Total Price". Right click the formula, and then click Summary Function. The Insert Summary dialog will pop up. Check the Create a new summary radio box. Select Sum in the first drop-down list and Order_Employee ID in the second, click Insert, and then place the summary in GroupFooterPanel.

View the report, you will find that all of the Employees in the Orders table are displayed.

If you would like to display the top three employees with the highest sales summary, then you should launch the Group Wizard. On the Sort tab, select Order_Employee ID in the Sort in drop-down list. Add Sum_Total Price to the Sort by field, and then click the up arrow icon and change it to a down arrow. Check TopN box, and then type 3. View the report again, you will find that only the top three employees are displayed.

Group Filter

JReport Designer can filter records by where condition or local where, and it can filter groups or records by TOP N settings. JReport Designer allows users to suppress sections or make them invisible, fields and objects conditionally. However, for some special cases, you will have to define more formulae to control properties to reach your goals. In order to simplify your work, JReport Designer are provided with some special group filter functions.

Group filter operation is based on groups. If the group satisfies the condition, then all the records in this group will be shown. If the group doesn't satisfy the condition, then the whole group will not be shown in report result.

Let's use some examples to illustrate the functionalities of the Group Filter dialog.

First follow the steps below to create a new report.

Launch JReport Designer, select catalog Tutorial.cat, select a template and then click Create.

On the Data tab, add tables Customers and Orders.

On the Joins tab, join the two tables as Customers_Customer ID = Orders_Customer ID.

On the Conditions tab, modify the condition expression to display USA customers only (Customers.Country = 'USA').

On the Display tab, select Customers_Customer ID, Customer Name and Orders_Order ID.

On the Group tab, add Customers_Region to the Group by list.

On the Sort tab, add Orders_Order ID to the Sort by list.

Click Finish to close the Report Wizard, adjust your report to get a better view.

Now a basic report has been created, let's see some cases that you may probably face.

Case 1

Query: Show the regions where the smallest order ID is less than 3007.

Resolution:

Bring out the Group Wizard (Insert | Group/Sort), switch to the Sort tab.

Select Customers_Region from the Sort in drop-down list.

The Group Filter button becomes available, click it.

The Group Filter dialog appears. Fill in the fields like below:

Logic: Unchecked
Function: Min
Field Name (before Occurs With): Orders_Order ID
Field Name (after Occurs With): Orders_Order ID
Operator: <
Value: 3007
More: END

Click OK to close this dialog, and then click OK again to get back to the report design.

Click View to view the result.

The regions whose smallest order ID is less than 3007 are shown, while other groups not fitting this condition are ignored.

Case 2:

Query: Show regions where at least one order ID is larger than 3035.

Resolution:

On the Group Filter dialog, fill in the fields like below:

Logic: Unchecked
Function: Exist
Field Name (before Occurs With): Disabled in this case
Field Name (after Occurs With): Orders_Order ID
Operator: >
Value: 3035
More: END

Refer to the resolution of case 1 if you don't know how to access the Group Filter dialog.

View the report.

The regions, which have one or more than one order ID larger than 3035, are shown, while other groups not fitting this condition are ignored.

Case 3:

Query: Show regions where at least one "not smallest" order ID is less than 3020.

Resolution:

On the Group Filter dialog, fill in the fields like below:

Logic: Checked
Function: Min
Field Name (before Occurs With): Orders_Order ID
Field Name (after Occurs With): Orders_Order ID
Operator: <
Value: 3020
More: END

Refer to the resolution of case 1 if you don't know how to access the Group Filter dialog.

View the report.

The regions, which has one or more than one "not smallest" order ID less than 3020, are shown, while other groups not fitting this condition are ignored.

Notes:

In the Value field, you can input constants and parameters.

For string type constants, you can just type the string without quotation marks. For Date/Time type constants, you have to follow the Date/Time format specified on the Date Format tab of the Get JDBC Connection Information dialog.

Reference Notes: Reference | Dialog | Group Filter

Limitations:

You must ensure that the report result you get must have at least one record. Otherwise, JReport Designer will throw out an error message.

Index