“
Bereits wenige Stunden nach der Bereitstellung der Dell KACE Appliance erhielten wir wertvolle Daten, insbesondere in Bezug auf Inventar und Assets.
”
Jim Austin, Webster Central School District
Startseite / Support / Ressourcen / Knowledge Base / Articles / Customizing-a-Report-Layout-With-Jasper-iReport

Customizing a Report Layout With Jasper iReport

Problem

When creating a custom SQL report or saving a wizard-generated report in SQL mode you are left with a report whose layout is practically frozen. It is not actually frozen, but because the report cannot be opened in the Wizard any longer you are unable to make layout changes without manually editing the layout XML, which is a daunting task.

Solution Overview

However, you can export this XML into a tool called iReport. This tool provides a way to design the XML in a GUI interface. When you are finished you can re-import the XML into the K1000 report, replacing the old with the new.

Our Example

For example purposes we are going to modify the layout of the built-in helpdesk report called "Open Ticket by Owner".

We are going to:

  • remove the number column (ie. the left-most column)
  • Make the title column wider
  • Add a final page footer that will tell us the total number of records on the field

Step 1: Download, Install and Configure iReport

iReport can be obtained from http://sourceforge.net/projects/ireport/files/ The version tested for this document was 3.6.0 released in Sept 2009. Download the appropriate installer for your platform. The one tested here was for MacOSX.

When you have it installed you must open the preferences and set the engine comptability for JasperReports 2.0.3. This is required for compatibility with the K1000 appliance XML format. 

Step 2: Configure a Datasource

Next you want to configure a datasource. Click on the database icon and choose to create a JDBC Database connection. You will set it up like this:
Name K1000
JDBC Driver MySQL (com.mysql.jdbc.Driver)
JDBC URL jdbc:mysql://K1000/ORG1
Username R1
Password box747
Note: you may have changed your reporting password 

Hit the "Test" button to know it is working. If it fails it may be because you do not have database access enabled under settings\Security on your K1000 appliance. If you need to configure a firewall this connection goes over port 3306 by default

Step 3: Export your Report XML to iReport

Nothing fancy here. Simply open your report in the SQL editor and copy the contents of the XML Report Layout field to clipboard. If you are modifying one of the built-in reports then you will need to duplicate it first.  

Next we open a new report in iReport (Use a very basic template) and in the XML tab, paste in the XML from our clipboard 

Do a quick visual scan to make sure it's all there. When you click on the Designer tab now it should look very much like the report the k1000 appliance generates 

Step 4: Design the report in iReport

Now for our tweaks. To review, we are going to:
  • remove the number column (ie. the left-most column)
  • Make the title column wider
  • Add a final page footer that will tell us the total number of records on the field

So, we start by:

  • right-click on the # label and deleting it
  • right-click on the datafield below that and delete it
  • drag the ID column to the far left
  • drag the datafield for ID to the far left (so that it is below it again)
  • Select and drag the title column to be wider (filling the voide that ID left behind)
  • Do the same for the title datafield
You know have this: 

To continue:

  • In the report inspector pane, look for the section called Last Page Footer and choose Add Band
  • In the report inspector pane, expand the variables section and find the variable called REPORT_COUNT. Drag this into the newly created page footer band 
  • Go ahead and bold this field
  • I also added a second label to describe that total that was not in bold. To do this:
    • Dragged a second variable for REPORT COUNT
    • Found this field in the Report Inspector
    • Right-clicked on it and chose transform to > Text Field 
  • Drag them around to get this arranagement 

Step 5 (Optional): Previewing in iReport

If you want to preview the data then we have to do one thing. The image in the top-left is broken because it is referencing a path on the K1000 appliance. We cannot compile (for preview) the report with this. To fix it you need to select this field and modify the properties of it. Change the Image Expression field to be blank for now (save the path)
 

Step 6: Import the XML back to the K1000 appliance

Now take the XML back to the K1000 appliance the same way you brought it in.
  • Go to the XML tab
  • copy the contents
  • Go to K1000 report
  • Paste in the XML
  • Save the Report

You can now run the report.

If you have a failure then it is likely the following error. Open the URL http://K1000/logs/tomcat_output If the latest error is a syntax error like this: 

23:10:50,048 ERROR ReportServlet,http-127.0.0.1-8080-Processor25:229 - Reporting Exception: com.jaspersoft.kace.adhoc.server.AdHocException: Exception running report.. java.lang.Exception: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select ...
That means the K1000 appliance double up your query. So go back in to the report editor and look for the query in the XML. You'll see something like this: 
<![CDATA[select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       CONCAT(TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
   'd ', DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",
      SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
      '%kh %im')) AS TIME_OPEN,
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL
]]><![CDATA[select HD_TICKET.ID, 
       HD_TICKET.TITLE ,
       HD_TICKET.DUE_DATE ,
       CONCAT(TO_DAYS(NOW()) - TO_DAYS(HD_TICKET.TIME_OPENED),
   'd ', DATE_FORMAT(ADDTIME("2000-00-00 00:00:00",
      SEC_TO_TIME(TIME_TO_SEC(NOW())-TIME_TO_SEC(HD_TICKET.TIME_OPENED))),
      '%kh %im')) AS TIME_OPEN,
       HD_PRIORITY.NAME as PRIORITY, 
       HD_CATEGORY.NAME as CATEGORY, 
       HD_STATUS.NAME as STATUS, 
       HD_IMPACT.NAME as IMPACT,
       MACHINE.NAME as MACHINE_NAME,
       ifnull((select FULL_NAME from USER where HD_TICKET.OWNER_ID = USER.ID),' Unassigned') as OWNER_NAME,
       (select FULL_NAME from USER where HD_TICKET.SUBMITTER_ID = USER.ID) as SUBMITTER_NAME
from HD_TICKET
left join HD_CATEGORY on HD_CATEGORY_ID = HD_CATEGORY.ID
left join HD_STATUS on HD_STATUS_ID = HD_STATUS.ID
left join HD_PRIORITY on HD_PRIORITY_ID = HD_PRIORITY.ID
left join HD_IMPACT on HD_IMPACT_ID = HD_IMPACT.ID
left join MACHINE on HD_TICKET.MACHINE_ID = MACHINE.ID
where HD_STATUS.STATE = 'opened'
order by OWNER_NAME, HD_PRIORITY.ORDINAL, HD_CATEGORY.ORDINAL, HD_STATUS.ORDINAL, HD_IMPACT.ORDINAL]]>

Notice how it is written twice!

To fix this delete the second one and re-save.

Enjoying your results

You should now have a report with a modified layout  
Categories for this entry