MyEclipse Reports Tutorial - The Basics

Introduction
The Report Development Workflow
Creating a Report Web Project
Creating a Report
Creating a JDBC Data Source
Creating a Data Set
Customizing a Report

Layout
Sorting
Formatting
Adding title

Deploying your Report
Conclusion
Resources
Feedback

1. Introduction

We use reports in all aspects of our personal and business lives. When data is presented in a well organized and informative manner, such as with business reports, it can significantly improve our critcal decision making processes. The report development workflow and MyEclipse Reports tools can be used to develop a wide range of web report types. In this tutorial we will walk through the basic workflow for creating, deploying and testing a simple customer listing web report using the MyEclipse Reports features.

Figure 1-1 is a screenshot of the completed customer listing report. You can download the customer listing report project developed in this tutorial using the Examples On-Demand browser, MyEclipse > Examples On-Demand.

Figure 1-1, Customer listing report from this tutorial viewed in a web browser

Suggested Audience

This tutorial assumes the reader is familiar with MyEclipse web project development and deployment concepts and tools and the MyEclipse Database Explorer features. If during the course of this tutorial you have questions or would like further information about the topics presented in this tutorial please consult the Resources section for additional resources. To get a better feel for the full scope of MyEclipse, please check out our product documentation.

System Requirements

This tutorial was created using MyEclipse 6.0.1 Professional Edition. If you are using a more recent version of MyEclipse, most of the images and instructions should be backward compatible.

MyEclipse Reports requires MyEclipse 6.0.1 or above. It is a Professional level feature and is only available to MyEclipse Pro subscribers.

2. The Report Development Workflow

Following is an outline of the task workflow for creating a new report:

Create a Report Web Project
Create a report
Create a MyEclipse JDBC data source
Create a report dataset
Layout, customize and style the report
Deploy Report Web Project
View and test Web Report

Each of these tasks will be discussed in the order that they are presented in the outline.

3. Creating a Report Web Project

MyEclipse Reports introduces a new type of project known as a Report Web Project. This type of project is a MyEclipse Java EE web project enabled with Report development capabilities. There are two methods for creating a Report Web Project. The first method is using the Report Web Project Wizard. Consider using this wizard when you do not have legacy web project constraints. The second method is to add Report Capabilities to an existing MyEclipse web project. This method also uses a wizard but requires the web project already exist before report capabilities can be added to it. Both appoaches are introduced in this section.

3.1 Using the Report Web Project Wizard

Launch the Report Web Project Wizard to create a new Report Web Project. Access the wizard at File > New > Project > Report Web Project.
Name the project: reportdemo

Figure 3-1, Enter the project name
Select Next> to view the report folder structure that will be created and to specify the logging level of the report engine.

Figure 3-2, Specify a report engine logging level
Select Finish to create the reportdemo project. Figure 3-3 below illustrates the structure of a MyEclipse Report Web Project.
When the wizard closes MyEclipse will ask you if you would like to open the MyEclipse Report Design perspective. Choose "Yes".

Figure 3-3, Report project structure

3.2 Adding Report Capabilities to a Web Project

You can Add Report Capabilities to any existing MyEclipse Web project using the Report Capabilities Wizard. This wizard is identical to the wizard page shown in Figure 3-2.

Begin by selecting the web project in a view such as the Project Explorer or Package Explorer. Then from the context menu, choose MyEclipse > Add Report Capabilities... This will launch the Report Capabilities Wizard as shown in Figure 3-2. Alternatively, you can launch the wizard from the menubar by selecting MyEclipse > Project Capabilities > Add Report Capabilities...

Figure 3-4, Add Report Capabilities
Complete the wizard and select Finish to update the project
When the wizard closes MyEclipse will ask you if you would like to open the MyEclipse Report Design perspective. Choose "Yes". If you can manually open the perspective using Window > Open Perspective > Other > MyEclipse Report Design.

4. Creating a Report

Select the reportdemo project and choose File > New > Report to launch the New Report Wizard. You can also choose the New Report button on the toolbar to launch the wizard.

