Lesson 2: Creating and performing data analysis on page reports

The easiest way to create a page layout ad hoc report is to use the JReport Standard Report Wizard. There are several types of reports in the wizard which collects information for you: blank (no component), banded, crosstab, table, and chart. You can add components to or remove them from the report after it is generated by the wizard. In this lesson, we will create a page report that contains three report tabs, and then perform data analysis on them to look at the data in another way.

Data analysis is a process of evaluating data using analytical/logical reasoning. In JReport, cube elements can be added to existing data components. Cubes greatly enhance your ability to discover useful information, draw conclusions and support decision making process.

This lesson contains the following tasks:

• Task 1: Create a banded report
• Task 2: Create a table report
• Task 3: Create a crosstab report
• Task 4: Insert a chart
• Task 5: Analyze data of a banded object
• Task 6: Analyze data of a table
• Task 7: Analyze data of a crosstab
• Task 8: Analyze data of a chart

Task 1: Create a banded report

1. On the JReport Console > Resources page, go to the Public Reports folder, then click New > Report on the task bar.
2. In the Select Report Type dialog, choose the Page Report option and then click OK.
3. In the New Page Report dialog, type Customer Orders Report in the Report Title text field, select Banded in the layout box, then click OK.

   

   The Banded Wizard appears.

4. In the Data screen, select WorldWideSalesRC in Data Source 1 from the Available Data Resources list. Click Next.

   A page layout ad hoc report is supported by only one business/report cube. The definition of a business/report cube determines what can be done on a report. Typically this information is communicated to the business analyst who builds the report.

5. In the Display screen, expand the Orders Detail category in the Resources box, add Order Date and Total in it as detail fields one by one by clicking . Click Next.

   

6. In the Group screen, select Country in the Customers category and click to add it as a group by field, keep Ascend as the sort manner, and then click Next.
7. Skip the Summary screen and click Next, the same to the Query Filter screen.
8. In the Style screen, set the style to ClassicBlue, and then click Finish to create the report.

   The report appears as follows in Page Report Studio:

   

9. Click Menu > File > Rename Report Tab.
10. In the Rename Report Tab dialog, enter CustomerOrders as the report tab name, then click OK.
11. Click the Save button on the toolbar.
12. In the Save As dialog, type CustomerOrders in the File Name text field, then click OK. In the prompt message window, click OK.

Task 2: Create a table report

1. In the current open report window, click the New Report Tab button on the toolbar.
2. In the New Report Tab dialog, enter Current Customers in the Report Title text field, select Table (Group Above) in the layout box, then click OK. The Table Wizard appears.
3. In the Data screen of the wizard, select WorldWideSalesRC in Data Source 1, then click Next.
4. In the Display screen, expand the Address category, then add Customer Name, Phone and CustomerCityStateZip (change its display name to Zip) as the detail fields. Click Next.

   

5. In the Group screen, add Country from the Customers category as the group by field, keep Ascend as the sort manner, then click Next.
6. Skip the Summary screen and click Next to access the Query Filter screen.

As we would like to focus on customers not in USA, we will apply a filter to the report cube. However, we find that there is no predefined filter in the report cube, so we will define one.

7. In the Query Filter screen, define the filter as Country != 'USA', then click Next.

   

8. In the Style screen, set the style to ClassicBlue, and then click Finish to create the table report.

   The table report appears as follows:

   

9. Click Menu > File > Rename Report Tab. In the Rename Report Tab dialog, enter CurrentCustomers as the report tab name, then click OK.
10. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Task 3: Create a crosstab report

1. In the current open report window, click the New Report Tab button on the toolbar.
2. In the New Report Tab dialog, type Sales Report in the Report Title text field, select Crosstab in the layout box, then click OK. The Crosstab Wizard appears.
3. In the Data screen, select WorldWideSalesRC in Data Source 1 and then click Next.
4. In the Display screen, add Country from the Customers category in the Resources box as the row field and Category from the Products category as the column field (keep Ascend as the sort manner for both columns and rows), then add Total Sales from the Orders Detail category as the summary field. Click Next.

   

5. Skip the Query Filter screen. Click Next.
6. In the Style screen, set the crosstab style as ClassicBlue, then click Finish to create the crosstab report.

   The crosstab report appears as follows:

   

7. Click Menu > File > Rename Report Tab. In the Rename Report Tab dialog, enter Sales as the report tab name, then click OK.
8. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Task 4: Insert a chart

In this task, we insert a chart into the Sales report created in Task 3.

1. In the current report window, click Menu > View > Toolbox to display the Toolbox panel.
2. Drag Chart to place it under the last row of the crosstab.

   

   The Chart Wizard appears.

