Download Report

2011
Maximo Query with
Open Source BIRT
Sometimes an organisation needs to provide certain users with simple query tools over
their key applications rather than complete access to use the application. In this case it
may be appropriate to create your query tool using BIRT and format the output to
resemble the original application. This is part one of a two part series.
Paul Bappoo
[email protected]
http://www.BirtReporting.com
1
February 2011
CONTENTS
How To Contact The Author ........................................................................................................................... 4
Introduction .................................................................................................................................................... 5
What You Will Need ....................................................................................................................................... 6
The Basic Report ............................................................................................................................................. 7
Adding the Parameter .................................................................................................................................... 9
Laying Out the Report .................................................................................................................................. 13
The Header Section .................................................................................................................................. 13
The Detail Section ..................................................................................................................................... 16
The line detail fields.................................................................................................................................. 17
Conclusion .................................................................................................................................................... 19
More Information ......................................................................................................................................... 20
2
Paul Bappoo –Query Maximo with Open Source BIRT (Part 1)
© COPYRIGHT 2011 by Paul Bappoo, all rights reserved.
This guide is furnished under license and may be used or copied only in accordance withthe terms of such license. The content of this
guide is furnished for informational use only, is subject to change without notice, andshould not be construed as a commitment by the
author. The author assumes no responsibility or liability for any errors or inaccuraciesthat may appear in this guide.
Except as permitted by such license, no part of this publication may be reproduced, stored in a retrieval system or transmitted, inany form
or by any means, electronic, mechanical, recording or otherwise, without the prior written permission of the author.
All product and company names are trademarks or registered trademarks oftheir respective holders.
3
HOW TO CONTACT THE AUTHOR
Email: [email protected]
Web Site: http://www.BirtReporting.com
Paul Bappoo is the author of BIRT for Beginners (which is available in paperback from BIRTReporting.com,
Amazon and Barnes and Noble amongst others) and the BIRT for Maximo Self Study Workbook, also
available from BIRTReporting.com. He has been an international technical software consultant and
involved with computers for over 30 years. Paul has an interest in BIRT reporting, enterprise application
integration, automated software testing, computer based training and enterprise system implementation.
Paul runs the BIRT User Group UK and is a member of the BIRT-Exchange Advisory Council. He would be
delighted to hear from you with your tips, tricks and stories about your usage of BIRT. If you have a
question, a need for training or consulting or great tip to share with the community then drop him a line.
4
INTRODUCTION
Do you have Maximo users who, for the most part, only need to run queries over your Maximo
data? Most large organisations do and if you have not yet identified them then now may be the
time. Maximo 7.1 and above, of course, comes with BIRT 2.3.2 as the budled report writer
software, however the open source community have developed BIRT beyond this version and at
the time of writing 2.6.1 is the current release.
BIRT can be used for more than simple reporting duties so in this tutorial we look at how to use
the latest version of open source BIRT as a query tool over Maximo, running from outside the
Maximo environment and how to format the resultant “reports” to resemble the Maximo screen
that the user would see if they were logged on to Maximo itself. Even if you don’t wish to report
over Maximo, this tutorial provides a valuable insight into the advanced formatting capabilities of
BIRT.
Here is a screenshot of the report that we are going to build:
The complete design files and all of the image files that were used in the creation of this report
are available free of charge to readers of my book “BIRT for Beginners” or readers of my “BIRT
for Maximo Self Study Workbook”. Just visit the readers area of my web site:
http://www.birtreporting.com/ReadersEntryPage.html
5
WHAT YOU WILL NEED
In order to create the “look and feel” of this report you will need some graphic files. You can
create these yourself with any image editing software or if you are a reader of my books then you
can download the files from my web site.
The files are as follows:
MXReportHeader.jpg
This is an image I created to sit at the top of all the reports and resembles the Maximo header
image. Of course you can create your own image to resemble your corporate reporting style or
just use mine.
MXArrow.jpg
This is the arrow that sits to the left of each detail line of the report. In this example we are
displaying purchase order lines. In the Maximo application this image would be active and when
clicked on it would reveal more details about the parchase order line. In this case it is a static
image, but could easily be made into a hyperlink which would run a secondary report to show the
line details.
MXFindBG.jpg
This image is the background to the search toolbar, that sits just beneath the main report header.
It is a thin sliver of an image and the report design uses standard HTML functionality to repeat it
to fill the entire width of the search bar.
MXLookupBtn.gif
This image is the hyperlink that actually runs the report, once the user has selected the PO
Number that they want to view.
You will also need a copy of the open source BIRT Report Designer and since this report uses a
Java Server Page (JSP), you will need something with which to create and edit one of those.
Luckily the BIRT designer is Eclipse based and so forms an excellent choice for JSP editing too.
For details on how to download and intall the BIRT Designer please see my report: The Complete
Getting Started Guide to BIRT which is available for free from the members area of
www.BIRTReporting.com.
6
THE BASIC REPORT
The BIRT report at the heart of this example is a very simple listing of purchase order lines,
combined with a little information from the report header.
When creating a report with BIRT 2.3.2 for Maximo you need to use the scripted data source
method, however, when creating a report which is designed to run outside of Maximo there is a
much easier way to create a data source and a data set. Again, follow the example provided in
my beginners guide: The Complete Getting Started Guide to BIRT which is available for free from
the members area of www.BIRTReporting.com.
To create the example report I used for the Maximo PO Lines I created 2 datasets, one to give
me a list of all the PO numbers so that the user could select one easily and one to source all the
PO and POLine data.
The SQL statement for the first was as follows:
select distinct PONum
from PO
And the second was like this:
select
po.description as POHeadDesc,
poline.*,
left(poline.description,60) as LineDesc,
left(po.description,50) as description,
po.status,
po.totalcost
from POLine
inner join po on po.ponum = poline.ponum
where poline.ponum = ?
Of course you can use your own SQL and select whatever data you like for your example.
TIP: It is always a good idea to write your SQL statements in a SQL query editor such as TOAD
or MS SQL Enterprise Manager to ensure they work, before moving them into BIRT.
7
The question mark in the second SQL statement indicates that a parameter will be used to
provide the value to the SQL at runtime. In my example this is going to be the PO Number
selected by the user.
Once you have created your two datasets you should be able to see them in the BIRT Designer
as follows:
POLines is the data set that returns the line details that will be used to populate the report and
POList is the data set that returns the list of PO’s that the user will select from at runtime.
8
ADDING THE PARAMETER
To add the report parameter that will be selected by the user at runtime, right click on the Report
Parameters node and select New Parameter.
9
Complete the parameter dialogue as shown:
Notice how the display type for the parameter is set to Combo Box – this is because we want the
user to be able to either select from a drop down list of PO numbers or type in their own PO
number.
The selection list values are set as Dynamic and the data set is specified as POList (remember
that’s the one that provides the list of PO numbers to select from).
I also like to provide a default value of a known database record so that when I test the data set in
the next stage, I know if it is working!
10
Next we need to join our report parameter to the data set, so reopen the dataset and select the
Parameters node.
You will see an entry here called param_1 – this is created automatically by BIRT because you
put that question mark into the original SQL statement. Select it and click the Edit button.
11
Now in the Linked To Report Parameter field, select the report parameter that you just created.
That’s it, now when the report runs, the user will be asked to select or type a PO Number and this
will be passed into the report SQL replacing the question mark and thereby filtering the report to
select all the lines for just the one selected PO.
Try this now by previewing the data set results and ensure that data is being returned. This is
where the default parameter value comes in handy, because without it, no data would be
returned!
12
LAYING OUT THE REPORT
Next you will want to set up 2 tables on your report layout andpopulate them with fields from your
data set.
The first table will hold details of the PO header information so will not require any detail or footer
rows, sinceit is supposed to only display a singlerecord. Create the table with 14 columns and 5
header rows. You will need to edit the table that BIRT creates for you by default to add in the
additional headers and remove the footer.
THE HEADER SECTION
Add the following fields to the third header row:
PONum
Description
SiteID
Status
TotalCost
Add an appropriate label in front of each field and leave a single column empty between each
label/field group, as in the screen shot below.
Format the text fields as follows for the field labels:
13
And as follows for the data items:
Next highlight all the cells in the second row, right click and select Merge Cells. This creates one
single cell that fills theentire second row. Place a label in this cell and set the general and border
properties as follows:
14
The label border colour as for most of the colours in this example, is defined using a custom
colour. To create the custom colour, click the colour box, click Define Custom Colours, select the
first Custom Colour box, then enter the RGB values into the appropriate fields and click Add to
Custom Colours. The colour will then be available to select from the palette for use in other areas
of your report.
You may need to play around with the left/right justification of the fields and format the number
fields appropriately. To format number fields to show two decimal places, select each number
field that you wish to format and set the properties as per the screen shot below.
15
THE DETAIL SECTION
Now we need to create the header for the detail lines section, which will appear in the second of
the two tables. I chose to place a label in the fourth row of the first table, but it could equally be
placed into the header row of the second table. Once the field is placed, set the background
colour as per the screen shot below and enter the text “PO Lines”
16
THE LINE DETAIL FIELDS
From the POLines dataset place the following fields into the second table, but this time place
them into the detail section, rather than the header.
POLineNum
ItemNum
LineDesc
OrderQuantity
OrderUnit
UnitCost
Discount
Tax1
LineCost
LoadedCost
Notice again that we separate the individual columns with a blank column as in the screenshot
below:
When you add the data fields you will see that the headers are added automatically for you, but
since they adopt the underlying field names, you may wish to edit them to a more appropriate
value.
Next we need to format the column headers and the field values. Here is the formatting for the
column headers:
17
And here is the formatting for the data items:
Finally the report date is added to the end of the report. BIRT does this by default and it is part of
the master page. Click on the master page tab, scroll to the end and modfiy the font and
background colours as we have done previously.
18
CONCLUSION
That’s about it for the BIRT report part of this. Preview the report in your browser and see how it
looks:
You will notice of course that the header image and the parameter selection are still not present
and the default BIRT viewer colour scheme is occupying the top of the report window.
In order to place a header image and paramter on the screen, above the report header objects
we need to invoke a little Java Server Page (JSP) and that will be the subject of the next tutorial
in this series.
I hope you enjoy creating this Maximo styled report and I hope it is useful for you. If you have any
difficulties please swing by the forum at BIRTReporting.com and post your query there. Our
members are a helpful bunch and I pop on there myself from time to time so there is a good
chance you will find a solution to your issue.
Also, don’t forget to become a member of BIRTReporting.com, it’s free to join and you will
automatically be alerted when the next part of this tutorial becomes available. Plus you will get
free access to the members area where you can find many other tutorials like this.
Finally, don’t forget that if you are a reader of either of my two books, “BIRT for Beginners” or
“BIRT for Maximo Self Study Workbook” then you can download the source files for this tutorial
from the readers area. This will allow you to use a pre-prepared BIRT report as a template for
creating these examples and any future Maximo styled reports that you need.
19
MORE INFORMATION
This book, written by Paul Bappoo, the founder of the
BIRT User Group UK presents an overview of the
open source BIRT tools and the commercial
enhancements available from Actuate, including...
Eclipse BIRT Designer, Actuate BIRT Designer,
iServer Express, Interactive BIRT viewer, Actuate
BIRT Studio, BIRT Spreadsheet Designer
With walkthrough tutorials of the main features,
including screenshots, from installation through data
selection to formatting reports and fully graphical
Flash charting this book will have you creating your
own reports from scratch in only a couple of hours.
If you are too busy to spend days learning software
and want tangible results fast then BIRT For
Beginners is for you.
When you buy the book you will automatically get
access to the readers section of my web site which
includes BIRT report request forms that you can
distribute to your end users, extra chapters in PDF
format and an ever growing library of reports,
tutorials, reviews, tips & tricks.
Buy the book and get free membership of BIRT Reporting and the BIRT User Group UK at:
http://www.BIRTReporting.com
Please feel free to share this address with your colleagues and inspire them to use BIRT to
create great looking reports.
I look forward to your feedback so please feel free to send me an email and let me know how you
get on with BIRT, provide feedback on this guide or share your tips and tricks.
Paul Bappoo
[email protected]
20