Figure 4-1, New Report toolbar button
Name the report, new_report.rptdesign. Reports should be named with the file extension, "rptdesign".
Leave the "Generate sample report access webpage" option checked and click Next>.

Figure 4-2, Select a Report Project
Page-2 of the New Report Wizard presents several templates to choose from. Since we're creating a simple report, choose the Blank template and click Next>.

Figure 4-3, Choose a Report Template

Page-3 of the New Report Wizard allows you to create a sample JSP webpage that demonstrates how to work with the new report. For example the sample JSP page can be configured to either contain a link to your report or embed it. Note: If you choose not to generate a sample report access page at this time you can always generate a page at a later time using the Generate Sample Report Access WebPage action from the report's MyEclipse context menu.

Use the default folder location and JSP file name, e.g., new_report.jsp
Choose the Generate "An embedded report viewer" option

Figure 4-4, Report Access webpage options
Click Finish to create the report. The report will automatically be opened in the Report Designer, see Figure 4-5.

Figure 4-5, Report Designer

5. Creating a MyEclipse JDBC Data Source

Our customer listing report will retrieve its data from the local MyEclipse Derby Server. You can use any database for which you can configure a JDBC driver in the Database Explorer, e.g., Oracle, MS SQL Server, etc. The MyEclipse Derby server is provided as a training convenience as part of the MyEclipse 6 Server Sandbox and includes a preconfigured JDBC driver.

Launch the MyEclipse Derby server if it is not already running. In the Servers view, select the MyEclipse Derby Server and choose Run Server from the context menu or the Run action on the Servers view toolbar.

Working with the Embedded Derby database is covered in this tutorial. If you wish to follow along use a different database, please ensure it is running at this point.

Launch the Data Source Wizard. In the Data Explorer view, select Data Sources > New Data Source from the context menu. If the Data Explorer view is not visible you can launch it from the menubar, Window > Show View > Other > MyEclipse Reports Design > Data Explorer.

Figure 5-1, New Data Source action
Select "MyEclipse JDBC Data Source" from the list of data source types
Enter "Data Source" in the Data Source Name field
Click Next>.

Figure 5-2, select the Data Source type
Select the preexisting "MyEclipse Derby" driver and click Finish.
Note: you could also choose actions to create or edit an MyEclipse JDBC driver from this page.

Figure 5-3, Select the Database Driver

The reportdemo's data source is now configured to fetch its data using the MyEclipse Derby JDBC driver. For more information about working with MyEclipse JDBC Data Source see the Working with MyEclipse JDBC Data Sources Tutorial.

6. Creating a Data Set

A data set is a collection of data retrieved from a data source which will be displayed in a report. In this tutorial we will create a SQL data source.

Launch the New Data Set Wizard. From the Data Explorer view, select Data Sets > New Data Set from the context menu.

Figure 6-1, New Data Set action
Select the data source we created in Step 5.
Name the data set Customers.
Choose MyEcipse Data Set for the data set type and click Next>

Figure 6-2, Select the Data Source

On the second page of the wizard enter a SQL query that will retrieve the data for this data set. As you enter your SQL query the query editor supports content-assist of schema and table names. You may also double click a table or column in the tree view to have the qualified name of that element automatically inserted into the query at the last place of editing.

Enter the following SQL query:

select CUSTOMERNAME, CONTACTFIRSTNAME, CONTACTLASTNAME, PHONE 
from CLASSICCARS.CUSTOMER

Figure 6-3, Data Set Query Editor

Click Finish to execute the SQL query and create the data set.
If there were no problems creating the data set, the Edit Data Set dialog will launch allowing you to further customize the data set. Selecting the Preview Results mode (see Figure 6-4) enables you to view the data results of the SQL query.

Figure 6-4, Data Set Editor

7. Layout, Customizing and Styling a Report

7.1 Layout the Report

With the data source and data set created, the next step is to add the user interface elements to the report that will display the data from the Customers data set. We will start by adding a table element to the report. Then we will bind columns from the data set elements to the columns in the report table. It is important to understand the functionality that the table provides:

The table iterates through all the data rows of a data set
It enables you to layout data easily in a row and column format

Select the Palette view to view the elements that can be added to a report.