3. In the Data screen, select WorldWideSalesRC in Data Source 1, then click Next.
4. In the Type screen, keep the default chart type Clustered Bar 2-D and click Next.
5. In the Display screen, add Sales Year from the Orders Detail category to the Category box and Category in the Products category to the Series box, then add Total Sales in the Orders Detail category to the Show Values box. Click Next.

   

6. Skip the Query Filter screen and click Next.
7. In the Style screen, set the chart style to ClassicBlue, and then click Finish to create the chart.

   The chart appears as follows:

   

8. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Task 5: Analyze data of a banded object

1. In the current report window, click CustomerOrders from the Go To drop-down list on the toolbar to switch to this report tab.

   

Now, we want to make the records in each Country group further grouped in product category, and to know the discount.

2. On the left side, close the Toolbox panel by clicking X on the Toolbox title bar, then click Menu > View > Resource View to display the Resource View panel.
3. In the panel, expand the Products category, drag Category to the banded object and drop it as the mouse pointer moves below the Country group header and a tip Group Header appears.

   

   The report tab appears as follow:

   

4. Drag the Discount field in the Orders Detail category to the detail panel of the banded object:

   

   Now the report tab looks as follows:

   

Next, we will do some adjustments to improve the appearance of the report tab.

5. Right-click the label Customer Orders Report and select Properties from the shortcut menu. Then in the Label Properties dialog, go to the Font tab, check the Bold option and then click OK.
6. Right-click any value of the Order Date field and select Properties from the shortcut menu.
7. In the General tab of the Data Field Properties dialog, set the Format property to MM/dd/yyyy and click OK.

There are two group levels in the banded object, so we will change the background color of the Category group in order to distinguish the two levels.

8. Right-click the group header panel of the Category group, which is shown as GroupHeaderPanel1 on the shortcut menu, and click Properties on the menu.
9. In the General tab of the Banded Panel Properties dialog, set the Background property to #668cb2, and then click OK to confirm.

Click the Next button on the toolbar (or just simply scroll down the mouse wheel) to navigate to the next page (page 2). Repeat this to navigate to the following page. There is a large space or gap before each new Category begins. This is caused by a blank Group Footer panel at the end of each category. To remove the Group Footer panel, set it to invisible.

10. Right-click the GroupFooterPanel1 which is footer panel of the Category group and select Properties from the shortcut menu.

    

11. In the General tab of the Banded Panel Properties dialog, set the Height property to 0 and click OK.

    Now the report tab looks as follows:

    

Next, we will sort and filter records. The totals will be sorted in ascending order for each group. The records will be filtered by discount. The report will only display orders in which the discount is 30.

12. Click the Sort button on the toolbar to open the Sort dialog.
13. In the Sort dialog, select BandedObject from the Sort in Scope drop-down list, choose Total from the field drop-down list, keep the sorting manner as Ascend, and then click OK to apply the settings.

    

14. Right-click any value of the Discount field, and on the shortcut menu, click Filter > 30.00.

    

    After doing the sorting and filtering, the report tab looks as follows:

    

In the above two steps, we used two ways to sort and filter the report results: one by dialog and the other by right-click menu command. In fact, both sorting and filtering can be achieved by either way.

We have defined two grouping levels in the banded report: one by Country and the other by Category. Next, we will change one of the grouping criteria: grouping the banded object by Product Type instead of Category.

15. Right-click any of the Category value ("Bold" for example) and click Drill To > Product Type on the shortcut menu.

    

    The report result finally changes to:

    

16. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Task 6: Analyze data of a table

1. Select CurrentCustomers from the Go To drop-down list on the toolbar to switch to this report tab.

First, we want to remove the zip information from the table as we think it is not required.

2. Right-click the Zip label and select Remove Column from the shortcut menu to delete the Zip label and its corresponding DBField.

   

Next, we will add a group Region, and make its level higher than the existing Country group.

3. From the Resource View panel, drag Region in the Customers category above the Country group header and drop it when a horizontal blue line appears above the group header Country.

   

Next we want to add some detail fields to the table. We can do this either by dragging or using the dialog.

4. Drag State from the Resource View panel to the top right boundary of the Phone column. Hover over the right boundary of the Phone table header and drop it when a vertical blue line appears to the right of Phone. This requires careful mouse movements and a steady hand. Click OK in the prompt message.

   

   The report tab appears as follows:

   

5. Click anywhere on the table, when the icon appears at its upper left corner, right-click the icon and select Insert > Detail Column from the shortcut menu.

   

6. In the Insert Detail Column dialog, expand the Address category, then select Address 1 and click OK. A detail column, Address 1, will be added to the right of the State column.

   

