Help setting specific values 3

[IvySkye]

I am new to Actuate. I've been using Crystal for over 6 years. Can someone please tell me how to specify values for a field? For example I want to look at ALL records that are NOT Cancelled, Closed, or Complete. This was easy in Crystal. I can't figure out how to set this restriction in ERD Pro. Thank you in advance

 

[wesw]

I assume your are using Maximo. If it is version 5.x then you can open the datastream <object> (Graphical query) click on the conditions tab and add to the where clause. If it is Maximo 6 (MXES) open the datastream <object> here you'll have a textual query where you would add to the where clause.

I think after a while you'll find Actuate although complicated is a very powerful reporting tool.

Wes

 

[ASG0856]

other option - filter in the SQL.
in the condition tab, the is an text area under the column name and query expression block. Enter your condition as

DatabaseName.Table.FieldName Not In('Cancelled','Complete','Closed')

 

[IvySkye]

Thank you. Yes, I am using Maximo 5.1. I've tried both solutions but continue to get an error in my SQL. My code is below. I only get the error when I add

and maxamr.dbo.workorder.status Not in ('CANCEL', 'COMP', 'CLOSE')

to the where clause (probably syntax)? Any ideas?

SELECT maxamr.dbo.workorder.wonum, maxamr.dbo.workorder.description, maxamr.dbo.workorder.jpnum, maxamr.dbo.workorder.worktype, maxamr.dbo.workorder.status, maxamr.dbo.workorder.calcpriority, maxamr.dbo.workorder.eqnum, maxamr.dbo.workorder.location, maxamr.dbo.workorder.crewid, maxamr.dbo.workorder.supervisor, maxamr.dbo.workorder.targstartdate, maxamr.dbo.workorder.targcompdate, maxamr.dbo.workorder.siteid

FROM maxamr.dbo.workorder

WHERE maxamr.dbo.workorder.siteid LIKE aram_SiteID and maxamr.dbo.workorder.status Not in ('CANCEL', 'COMP', 'CLOSE')

ORDER BY maxamr.dbo.workorder.targstartdate

 

[wesw]

I think Maximo only wants owner.tablename check options.
Does the code work in the query analyzer?
also workorder.siteid = :mrosite

Also do you have any synonyms of 'CANCEL', 'COMP', 'CLOSE', 'INPRG' ETC?

if so then you should use something like this
workorder.status in (select value from valuelist where listname ='WOSTATUS' and maxvalue in ('INPRG','WSCH','WMATL','APPR'))

You say it only fails when you add (and...)

Is this in the bottom pane of the conditions window? If so remove the and and then check the SQL tab and see it it looks right.

Wes

http://www.weswilliams.net

 

[IvySkye]

Thank you. I've set the site ID as a parameter because we are a global company using a regional dB. This allows each site to filter and see only their WOs.

I can now get it to run, however, I'm getting 336 blank pages so I am still not sure if the query is working.

 

[ASG0856]

Have you entered this and maxamr.dbo.workorder.status Not in ('CANCEL', 'COMP', 'CLOSE') in the filter area exactly as printed. If so dump the and.

 

[IvySkye]

When I drop the 'and' I receive an incorrect syntax error

WHERE maxamr.dbo.workorder.siteid LIKE aram_SiteID
and
maxamr.dbo.workorder.status Not in ('CANCEL', 'COMP', 'CLOSE')

 

[ASG0856]

Try putting the the aram_SiteID in Brackets so it becomes

WHERE maxamr.dbo.workorder.siteid LIKE Param_SiteID)
and
maxamr.dbo.workorder.status Not in ('CANCEL', 'COMP', 'CLOSE')

 

[wesw]

Click on the datastream, click the methods tab, scroll down to the function obtainselectstatement add the following; setclipboardtext(ObtainSelectStatement)

so it looks like this

ObtainSelectStatement = Super::ObtainSelectStatement( )
setclipboardtext(ObtainSelectStatement)

End Function

This will put the sql statement into the clipboard. You can then paste it into the query anaylzer or sql plus etc. and see exactly what is going on. You can also post the output here etc.


Wes W.

 

[tav1035]

IvySkye,
Your in statement appears to have bad syntax.
try->
WHERE maxamr.dbo.workorder.siteid LIKE aram_SiteID
and maxamr.dbo.workorder.status Not in ('CANCEL','COMP','CLOSE')
There were spaces between your statuses.

I pasted your 'CANCEL', 'COMP', 'CLOSE'
into this tool and it gives you the proper syntax everytime.

http://www.datapaste.com/pub/demoworkorder.htm

tav