Figure 7-1, Palette View
Drag a table element from the palette and drop it on the report in the Layout Editor. A new Table dialog will appear and prompt you for the number of columns and detail rows to create for the new table and the data set to bind to the table.
In the new Table dialog, specify the following values, as shown in Figure 7-2.
- Number of columns: 3
- Number of details: 1
- Data Set: select Customers from the drop-down list
Choose OK

Figure 7-2, Insert Table Dialog properties

A table with three columns and one detail row appears in the Layout Editor. Next we will bind data set elements to columns of the table.
In the Data Explorer view, expand the Customers data set to view the columns that we specified in the query.
Drag the CUSTOMERNAME column from Data Explorer and drop it in the first cell of the table's detail row as shown in Figure 7-3.

The table detail row defines the columns of the data set and their order of display in each data row of the table. In the finished report, the detail row is repeated for each row of data in the data set.

Figure 7-3, Dragging a data set column and dropping it in a table cell

When the CUSTOMERNAME column from the data set data set was dropped on the detail row, the Layout Editor added the [CUSTOMERNAME] data binding element. Additionally, in the header row above the new [CUSTOMERNAME] data element, the Layout Editor automatically added a column heading label with text, e.g., CUSTOMERNAME. Figure 7-4 shows the data and label elements.

Figure 7-4, Data and label elements in a table
Drag the PHONE column from Customers data set and drop it in the second cell of the detail row.
Drag the CONTACTFIRSTNAME column from the Customers data set and drop it in the third cell of the detail row.
Drag the CONTACTLASTNAME column from the Customers data set and drop it in the fourth cell of the detail row, below CONTACTFIRSTNAME.

The report layout should appear as shown in Figure 7-5 below.

Figure 7-5, Customer and contact information added to a table
To confirm that the report data and table layout are correct choose the Preview tab at bottom of the Report Designer. See Figure 7-6 for how the report preview should appear. You can also preview the report other formats such as PDF by choosing File-> View Report-> View Report as PDF from the main menubar.

Figure 7-6, Preview of report data

7.2 Sorting Data

In Figure 7-6 we can see in the report preview that data rows are displayed in the order that they are returned by the Customers data set query. The default data row ordering can be influenced by many factors, such as the order in which data was created in the database. In most cases, you will want to change the order in which data appears in the report. This section will introduce how to sort report data.

In the Report Designer choose the Layout tab to return to the Layout Editor
Open the Property Editor View, if necessary. If you use the default Report Design perspective, the Property Editor appears just below the Report Designer in the bottom-center section of the workbench. If the view is not visible, choose Window > Show View > Property Editor.
In the Layout Editor, select the table by selecting the Table tab in the lower left corner. This tab appears when you hover the mouse pointer over this area. The Property Editor will display the selected table properties as shown in Figure 7-7.

Figure 7-7, Table Property Editor
Choose the Sorting tab
Choose Add to specify a sort key. This will open the New Sort Key dialog. See Figure 7-8.
In the New Sort Key dialog, specify the following values:
- Key: select CUSTOMERNAME
- Direction: select Ascending
Figure 7-8, New Sort Key dialog
Choose OK. The Sort page will add the new CUSTOMERNAME sort key that you defined for the table
Preview the report again. This time the data rows appear in ascending sort order on the CUSTOMERNAME column data. See Figure 7-9.

Figure 7-9, Preview of data sorted on the CUSTOMERNAME column

Notice that names with uppercase letters appear at the top of the list. MyEclipse Reports sorts string data by UCS2 code point values. In ASCII-based character sets, uppercase letters have lower code point values than lowercase letters. Therefore, uppercase letters appear before lowercase letters.

Next let's sort the customer names case-insensitively so that ANG Resellers appears after American Souvenirs Inc., rather than before it.
In the Property Editor select the Sorting tab. Next select the CUSTOMERNAME sort key as shown in Figure 7-10, followed by the Edit button. This will open the Sort Key Editor.