However, we now consider the state information is not very useful because we will care more about the city information. So we can overwrite State with City.

7. Drag City in the Customers category from the Resource View panel to the State column label and drop it:

   

   The report result becomes:

   

8. Resize the Customer Name column by dragging the boundary to the right of the column to make all the customer names in the column displayed entirely.

   

9. Repeat the resizing step for the Phone and Address 1 columns.

   The report result finally changes to:

   

10. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Task 7: Analyze data of a crosstab

1. Click Sales from the Go To drop-down list on the toolbar to switch to the report Sales, which contains a crosstab and a chart.
   
2. Right-click USA in the row header, and select Drill to by Value > Territory to obtain a more detailed view of USA information.

   

   The crosstab is regenerated to show data of territories in the USA:

   

Next, we want to further view the details about the territory Southeast of USA. Because Territory is a level predefined in the hierarchy Geography of WorldWideSalesRC, we can drill it down to the Country level.

3. Right-click Southeast, USA in the row header, and select Drill Down > Country from the shortcut menu.

   

   Now, only data of countries in the Southeast territory is displayed:

   

We can further drill any Country down to State in this way if necessary. Here, let's leave the crosstab as it is.

Next, we want to pivot the crosstab, namely interchange the rows and columns.

4. Click any cell of the crosstab, then click Menu > Report > Rotate Crosstab, and the crosstab changes to:

   

5. Right-click any value and select Properties from the shortcut menu.
6. In the General tab of the Data Field Properties dialog, set the Format property to $#,###.##, then click OK to confirm.
7. The report result finally changes to:

   

8. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Task 8: Analyze data of a chart

Now let's focus on the chart at the lower part of the Sales report. In JReport, a chart can be converted to a crosstab, and vice versa. So we will convert the chart to a crosstab first.

1. Right-click anywhere on the chart and select To Crosstab from the shortcut menu.
2. In the Display tab of the To Crosstab dialog, add Sales Year to the Columns box, Category to the Rows box, and Total Sales to the Summaries box, then click Next.

   

3. In the Style tab, set the crosstab style as ClassicBlue, and then click OK. The chart is successfully converted to a crosstab.

   

Now, we want to return the crosstab to a chart, and change the chart type to pie chart.

4. Click the menu command Menu > Edit > Undo to cancel the operation of converting chart to crosstab.

   We can also select any cell of the crosstab and click Menu > Report > To Chart to convert the crosstab to chart.

5. Right-click anywhere on the chart, and on the shortcut menu, click Chart Type > Pie > Clustered Pie. The chart appears as follows:

   

Next, we will format the chart.

6. Right-click the platform of the chart and select Format Chart from the shortcut menu.
7. In the Chart Type tab of Chart Definition dialog, select Bench from the Chart Type box, and select the Bench 3-D thumbnail.
8. Click the Display tab, select Sales Year in the Category box and click to remove it, then expand the Products category in the Resources box and add Category to the Category box. Remove Category from the Series box and add Sales Year in the Orders Detail category to the box. Add Total Sales in the Orders Detail category to the Show Values box.

   

9. Click the Style tab, apply the ClassicBlue style to the chart, then click OK to apply the changes.

   The chart now appears as follows:

   

10. Right-click the chart, select Format Paper from the shortcut menu.
11. In the Format Paper dialog, click the Coordinate tab, set Scale X to 80, Scale Y to 80 and Angle Y to 12, then switch to the Graph tab and set Bar Width to 30. Click OK to confirm.
12. Right-click the chart and select Format Platform on the shortcut menu.
13. In the Format Platform dialog, click the Border tab, set Border Type to solid and Color to #66ccff, then click the Data tab, set Sort Category to ascend and Sort Series to descend. Click OK to confirm.

    Finally the chart looks as follows:

    

Next, we want to apply a filter to the report cube the chart uses to show any category which is not Exotic.

14. Right-click anywhere on the chart and click Query Filter on the shortcut menu.
15. In the Query Filter dialog, specify the filter condition as follows . Click OK to apply the changes.

    

    We can find that the filter is then saved as a user defined filter in the report cube.

    The chart now becomes:

    

16. Click the Save button on the toolbar. In the Save Report Template dialog, click Yes to save the report, then click OK in the prompt message window.

Lesson 2 summary

In this lesson, we created three report tabs in a page report. The first report tab is a banded report, the second contains a table. In the third report tab, we created two components: a crosstab and a chart. Then, we performed some data analysis tasks on the four data components. We applied some sorting and filtering conditions on the banded object, added and removed some columns in the table, drilled around the crosstab, and converted the chart into a crosstab and changed the chart type.