Install Jaspersoft I-Reporter Designer

If Jasper not available on machine, it can be downloaded from here: http://www.jaspersoft.com

Connecting to Oracle Database

Jaspersoft Designer requires an Oracle Driver in order to connect to Oracle database using JDBC connection. The Oracle Driver can be downloaded here: http://www.oracle.com

The JDBC driver will be located in the sections under the Drivers section. For Oracle 11g, ojdbc6.jar will work.

After downloading ojdbc6.jar, move it in the following directory C:\Program Files\Jaspersoft\iReport-5.0.1\ireport\modules (this is assuming that installed the Designer in the default location).

Launch Jaspersoft Designer. Go to Tools -> Options. In the options section got to Classpath and click on Add Jar. Add the the odbc driver that was placed in C:\Program Files\Jaspersoft\iReport-5.0.1\ireport\modules. Click OK 

Open the Jaspersoft Designer and create a new database connection by clicking on the database icon

NOTE: The following requires connection string. See support or appropriate department for access to your database string

  1. JDBC connection for the Data source 

  2. Click Next
  3. For JDBC driver choose Oracle(oracle.jdbc,driver.OracleDriver)
  4. NOTE: JDBC URL for Oracle has the following format -> jdbc:oracle:thin:@[HOST][:PORT]:SID

  5. Enter Username and password the for the specific environment.  

  6. Click Test to see if connection is valid, then click save

NOTE: Can add a database when creating a new Report in the Launch Report Wizard -> Connection / Data Source

Creating a Report using Database

NOTE: This section assumes the user has gone through the page "Install Jaspersoft I-Report Designer and Connect to Oracle DB". An assumption is made that the user also knows how to create business objects, rules, and use rule debugger.

Set up

  1. Create a solution in AppBase and create a business object called Worker. Add the following attributes : 

    NameType
    FNameText
    LNameText
    AgeNumber
    PositionText
  2. Create a rule using Rule Generator that creates records for Worker , create_root_Worker
    1. Click Rule Generator and choose Create Object
    2. Select object Worker
    3. Select Age, Id, fname, lname, position 
  3. Deploy Solution 

  4. Create records using rule debugger. For example 

    FNameLNameAgePosition
    JimBob23QA
    SarahMarshall32PM
    KathyMacky55Engineer
    JayBob56Engineer

Create a New Report

  1. Open Jaspersoft Designer and connect to the environment where the business object was deployed
  2. To create a new report open Jaspersoft I-Report Designer, go to “File” -> “New”.“New File” window will pop up. Select “Report” icon from left side and you will see a list of report Templates.
    Select one of the templates that you would like to create. Here you have two choices:
    1. First to open Report Wizard. Report Wizard will ask you to define a SQL query.
    2. Second just to open a template. An empty report will be created and if you will need to create a SQL query later, you have to add it.
    3. Give it a name and click Finish.
  3. Now you’ll see an empty report with sections Title, Page Header, Column Header, etc. On this report you can add Text Field, Label, Grid, whatever you want and wherever you want on the report (position).
  4. On the left side of the Designer you can see a menu for the report containing Styles, Parameters, Fields, etc.

Create a Report With a Template

  1. To see this in action let us use one of the templates available. Go to “File” -> “New” -> “Coffee Landscape” and select this template for demonstration.

    1. Here we can click “Open this Template” or “Launch Report Wizard” buttons. In this report template, we have grid, so we need to write a SQL query later, or we can click “Launch Report Wizard” button and configure a SQL query right away. 

    2. Click “Launch Report Wizard” button, give report a name and location where it should be saved and click “Next”.
    3. On “Query” screen we should see you database connections if you added. If not, we can add a new connection from here and place our query in Query box. 

  2. Add the following Query for demonstration 

    Selecting all attributes from Tbl_Worker
    SELECT * FROM Tbl_Worker

    NOTE: If get Query Error Check SQL statement ( maybe remove ';' )

  3. Include the following columns: fname, lname, age, position. 

  4. The next section is the group by section which can be ignored at the moment.
  5. Click Finish to generate the Report

  6. Jasper will generate three views 

    NameDescription
    Designer

    This view allows user to modify arrangements and data in the report

    XML

    This view gives experianced user same option of designing, but in XML form

    Preview

    This gives view after design

