Maximo Ad Hoc Reporting Tom Rosenburg, General Motors Robert Clarkson, Raytheon

Maximo Ad Hoc Reporting
Tom Rosenburg, General Motors
Robert Clarkson, Raytheon
Pam Denny, IBM
© 2013 IBM Corporation
Agenda
1. Tom Rosenburg, General Motors
Email: [email protected]
Maximizing Ad Hoc Reporting with the Application Where
Clause
2. Robert Clarkson, Raytheon
Email: [email protected]
Extracting Ad Hoc Reports to BIRT Designer
1
Tom Rosenburg, General Motors Introduction
Maximo Local Business Administrator at the General Motors Spring Hill
Manufacturing Facility.
– 6.9 million square feet, and occupies 2,100 acres.
– Located in Spring Hill, 40 miles south of Nashville.
– Produced Saturn vehicles from July 1990 - March 2007.
– Produced the Chevrolet Traverse from Sept 2008 - November 2009.
– Currently, the site manufacturing operations consist of a flexible vehicle
assembly plant producing Chevrolet Equinox, an engine plant making 4cylinder engines, a stamping plant, two polymer injection molding operations
and one painting operation for bumper fascias.
– Manufactured engines and components supplied to GM assembly plants
around the globe.
Awards
2012: Wildlife Habitat Council: Wildlife Habitat certification
2012: EPA’s Energy Star® Challenge for Industry for Stamping/Powertrain Plants
2012: Ward’s North America “10 Best Engines” for Ecotec 2.0L Engine
2012: Tennessee Pollution Prevention Program: Performer status
2011: Tennessee Chamber of Commerce & Industry: Environmental Certificate of
Achievement
2010: Ward’s North America “10 Best Engines” for Ecotec 2.4L Engine
2
How GM Uses Ad Hoc Reporting
Utilize Ad Hoc Reporting for
- We need to know what Pm’s due this month were ‘Skipped’ or
not done last month.
- We needed a report from Inventory that showed the items that
have a value of over $100
- I needed a report from Asset , that lists the spare parts in the
spare parts table
The QBR’s above require data from other tables in order to select
the records to report on.
Examples of Ad Hoc Reporting include
–
–
–
–
Work Order
Job Plans
Inventory
Asset
3
The where clause, a key to unlocking QBR reports
Example #1: Work Order
Find Work Orders that were not filled out correctly with missing
crafts. I need to create a qbr to email to the area CAC letting
them know to correct these work orders
((woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and historyflag = 0
and siteid = '1536'
and istask = 0
and craft is null
and location in (select y.location from locancestor y
where y.siteid = '1536'
and y.ancestor = 'EN.LGE'))
4
From Work order, click the down arrow next to
Advanced Search
5
Click on the ‘Where Clause’
6
Paste the SQL Expression into the where clause.
7
You can now create the QBR from your results.
Click Select Action, then Run Reports
8
Then Create Report
9
You then Name the Report, select whether to
make it public, and save it
10
Select the fields you want on the report. Then,
very importantly, select whether to use the current
query when ever the report is run
11
Format the report. In this case, I grouped by
persongroup
12
Schedule, or submit the report. In this case,
scheduled once a month, on the 1st at 8am.
13
Locancestor table can be found in system
configuration, where ancestor is the same as
location
14
From Locations the ancestor is EN.LGE for all the
children locations, and their children , if any.
15
Example #2: Job Plans
Find job plans where there is an asset associated
with the job plan, and the asset is in location B1000.
We have a Site / Org division of the database, so I call out siteid, to
reduce the processing time of the query.
I also reference 2 different tables, in order to select the job plans
I want: JPASSETSPLINK and ASSET
– since all the tables have fields that have the same name I use an
alias for the table, to simplify the query.
X = JPASSETSPLINK and y = ASSET
(siteid = '1536‘ and jpnum in (select x.jpnum from jpassetsplink x
where x.siteid = '1536‘
and x.assetnum in (select y.assetnum from asset y
where y.location = 'B1000'
and y.siteid = '1536')))
16
I paste the query into the Job Plan where clause.
I can then write a QBR Report to email to the CAC’s
to clean these up.
17
Example #3: Inventory
Find information from multiple tables. Some fields are
in the Item table, and some are in Inventory.
Note: You don’t have to call out the inventory table. SQL assumes
inventory, because that is the main table the query is run from.
From the Inventory module, I want to see the Inventory for a group
of Items. I know the field IN2. I also want the inventory to be not
‘Obsolete’, and from a particular site. I am going to give the Item
Table an alias of ‘x’.
((status != 'OBSOLETE' and siteid = '1536') and
itemnum in (select x.itemnum from item x
where x.itemsetid = 'I1536'
and x.in2 in ('000-0D1KZ', VV.'000-0KKTN')))
18
I paste the query into the Inventory where clause.
This is an example of an ‘in’ statement.
19
Example #4: Inventory
Find spare parts with a current balance of ‘0’ for a
particular set of assets.
This could be a very useful QBR depending on how you
order your parts.
(status != 'OBSOLETE' and siteid = '1536'
and location != 'SPRING-HILL'
and itemnum in (select itemnum from sparepart where assetnum
like 'T__-_____')
and itemnum in (select x.itemnum from INVBALANCES x where
x.curbal = '0' and location != 'SPRING-HILL'))
20
Example #5: Inventory
Find inventory where the standard cost is greater than $100.
The std cost is not in the inventory table, so I need to get that from
the invcost table.
(status != 'OBSOLETE'
and siteid = '1536'
and itemnum in (select itemnum from invcost
where stdcost > 100
and siteid = '1536'))
21
I select the fields to create a QBR
22
I am going to group by itemnum to create a QBR
23
I now have a QBR report. However, after viewing
and saving, it requires additional modificationsV
Robert will detail these next
24
Extracting Reports to
BIRT Designer
25
Robert Clarkson, Raytheon Introduction
Six years working with Raytheon Company
Raytheon Company is a technology and innovation leader specializing in
defense, security and civil markets throughout the world. With a history of
innovation spanning 91 years, Raytheon provides state-of-the-art
electronics, mission systems integration and other capabilities in the areas
of sensing; effects; and command, control, communications and
intelligence systems; as well as a broad range of mission support services.
Six years working with Maximo and Maximo Reporting
Actuate
BIRT
Over 15 years as a computer programmer, professionally,
and over 25 years total
About five years working with Databases and SQL
26
When you may need to extend a QBR in BIRT Designer
Add functionality not available through the QBR
–
–
–
–
–
–
Special SQL functions (Max, Min, Nvl, Lead, Lag, etc V)
Access to procedures
Complex SQL scripting (sub-queries)
Formatting the data better (Currency, Conditional formatting)
Join to other data sources or databases
Complex Programming in Java
27
Applications UsedV
IBM Maximo
This example should be applicable
to versions 7.x
Oracle SQL Developer
Any SQL Development
Environment could be used
also like Sqlplus or TOAD
Eclipse BIRT Developer
This example should be
applicable to both “Indigo”
and “Ganymede” versions
And a Text Editing Software
This example uses Notepad++, but
any text editor like Notepad,
Wordpad will do also.
28
SQL Developer: Find the report to extract
SELECT *
FROM
Maximo.Reportdesign
WHERE
ImportedBy = ‘1676167’
ORDER BY ImportedDate DESC
Enter Your Maximo UserID or
whomever’s UserID that produced
the Ad-Hoc Report.
DESIGN
(Content)
REPORT
FILENAME
29
Text Editor: Save the Content
Filename
Remove the Double quotes
from the beginning and end of
the file content (red circles)
30
BIRT Developer: Open the file
31
BIRT Developer: Open Method Script
This is where the SQL Script is located. The script is made up
of a concatenated string of text. (purple text above)
32
SQL Developer: Extract the script
I format the script to make it easier to read and easier to modify.
Also, you may need to qualify the tables in the FROM statement with
“Maximo.” (see red lines above)
33
SQL Developer: Modify the script
34
BIRT Developer: Update OPEN Script
IMPORTANT!
Always place a trailing space before the closing double-quote marks; This
will insure that you don’t accidently concatenate the last word with the
first word on the next line.
35
BIRT Developer: Fetch Method Script
36
BIRT Developer: Update FETCH Script
37
BIRT Developer: Edit Data Set Dialog
Click here to open the Edit
Data Set Dialog
38
BIRT Developer: Check the changes
After
- Updating Open Script
- Updating Fetch Script
- Updating Data Set Columns
39
BIRT Developer: Edit the Layout
40
BIRT Developer: Insert Column
Insert Column Here
41
BIRT Developer: Insert Column (Continued)
42
BIRT Developer: Add the new field
NOTE:
Drag the “Avg. Unit Cost” from the left Data Set over to the empty “new”
column.
43
BIRT Developer: Format the new column
44
BIRT Developer: Preview the Report
NOTE: This preview has “Tivoli Software” and “IBM”, which is the default for
the Maximo templates. You can change the Master Page, if you want, but
Maximo has a separate set of Master Pages, so you will need a copy of
Maximo’s Master Pages, if you want the header and icons to match.
45
Maximo: Import the file into Maximo
NOTE: If you don’t have access to the
Report Administration application,
then submit your updated
*.rptdesign file to your Report or
Maximo Administrator
Search for your report by
using the “CreatedBy”
Column and entering your
Maximo UserID
46
Maximo: Import the file into Maximo (Continued)
47
Maximo: Import the file into Maximo (Continued)
Open the
“Import Report”
Dialog
48
Maximo: Import the file into Maximo (Continued)
Browse to the
*.rptdesign file
49
Maximo: Test the Report!
Click Preview, then Submit
50
Encrypting database credentials
Database connectivity file
51
Encrypting database credentials (continued)
#<DataSourceName>.<propertyName>=value
# driver for ORACLE
# oracle.jdbc.OracleDriver
# sample url for ORACLE
# jdbc:oracle:thin:@<HOST>:<PORT>:<SID>
# sample schemaowner for ORACLE
# maximo
# driver for SQLServer
# com.microsoft.sqlserver.jdbc.SQLServerDriver
# sample url for SQLServer
# jdbc:sqlserver://hostname:port;databaseName=dbname;integratedSecurity=false;
# sample schemaowner for SQLServer
# dbo
# driver for DB2
# com.ibm.db2.jcc.DB2Driver
# sample url for DB2
# jdbc:db2://localhost:50000/dbalias
# sample schemaowner for DB2
# maximo
Exposed Username and Password
maximoDataSource.url= jdbc:db2://IBM-A5:50000/DB2A
maximoDataSource.driver=com.ibm.db2.jcc.DB2Driver
maximoDataSource.username=wilson
maximoDataSource.password=wilson
maximoDataSource.schemaowner=maximo
52
Encrypting database credentials (continued)
1) Place your Username and Password into the mxreportdatasources.properties file
2) Navigate to the following folder:
• <V75>\reports\birt\tools (on the Maximo Server)
3) Execute encryptproperties.bat
4) This Utility will do the following:
• Add maximoDataSource.encrypted=true
• Encrypt your Username and Password (see below)
• Add escape characters to URL
maximoDataSource.encrypted=true
maximoDataSource.schemaowner=maximo
maximoDataSource.username=YaNJYGUPFrc\=
maximoDataSource.url=jdbc\:db2\://IBM-A5\:50000/DB2A
maximoDataSource.password=mEWNeVcBRfuBL54acL+JSg\=\=
maximoDataSource.driver=com.ibm.db2.jcc.DB2Driver
53
Questions?
54
Backup
55
Backup 1
This is a for a QBR , looking at Safety PM work that are past due, and they were due
sometime in the past 6 months. I like this type of date range , as it floats.
(woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and status not in ('COMP','CLOSE','CAN')
and istask = 0 and safety = 1
and siteid = '1536'
and (pmnum is not null or (parent is not null and jpnum is not null))
and targcompdate < add_months (trunc (sysdate, 'MM'), +0)
and targcompdate > add_months (trunc (sysdate, 'MM'), -6)
56
Backup 2
This is a query from inventory to select items from a particular
manufacturer , with a particular description. The description is in
Item, thus the sub select.
(status != 'OBSOLETE' and manufacturer = 'GR01'
and siteid = '1536'
AND itemnum in (select itemnum from item
where upper (description) like '%BELLOW%'))
57
Backup 3
This query is run from PM, and references the Route, Asset, and
Route_stop tables. Using the Alias’s x, y, Z
(siteid = '1536'
and (location = 'LGE.BLOCKS.C.LINE'
or assetnum in (select x.assetnum from asset x
where x.location = 'LGE.BLOCKS.C.LINE'))
or route in (select route from routes Y
where Y.route in (select Z.route from route_stop Z
where
Z.assetnum in (select x.assetnum from asset x
where x.location = 'LGE.BLOCKS.C.LINE'))))
58
QBR Report Output Example
59
Backup 4 This is based on the user that is
loged in, and the persongroup the user belongs
to. I use it for the users start center, however it
could be used for a qbr to email pm’s due this
month.(Notice nothing in RED) From Workorder
((woclass = 'WORKORDER' or woclass = 'ACTIVITY')
and historyflag = 0
and istask = 0
and siteid in (select LOCATIONSITE from PERSON where
PERSONID =:USER)
and persongroup in (select persongroup from
PERSONGROUPTEAM
where RESPPARTYGROUP = :USER)
and (pmnum is not null or (parent is not null and jpnum is not
null))
and targcompdate >= add_months (trunc (sysdate, 'MM'), +0)
and targcompdate < last_day(trunc(sysdate))+1)
60
Backup 5 To use the previous SQL on a start
center. Click on Change Content
61
Click on Select Content, then Result Set
62
Rename the result set , and click Finished
63
Click on the Pencil on the right of the result set
64
Filter for the query you want to report on with
your result set.
65
On the Column Display , at the min select
wonum , then save
66
Under Chart Options check chart by default
and select status in display by, then save , then
finished
67
The result is an interactive report , showing
what needs to be completed, and what has
been completed.
68
Clicking on the Chart takes you to the
workorder list.
69
BACKUP 7 FIND SPARE PARTS WITH NONE
ON HAND
(status != 'OBSOLETE' and 'siteid = '1536
and location != 'SPRING-HILL'
and itemnum in (select s.itemnum from sparepart s
where s.assetnum = 'BLK.OP068GR1A')
and itemnum not in (select x.itemnum from INVBALANCES x
where x.curbal >= '1' and x.location != 'SPRING-HILL' ))
In this I used an alias if s for the sparepart table and x for the
invbalances table. So I reference two tables from inventory to get
my result set. You can then create a QBR to generate on a set
schedule , to get this information to the correct people in your
organization.
70