Figure 7-10, New key on Sorting page
In the Sort Key Editor, change the Sort Key expression to the following expression and choose OK.
```
row["CUSTOMERNAME"].toUpperCase()
```
This expression uses the JavaScript toUpperCase() function to convert all the customer name values to uppercase before sorting. JavaScript function names are case-sensitive, so you must type toUpperCase() exactly as shown. References to column names are also case-sensitive. In this expression, row["CUSTOMERNAME"] is the correct name to use. If you type row["customername"], for example, the Report Designer displays an error when you run the report. You can verify the capitalization of a column name by looking at its spelling in the Data Explorer.

Preview the report and confirm that the customer names appear in case insensitive ascending order.

7.3 Formatting the Report

Now that the report displays the data in the correct order, we can turn our attention to improving the report's appearance. In this section we will perform the following tasks:

Edit column heading labels
Format column headings to differentiate them from the data rows
Display the contact first and last names on the same line
Increase the space between rows

7.3.1 Edit Column Heading Labels

In the Report Designer, choose the Layout tab to return to the Layout Editor
The Layout Editor allows you to edit labels directly. Place the cursor in the label text insert and delete characters directly. Hit Enter to save the new label text. Replace the CUSTOMERNAME column heading with the following text:
```
Customer
```
Repeat step 2 to change the second and third column headings to the following text:
```
Phone 
Contact
```
The report design should look like the one shown in Figure 7-14.

Figure 7-11, Revised column headings

7.3.2 Format Column Head Labels

To format a report element, you set its properties. You can accomplish this task in two ways:

Set an element's properties through the Property Editor.
Define a style that contains the desired properties and apply the style to an element. Use this method to define format properties once and apply them to more than one element.

In this procedure, we will use the first method, Property Editor, to set the column headings to bold. The second method will be used add color to the header row.

To set the column headings to bold using Property Editor:

Select all the column headings. To select multiple elements, press the Shift key as you click each element. The Property Editor displays the properties for the selected elements, as shown in Figure 7-11 above.
Choose the 'B' style option to format the selected column headings as bold text
To deselect the column headings, click the whitespace outside the table.

To add a background color to the header row, using a style:

From the main menu bar, choose Element->New Style.

Select the Custom Style option and specify the following name for the style:

table_header_row

Choose Background from the list of property categories. New Style displays the background properties that you can set.
Specify a color for the Background Color property, using one of the following methods:

Select the button next to the property, then select a color from the color palette that appears.
Select a color from the drop-down list.

In the Layout Editor, select the table by selecting the Table tab in the lower left corner. This tab appears when you hover the mouse pointer over the lower left corner of the table. Clicking the table causes guide cells to appear at the top and left side of the table, as shown in Figure 7-13.

Figure 7-13, Guide cells at top and left of a table
Select the guide cell next to the header row. The Property Editor will display the properties for the selected row.
Choose Properties and then General to display the general properties for the row.
Apply the style that you just created by selecting table_header_row from the drop-down list next to Style. The Report Designer applies the style to the header row and it appears in color.
Preview the report. The report should look like the one shown in Figure 7-14.

Figure 7-14, Report preview with improved header row styling

7.3.3 Display First and Last Names on Same Line

When you place multiple elements in a single cell, MyEclipse Report Designer creates block-level elements. If you are familiar with HTML, you know that each block element starts on a new line. To display multiple elements on the same line, you need to set them as inline elements. Alternatively, you can concatenate the first and last name values to display in a single data element, as described in this procedure.

In the Report Designer choose Layout to return to the Layout Editor.
Delete the data element that displays [CONTACTLASTNAME].
Double-click the data element that displays [CONTACTFIRSTNAME].

Click the Expression Builder button next to dataSetRow["CONTACTFIRSTNAME"].
To concatenate the first and last names, edit the expression as follows:
```
dataSetRow["CONTACTFIRSTNAME"]+" "+ dataSetRow["CONTACTLASTNAME"] 
```
Figure 7-16 depicts this expression in the Expression Builder. The empty quotation marks (" ") add a space between the first name and last name. You can type the expression in the text area, as shown in Figure 7-16 or double-click an item in the lower right of the window to insert it in the expression. Figure 7-16 shows a column name that you can double-click to insert into the expression.

Figure 7-16, Concatenated data in the Expression Builder created by choosing a data set field
Choose OK to close Expression Builder.