More Technical Jasper Example

  1. To see this in action let us use one of the templates available. Go to “File” -> “New” -> “Coffee Landscape” and select this template for demonstration.
    1. Here we can click “Open this Template” or “Launch Report Wizard” buttons. In this report template, we have grid, so we need to write a SQL query later, or we can click “Launch Report Wizard” button and configure a SQL query right away.
    2. Click “Launch Report Wizard” button, give report a name and location where it should be saved and click “Next”.
    3. On “Query” screen we should see you database connections if you added. If not, we can add a new connection from here and place our query in Query box.

  2. Let’s make a simple SQL query for demonstration. Let’s say we have a query like this 

    Simple Query
    SELECT 'John' as FirstName, 'Smith' as LastName, 32 as Age FROM DUAL
  3. Place this query in “Query” section and click “Next”.
  4. Select fields that you want to populate on report. In this case let’s select all of them. Next is “Group by” clause which we don’t need in this simple SQL, so click “Next” and then “Finish”. Now we can see .jrxml report that has been created.
  5. Also please note that “FirstName”, “LastName” and “Age” that we defined above in SQL query, we can see those fields automatically added on report screen grid.
  6. So let’s get back to “Designer” screen. Here we can change element positions by moving them, delete elements, add another one, etc.
  7. On the left side we have a report menu.
  8. Expend “Fields” section, and we will see our three fields are there.
  9. From menu, select Fields, right click and we can add another field from there or delete.
  10. Parameter section has some default parameters that we cannot delete, but you can add parameters the same way like we add fields, variables, etc.
  11. We did not add anything yet or made some changes, so let’s click “Preview”button.
    1. Adobe Reader will pop up (if you have installed on your computer) and we will see PDF report.
    2. You can see from there that grid has three columns with header First Name, Last Name and Age and one record with values John, Smith, 32 as we mentioned in our SQL query.
  12. Now let’s do some modifications. For that purpose, go back to designer screen, select coffee picture on designer, right click and delete it.We also have coffee stain picture. Let’s delete that one also.
  13. Now let’s change the Title of the report. For that we can double click on Title from designer and change it from there or click on Title and on right side we can see “Properties” window with all available properties for this label including text, color, position, style, etc. Let’s change title label text to “Invoice” or whatever you want.
  14.  Also let’s delete coffee sub title and Lorem ipsem text label. Now we have a simple report with Title and grid only. Click “Preview” and you will see how report looks like
  15. To be able to modify already created query, click on report name from the left Menu and on right side of designer we can see Report properties. There is section called “Query Text” and the value we can see our SQL query.

  16. Open “Query Text”window (there is a small button next to the SQL query value). Add middle name to SQL so your query will look like something like this: 

    Simple Query
    SELECT 'John' as FirstName, 'Smith' as LastName, 32 as Age, 'M' as MiddleName FROM DUAL


  17. Click OK. Now from Designer change the Age column width (header and field), make it smaller gently and add Middle Name column to the grid. For that you need to drug and drop label and put next to the Age header and change Text to “MiddleName”. Also same way add text field to the grid next to the Age field and change the Field Expression to “$F{MiddleName}”. Line up header and fields so grid will look nice and press “Preview” button 
  18. To add a field on report we can simple drag and drop field from left menu on report wherever we want.
  19. Same way we can change the grid header by double clicking or single click and go to the properties section on the right. To be able to add another image, text field, label, table or any other element on report, go to “Window” à”Palette”.  Again by drug and drop technique you can add elements on report and modify their properties as you wish.
  20. To add a parameter to report, select “Parameter” section from left menu, right click and “Add Parameter”. You will see parameter’s property window open on the right side. Give it name and type Name = InvoiceNumber, Parameter Class = java.lang.String
  21. Now drag and drop that parameter on the report anywhere you prefer.
  22. After adding parameter to report click “Preview” button and you can see that “Parameter prompt” window pops up so you can give it a value.
  23. Give a value and click OK and on Preview screen or In PDF you will see the value assigned to that parameter on the report.
  24. Same thing you can do by adding for example text field on report by drug drop from “Palette”, then click on that text field on the page, go to properties and assign “Text Field Expression” value to $P{InvoiceNumber}.
  25. Click Preview and you will get the same result.
  26. Add four more parameters of type string:     
    1. Address
    2. City
    3. State
    4. Zip

  27. Now add text field on report from “Palette”. In properties window for this text field let’s change “Text Field Expression” by clicking the small button next to the expression value. 

  28. “Text Field Expression” window will pop up.
  29. From here we can select fields, parameters, variables that we want to populate on report for this particular text field.
  30. For now we can see that value for this text field is “$F{field}”. Let’s remove that. From this window we also see three columns.
  31. Please select “Parameters” from the left column. Middle column will show all available parameters for report. Here we want to populate Full address for this text field.
  32. Double click “Address” parameter and on top section of the window we can see that value “$P{Address}” has been added. Here we have some available functions.

  33. Please find “concat()” function and double click on it.  Make you the final expression looks like this $P{Address}.concat(',').concat($P{City}).concat(',').concat($P{State}).concat(',').concat($P{Zip}) 

  34. Click OK button and Preview report, give parameter values and we will see that our text field has the full address containing City, State and Zip values (Maybe for this example you need to change the text field width property, if your given address is too long, so you will be able to see full value of text field).
  • No labels