iReport Tutorial

15 

Loading.... (view fulltext now)

Loading....

Loading....

Loading....

Loading....

Full text

(1)

ZuckerReports

ZuckerReports

iReport Tutorial

iReport Tutorial

(2)

Table of Contents Table of Contents

Introduction

Introduction ... 33 iReport

iReport ConfigurationConfiguration ... 44 Start a new Repo

Start a new Reportrt ... 66 Enter the

Enter the Report QueryReport Query ... 77 Design the

Design the ReportReport... 99 Deploy this report to ZuckerReports

Deploy this report to ZuckerReports ... 1212 Conclusion

Conclusion... 1515 Contact

(3)

Introduction

Introduction

This Step-By-Step tutorial shows how to use the free software “iReport” to design, deploy and This Step-By-Step tutorial shows how to use the free software “iReport” to design, deploy and run a simple report with ZuckerReports and export the result as PDF. iReport is a powerful, run a simple report with ZuckerReports and export the result as PDF. iReport is a powerful, intuitive and easy to use visual report designer, suitable for use with ZuckerReports

intuitive and easy to use visual report designer, suitable for use with ZuckerReports

((http://ireport.sourceforge.net/ http://ireport.sourceforge.net/ ). ZuckerReports is an extension module for Sugar Suite, the). ZuckerReports is an extension module for Sugar Suite, the well-known Open Source Customer Relationship Management software. In short, it provides well-known Open Source Customer Relationship Management software. In short, it provides the following functions:

the following functions:

 Management of report definitions and query templatesManagement of report definitions and query templates

 Management of parameter selection listsManagement of parameter selection lists

 Parametrization and execution of report definitions and query templatesParametrization and execution of report definitions and query templates

 Categorization of executed reports and query results for later useCategorization of executed reports and query results for later use

(4)

iReport Configuration

iReport Configuration

You have to configure iReport to get access to your Sugar database. You have to configure iReport to get access to your Sugar database.

Open menu item “Datasource -> Connections / Datasources” and click on the “New” button Open menu item “Datasource -> Connections / Datasources” and click on the “New” button to open the “Connections Properties” dialog.

to open the “Connections Properties” dialog.

Enter/Select the following values: Enter/Select the following values:

Type of connection/datasource

Type of connection/datasource-> select “Database JDBC connection”-> select “Database JDBC connection”

 Name

 Name-> enter “sugar”-> enter “sugar”

 JDBC Driver 

 JDBC Driver -> select “com.mysql.jdbc.Driver”-> select “com.mysql.jdbc.Driver”

 JDBC URL

 JDBC URL -> enter “jdbc:mysql://<sugar-mysql-host>/<sugar-mysql-database>”-> enter “jdbc:mysql://<sugar-mysql-host>/<sugar-mysql-database>”

Note: alternativly you can enter the host in the field “Server Address” and the Note: alternativly you can enter the host in the field “Server Address” and the mysql-database in the field “Database” in the “JDBC URL Wizard” box and click on the “Wizard” database in the field “Database” in the “JDBC URL Wizard” box and click on the “Wizard” button afterwards.

button afterwards.

Username

Username -> enter the mysql username used for Sugar-> enter the mysql username used for Sugar

Password 

Password -> enter the mysql password used for Sugar-> enter the mysql password used for Sugar

Save Password 

(5)

Click the “Test” button to do a connection test and afterwards click the “Save” button. Click the “Test” button to do a connection test and afterwards click the “Save” button.

The “Connections / Datasources” dialog now shows the sugar mysql connection. The “Connections / Datasources” dialog now shows the sugar mysql connection.

Close this dialog, open menu item “Build -> Set active connection” and ensure that “sugar” is Close this dialog, open menu item “Build -> Set active connection” and ensure that “sugar” is the currently selected connection in the “Set active connection” dialog.

(6)

Sta

Start a new

rt a new Report

Report

Select menu item “File -> New Document” to open the “Report properties” dialog. In the Select menu item “File -> New Document” to open the “Report properties” dialog. In the “Report name” field enter “AccountSummary” and click the “OK” button to open a blank  “Report name” field enter “AccountSummary” and click the “OK” button to open a blank 

report design. report design.

Note: Please be sure to not use blanks Note: Please be sure to not use blanks in the “Report name” field.

(7)

Enter the Report Query

Enter the Report Query

Now it is time to tell the report which data to show. In this tutorial we will build a report Now it is time to tell the report which data to show. In this tutorial we will build a report

which allows the user to select an account, the report will show some account details and a list which allows the user to select an account, the report will show some account details and a list of contacts for this account.

of contacts for this account.

First we have to define a so-called

First we have to define a so-called“Report Parameter”“Report Parameter”named “ACCOUNT_ID”, which isnamed “ACCOUNT_ID”, which is given to the report for filtering data for the user-selected account.

given to the report for filtering data for the user-selected account.

In the “Project Browser” tree view on the left side of the In the “Project Browser” tree view on the left side of the screen, select the “Objects library” tab at the bottom. screen, select the “Objects library” tab at the bottom.

Right-click on “Parameters” and select popup menu “Add -> Right-click on “Parameters” and select popup menu “Add -> Parameter”.

Parameter”.

In the “Add/modify parameter” dialog enter/select the In the “Add/modify parameter” dialog enter/select the following values and click the “OK” button afterwards. following values and click the “OK” button afterwards.

Parameter Name

Parameter Name-> enter “ACCOUNT_ID”-> enter “ACCOUNT_ID”

Parameter class type

Parameter class type-> select “java.lang.String”-> select “java.lang.String”

 Is for prompting

 Is for prompting-> make this box checked-> make this box checked

 Default value expression

 Default value expression-> enter the Sugar ID of an account-> enter the Sugar ID of an account in your sugar database (see note below), don’t forget the in your sugar database (see note below), don’t forget the double quotes.

double quotes.

Note on

Note on ACCOUNT_ID ACCOUNT_ID: a valid Sugar ID of an account is needed for this purpose to allow: a valid Sugar ID of an account is needed for this purpose to allow iReport to make a preview of what the report will look like, which is a great help when iReport to make a preview of what the report will look like, which is a great help when

designing a report. For getting the Sugar ID of an account, open the detail view of the account designing a report. For getting the Sugar ID of an account, open the detail view of the account in Sugar and copy the string following

(8)

You can now see the “ACCOUNT_ID” in the list of Parameters in the “Project Browser”. You can now see the “ACCOUNT_ID” in the list of Parameters in the “Project Browser”.

Now let’s enter the SQL for t

Now let’s enter the SQL for the query.he query.

Open menu item “Datasource -> Report query” to open the “Report query” dialog, where you Open menu item “Datasource -> Report query” to open the “Report query” dialog, where you can enter an SQL-query to use. Select the “Report SQL query” tab, make sure that the

can enter an SQL-query to use. Select the “Report SQL query” tab, make sure that the

“Automatically Retrieve Fields” checkbox is checked and enter the following query into the “Automatically Retrieve Fields” checkbox is checked and enter the following query into the textbox:

textbox:

select a.name, a.industry,

select a.name, a.industry,

c.last_name, c.first_name, c.email1

c.last_name, c.first_name, c.email1

from

from

accounts a, accounts_contacts ac,

accounts a, accounts_contacts ac,

contacts c where

contacts c where

a.id = $P{ACCOUNT_ID} and

a.id = $P{ACCOUNT_ID} and

a.id = ac.account_id and

a.id = ac.account_id and

ac.contact

ac.contact_id = c.id _id = c.id andand

a.deleted = 0 and c.deleted = 0 and

a.deleted = 0 and c.deleted = 0 and

ac.deleted = 0

ac.deleted = 0

Please note that we refer to the previously Please note that we refer to the previously defined parameter by restricting search to defined parameter by restricting search to $P{ACCOUNT_ID} within the SQL query. $P{ACCOUNT_ID} within the SQL query. All of the selected columns are shown in the All of the selected columns are shown in the field listing. Click the “OK” button to close field listing. Click the “OK” button to close the dialog.

the dialog.

You can now see all the query result fields in the “Fields” section of the “Project Browser”. You can now see all the query result fields in the “Fields” section of the “Project Browser”.

(9)

Design the Report

Design the Report

Now it is time to tell the report where to show which information! Now it is time to tell the report where to show which information!

Drag&Drop the “name”-field onto the section on the report called “title”. Drag&Drop the “name”-field onto the section on the report called “title”.

You can make the “name” field larger by dragging it with the mouse. Double-Clicking it You can make the “name” field larger by dragging it with the mouse. Double-Clicking it opens a dialog box where you can change font style, size, alignment and others. Give the field opens a dialog box where you can change font style, size, alignment and others. Give the field a larger font by changing the “Size” field in the “Font” tab.

a larger font by changing the “Size” field in the “Font” tab.

Now let’s add the detail information for the account. Drag & Drop the Now let’s add the detail information for the account. Drag & Drop the

“industry” and “website” field onto the section called “columnHeader”. We “industry” and “website” field onto the section called “columnHeader”. We have to give some textual information on these fields, so select the “T” have to give some textual information on these fields, so select the “T” button in the toolbar to activate the “Static text tool”.

(10)

With the mouse, paint a query in front of the “industry”-field. With the mouse, paint a query in front of the “industry”-field.

Double-clicking it open a dialog box, where you can select the “Static Text” tab and enter Double-clicking it open a dialog box, where you can select the “Static Text” tab and enter “Industry:” for setting the text for this field. Do the same to add a label for the “Industry:” for setting the text for this field. Do the same to add a label for the “website”-field.

field.

Now it’s the time to execute the report for the first time to check the layout. Save the report Now it’s the time to execute the report for the first time to check the layout. Save the report design (“File -> Save as …”).

design (“File -> Save as …”).

Click on “Build -> Compile” to compile the report design. Click on “Build -> Compile” to compile the report design.

Afterwards click on “Build -> Execute report (using active conn.) Afterwards click on “Build -> Execute report (using active conn.)

A dialog box opens asking to give a value for the “ACCOUNT_ID”-parameter. Click the A dialog box opens asking to give a value for the “ACCOUNT_ID”-parameter. Click the “Use default” button.

“Use default” button.

You now see the report you just created with data filled from your database. Success! You now see the report you just created with data filled from your database. Success!

(11)

Now let’s add the contacts for the account to the report. Drag&Drop the “last_name”, Now let’s add the contacts for the account to the report. Drag&Drop the “last_name”,

“first_name” and “email1” fields from the “Project Browser” to the section named “detail”. “first_name” and “email1” fields from the “Project Browser” to the section named “detail”. Save the report, compile it and execute it as before.

Save the report, compile it and execute it as before.

You now see the list of contacts on the generated report. Checking the menu item “Build -> You now see the list of contacts on the generated report. Checking the menu item “Build -> PDF preview will generate a PDF-file you can open in Acrobat Reader, for example.

PDF preview will generate a PDF-file you can open in Acrobat Reader, for example.

This report is rather ugly, but now that you know the basics, is rather easy to change the This report is rather ugly, but now that you know the basics, is rather easy to change the report design. Just play around with all the dialogs in iReport to reach amazing results! Here report design. Just play around with all the dialogs in iReport to reach amazing results! Here is a list of what you can do with it:

is a list of what you can do with it:

 Include your company logo and other picturesInclude your company logo and other pictures

 Group data and do group calculationsGroup data and do group calculations

 Use subreports to build more complex reportsUse subreports to build more complex reports

 Include charts generated on the fly with data from the specified SQL queryInclude charts generated on the fly with data from the specified SQL query

 Use iReport for doing mass mailingsUse iReport for doing mass mailings

 Include hyperlinks for linking to your Sugar items from the reports, enabling “Drill-Include hyperlinks for linking to your Sugar items from the reports, enabling “Drill-Down”

(12)

Deploy this report to ZuckerReports

Deploy this report to ZuckerReports

The next step is to deploy the report to ZuckerReports to make it available to other Sugar The next step is to deploy the report to ZuckerReports to make it available to other Sugar users. ZuckerReports allows easy-to-use parameter selection, so you can choose the account users. ZuckerReports allows easy-to-use parameter selection, so you can choose the account to execute this report for from a select box.

to execute this report for from a select box.

Login to Sugar as “admin” and open the “ZuckerReports” module Login to Sugar as “admin” and open the “ZuckerReports” module

Note that the ZuckerReports distribution comes with a Parameter suitable for selecting the Note that the ZuckerReports distribution comes with a Parameter suitable for selecting the account for this report, so we don’t have to configure it ourselves.

account for this report, so we don’t have to configure it ourselves.

Select menu item “Report and Query Templates” and select the “New Report Template” Select menu item “Report and Query Templates” and select the “New Report Template” button.

(13)

Enter “Account Summary” in the “Template Name” field, and select the Enter “Account Summary” in the “Template Name” field, and select the

“AccountSummary.jrxml” file you saved the report from iReport using the “Browse…” “AccountSummary.jrxml” file you saved the report from iReport using the “Browse…” button as “Template File”.

button as “Template File”.

For the “Allowed Formats”, check the

For the “Allowed Formats”, check the“Adobe PDF (*.pdf)”“Adobe PDF (*.pdf)”box to enable PDF export.box to enable PDF export. Click the “Save” button to apply changes.

Click the “Save” button to apply changes.

Now you are in the detail view for this report template. In the “Parameter Selection” section Now you are in the detail view for this report template. In the “Parameter Selection” section on the bottom select the “Account”-Parameter and click the “Attach” button to configure this on the bottom select the “Account”-Parameter and click the “Attach” button to configure this report to use the “Account”-Parameter for execution.

(14)

You can see the “Account”-Parameter in the “Parameters”-list afterwards. You can see the “Account”-Parameter in the “Parameters”-list afterwards.

Now click the “Run Report” to open the “On-Demand Reporting” panel. Select the Account Now click the “Run Report” to open the “On-Demand Reporting” panel. Select the Account you want to run the report for and click the “Run Report” button at the bottom.

you want to run the report for and click the “Run Report” button at the bottom.

You now have the report generated within Sugar as PDF. Clicking on the “Filename” link will You now have the report generated within Sugar as PDF. Clicking on the “Filename” link will open it in your browser.

(15)

Conclusion

Conclusion

It is very easy to design and deploy reports with It is very easy to design and deploy reports with iReport as design tool and ZuckerReports as iReport as design tool and ZuckerReports as runtime tool integrated into Sugar. This “First runtime tool integrated into Sugar. This “First Steps” tutorial only covered a very basic report, Steps” tutorial only covered a very basic report, whereas it is possible to design colorful,

whereas it is possible to design colorful,

amazingly looking reports with huge complexity amazingly looking reports with huge complexity with these tools.

with these tools.

Contact

Contact

For licensing questions please contact us at

For licensing questions please contact us at sales@go-mobile.atsales@go-mobile.at For support questions please contact us at

For support questions please contact us at support@go-mobile.atsupport@go-mobile.at For general questions please contact us at

Figure

Updating...

References

Updating...

Related subjects :