nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Missing data in maximo75

oraBirt

HI, All,
Thanks in advance!
Birt designer (3.7) returns same dataset as from SQL developer, yes, same data source, but after the report is imported into maximo, it returns less data.
Please see the attached files.
 Thanks very much.

Find more posts tagged with

Comments

wwilliams

Try putting your sqlText in the header as a data element so you can cut and paste from Maximo, then run that in your sql developer and see what you get

DanielleS

Yes, do what wwilliams said. Also, make sure the "Use Where Clause?" button is unchecked in report administration for that report. Alternatively if you have any global data restrictions on the app that WILL affect your results. Doing what wwilliams said will see if there is any extra restrictions added on to your query.
 
If there is, you can force the report to ignore the restrictions by putting 
<pre class="_prettyXprint">
params["where"] = "1=1"</pre>
before your sqlText query in your open script. This is a last resort though, ignoring restrictions put in place for a reason can be very bad.

oraBirt

Thanks very much for your help! Is there any example how to display sqltext in the header?

wwilliams

After looking at your report, I am thinking Danielle is on the right track. When you run the report from eclipse what value is the parameter where? is it 1=1? I'd just use this as the sql, Maximo is passing additional criteria which is limiting the number of returned rows.
 
If you have + " and wopriority in (2,3,4,5) " why do you also have + " and wopriority is not null "
<pre class="_prettyXprint">
sqlText = "select distinct persongroup" //,actfinish,reportdate, wopriority "
+ " from workorder"
// Include the Maximo where clause
//+ " where " + params["where"]
+ " where Actfinish >= add_months(sysdate, -6 ) " //should be 6
+ " and worktype not in ('FT', '1JOB', 'JOB', 'PM','TS') "
+ " and wopriority is not null "
+ " and persongroup is not null "
+ " and reportdate >= to_date('03/31/2014' , 'MM/DD/YYYY')" //need this for prod
+ " and wopriority in (2,3,4,5) "
+ " order by 1"

</pre>

oraBirt

Yes, parameter is 1=1 in eclipse. Yes, you are right, no need to have "wopriority is not null ", thanks for correcting me!
After add params["where"] = "1=1" before the sqlText query in the open script, maximo returns correct data. but I have some questions:
1. how does maximo limit the number of returned rows? Any rule for maximo to limit data?
2. After it returns correct data, and I tried to export the data into excel file, but from this link: <a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/11672583/why-is-my-crosstab-being-cut-off-in-excel'>http://stackoverflow.com/questions/11672583/why-is-my-crosstab-being-cut-off-in-excel</a>, what the exported excel missed is the left side of the report, the first column( a group). If the column sizes are manually resized, then the right side of the report missed. 
Any idea?
3. Again, really like to know how to display sqlText as data element in header (searched a lot, not work yet)
 
Your time and patience is really appreciated!

wwilliams

In your beforeFactory enter sqltxt = new String();
 
In your Main Select (or whichever) add
; sqltxt = sqlText; 
maximoDataSet.setQuery(sqlText);
 
Then put a data element in your header with the expression sqltxt
 
Now when it runs in Maximo you'll see the sql it is using. (Remember to hide it when you put it into production)