Preview the report. The report should look like the one shown in Figure 7-17.

Figure 7-17, Report preview

7.3.4 Increase Space Between Rows

The default layout adds minimum space between table rows. Typically, you will want to adjust the spacing between rows.

Choose Layout to return to the Layout Editor.
Select the table's detail row, i.e., the middle row as shown in Figure 7-18.

Figure 7-18, Selected table row in the Layout Editor

In the General properties, set Height to 24 points. This will increase the height of each row increases, as shown in Figure 7-19.
Preview the report. The report should look like the one shown in Figure 7-19. There is more space between the rows of data.

Figure 7-19, Report preview with expanded row spacing

7.4 Adding a Report Title

To wrap up our report needs a title. To display a title, you can use either a label element, a text element, or a data element:

The label element is suitable for short, static text, such as column headings
The data element is suitable for displaying dynamic values from a data set field or a computed field
The text element is suitable for multi-line text that contains different formatting or dynamic values

In this procedure, you use a text element and HTML tags to format the text. Note that you are not required to use HTML to create formatted text. If, however, you are well-versed in HTML or web design, you might prefer using HTML to create a block of formatted text.

In the Report Designer, choose the Layout tab to access the Layout Editor
From the Palette view drag a text element and drop it above the table. This will open the Text Item Editor

Figure 7-20, Drag and dropping a Text element to create Report heading
On Edit Text Item, select HTML from the drop-down list that displays Auto.
Selecting HTML enables you to embed HTML tags or CSS properties in the text. You can type the tags or you can insert the commonly used HTML tags that the text editor provides.
Enter the following HTML in the text area, shown in Figure 7-21:

<CENTER>
    <SPAN style="font-size: larger">
        <B>Customer List</B>
    </SPAN>
    <BR> 
    <FONT size="small">For internal use only</FONT>
    <BR><BR> 
    Report generated on <VALUE-OF>new Date( )</VALUE-OF> 
</CENTER>
<BR><BR>

Choose OK, then preview the report. The report should appear similar to the report shown in Figure 7-22.

Figure 7-22, Report preview, showing formatted report title

Use different formatting for each line in a multi-line text block.
Insert dynamic values such as the current date.

Two label elements to display the first and second lines of static text
A data element to display the third line that contains the dynamic value

8. Deploying and Testing The Report

With the report designed and styled, the next step is to deploy to a web server and test the reports operation using a web browser. For this tutorial we will deploy the reportdemo project to the embedded MyEclipse Tomcat server.

Select the reportdemo project and from its context-menu choose Run As > MyEclipse Server Application.

This will start the MyEclipse Tomcat server and deploy the reportdemo project to it.
The MyEclipse integrated web browser will be opened with an initial URL similar to http://localhost:8080/reportdemo/

Figure 8-1, Deploying and running reportdemo project to the MyEclipse Tomcat 6 Server

Add the name of the report access webpage to the URL, so it will look something like:
http://localhost:8080/reportdemo/new_report.jsp
Click Go or press Enter to have it load and display your report in the browser.

Figure 8-2, Customer List report accessed from a web browser

Note:

MyEclipse Deployment tutorial

9. Conclusion

You have just built your first report and learned about many of the basic tools and features of the MyEclipse Report Designer. There are many more tasks that you can accomplish to build more sophisticated reports. Some of these tasks, described in other chapters include:

Connecting to your own data source
Creating charts
Creating cross tabs
Creating report parameters for user input
Building reports that contain subreports
Formatting report elements based on conditions
Hiding reports based on conditions
Adding hyperlinks to link your report to web locations or to link one report section to another

Below, we would provide you with more information pertaining to the topics covered in this tutorial. We realize we can't cover every question you may have in one tutorial, but between this tutorial contents and our additional learning resources we hope you are well on your way to developing your own custom reports.

10. Resources

11. Feedback

We would like to hear from you! If you liked this tutorial, has some suggestions or even some corrections for us please let us know. We track all user feedback about our learning material in our Documentation Forum. Please be sure to let us know which piece of MyEclipse material you are commenting on so we can quickly pinpoint any issues that arise.