Displaying A non Persistent field from a table in BIRT
HI Guys,
I am designing a very simple report in BIRT for Maximo 7.1 .The tables that are used in this SR[Service Request] and CLASSSTRUCTURE Table in Maximo database.
The Column and the Fields that are going to be displayed in the report are as follows
TABLE COLUMN
SR TICKETNUM
SR ASSETID
SR BCUSTOMERNAME
SR BCUSTOMERADDRESS
SR BTELEPHONEHOME
SR BTELEPHONEMOB
SR BTELEPHONEWORK
SR REPORTDATE
SR REPORTBYID
SR BCALLERNAME
SR BCALLERTELMOB
CLASSSTRUCTURE HEIRARCHYPATH
The problem here is that CLASSSTRUCTURE table does not have a persistent HEIRARCHYPATH column in it that means this column doesnt exist in maximo databse table of classstructure.
Now can anyone please throw some light on the thing that CAN ONE PULL A non persistent Column into BIRT dataset for this report [e.g let us say Dataset1]
.If yes Please kindly explain.
Please this is urgent do send in your responses.
If you require more info on this also let me know.
I will post the same
Thanks
AJ
I am designing a very simple report in BIRT for Maximo 7.1 .The tables that are used in this SR[Service Request] and CLASSSTRUCTURE Table in Maximo database.
The Column and the Fields that are going to be displayed in the report are as follows
TABLE COLUMN
SR TICKETNUM
SR ASSETID
SR BCUSTOMERNAME
SR BCUSTOMERADDRESS
SR BTELEPHONEHOME
SR BTELEPHONEMOB
SR BTELEPHONEWORK
SR REPORTDATE
SR REPORTBYID
SR BCALLERNAME
SR BCALLERTELMOB
CLASSSTRUCTURE HEIRARCHYPATH
The problem here is that CLASSSTRUCTURE table does not have a persistent HEIRARCHYPATH column in it that means this column doesnt exist in maximo databse table of classstructure.
Now can anyone please throw some light on the thing that CAN ONE PULL A non persistent Column into BIRT dataset for this report [e.g let us say Dataset1]
.If yes Please kindly explain.
Please this is urgent do send in your responses.
If you require more info on this also let me know.
I will post the same
Thanks
AJ
0
Comments
-
From Maximo Perspective If one goes into Database Configuration by logging into Maximo 7.1 and searches for CLASSSTRUCTURE object i.e table .One can find in the Attribute tab [That is column] that HEIRARCHYPATH is present as a Non- Persistent Field.
So any suggestion of manually entering the field into the CLASSSTRUCTURE table ccannot be done in maximo .
Our aim is to pull this non persistent field called HEIRARCHYPATH and show it on the rptdesign along with a label called Classifcation : row["HEIRARCHYPATH"]
If any more clarification is needed..kindly reply in.
thanks
AJ0 -
Hello,<br />
I guess that i'm answering too late, but i believe my solution can help to somebody.<br />
<br />
<br />
Im using Maximo 7.5.0.3 and SQL server 2008.<br />
<br />
All u need is to use nested dataset. <br />
<em class='bbc'>maximoDataSet.open()<br />
{<br />
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());<br />
maximoDataSet.open();<br />
var sqlText = new String();<br />
sqlText += "select assetnum, classstructureid from asset "<br />
maximoDataSet.setQuery(sqlText);<br />
}</em><br />
<br />
DataSet fetch method is called for every row of selected data.<br />
so we need to create our dataset in the fetch method.<br />
<br />
<em class='bbc'>maximoDataSet.fetch(){<br />
if (!maximoDataSet.fetch())<br />
return (false);<br />
<br />
row["assetnum"] = maximoDataSet.getString("assetnum");<br />
<br />
<br />
<strong class='bbc'>//gets asset.classstructureid<br />
var classstructureid = maximoDataSet.getString("classstructureid");<br />
<br />
<br />
var hierarchypath = new String();<br />
if( classstructureid != null) {<br />
downtimeDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "classstructure");<br />
downtimeDataSet.open();<br />
<br />
var downSql = " with Hierarchy(classstructureid, parent, Level) "<br />
+ " as "<br />
+ " ( "<br />
+ " select classstructureid, parent, 0 as Level "<br />
+ " from classstructure l "<br />
+ " where l.classstructureid = '" + classstructureid +"'"<br />
+ " union all "<br />
+ " select l.classstructureid, l.parent, ch.Level + 1 "<br />
+ " from classstructure l "<br />
+ " inner join Hierarchy ch "<br />
+ " on ch.parent = l.classstructureid " <br />
+ " ) "<br />
+ " select Hierarchy.*, classstructure.classificationid "<br />
+ " from Hierarchy "<br />
+ " join classstructure on classstructure.classstructureid = Hierarchy.classstructureid "<br />
+ " order by Level desc ";<br />
<br />
<br />
downtimeDataSet.setQuery(downSql); // gets classstructure hierarchy of our classification<br />
<br />
<br />
while (downtimeDataSet.fetch()) { // adds " \ " between recrods<br />
hierarchypath += downtimeDataSet.getString("classificationid") + " \\ "; <br />
} <br />
downtimeDataSet.close();<br />
<br />
// removes " \ " after last classification<br />
hierarchypath = hierarchypath.substring(0,hierarchypath.length -2);<br />
<br />
row["hierarchypath"] = hierarchypath;<br />
}</strong><br />
return (true);<br />
}</em>0 -
<blockquote class="ipsBlockquote" data-author="IldarGalik" data-cid="112359" data-time="1355300280">
<div>
<p>Hello,<br>
I guess that i'm answering too late, but i believe my solution can help to somebody.<br><br><br>
Im using Maximo 7.5.0.3 and SQL server 2008.<br><br>
All u need is to use nested dataset.<br><em>maximoDataSet.open()<br>
{<br>
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());<br>
maximoDataSet.open();<br>
var sqlText = new String();<br>
sqlText += "select assetnum, classstructureid from asset "<br>
maximoDataSet.setQuery(sqlText);<br>
}</em><br><br>
DataSet fetch method is called for every row of selected data.<br>
so we need to create our dataset in the fetch method.<br><br><em>maximoDataSet.fetch(){<br>
if (!maximoDataSet.fetch())<br>
return (false);<br><br>
row["assetnum"] = maximoDataSet.getString("assetnum");<br><br><br><strong>//gets asset.classstructureid<br>
var classstructureid = maximoDataSet.getString("classstructureid");<br><br><br>
var hierarchypath = new String();<br>
if( classstructureid != null) {<br>
downtimeDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), "classstructure");<br>
downtimeDataSet.open();<br><br>
var downSql = " with Hierarchy(classstructureid, parent, Level) "<br>
+ " as "<br>
+ " ( "<br>
+ " select classstructureid, parent, 0 as Level "<br>
+ " from classstructure l "<br>
+ " where l.classstructureid = '" + classstructureid +"'"<br>
+ " union all "<br>
+ " select l.classstructureid, l.parent, ch.Level + 1 "<br>
+ " from classstructure l "<br>
+ " inner join Hierarchy ch "<br>
+ " on ch.parent = l.classstructureid "<br>
+ " ) "<br>
+ " select Hierarchy.*, classstructure.classificationid "<br>
+ " from Hierarchy "<br>
+ " join classstructure on classstructure.classstructureid = Hierarchy.classstructureid "<br>
+ " order by Level desc ";<br><br><br>
downtimeDataSet.setQuery(downSql); // gets classstructure hierarchy of our classification<br><br><br>
while (downtimeDataSet.fetch()) { // adds " \ " between recrods<br>
hierarchypath += downtimeDataSet.getString("classificationid") + " \\ ";<br>
}<br>
downtimeDataSet.close();<br><br>
// removes " \ " after last classification<br>
hierarchypath = hierarchypath.substring(0,hierarchypath.length -2);<br><br>
row["hierarchypath"] = hierarchypath;<br>
}</strong><br>
return (true);<br>
}</em></p>
</div>
</blockquote>
<p> </p>
<p> </p>
<p>What are the required modification to make this work on an oracle database</p>0 -
HI Steve ,
I am also facing the same issue in Maximo BIRT 4.3.2 version .What are all the steps that i need to follow from the above script .I can able understand the section Open and Fetch section.I was not able to under stand the Highlighted script
0
Categories
- All Categories
- 107 Developer Announcements
- 53 Articles
- 106 General Questions
- 142 IM Services
- 43 OpenText Hackathon
- 32 Developer Tools
- 20.6K Analytics
- 4.1K AppWorks
- 8.9K Extended ECM
- 898 Cloud Fax and Notifications
- 76 Digital Asset Management
- 9.3K Documentum
- 29 eDOCS
- 118 Exstream
- 39.8K TeamSite
- 1.7K Web Experience Management