Maximo Open Forum

How would I write a front-end query for Reporting to find work orders by the previous week.  I would like it to be dynamic, so that each time it runs it only shows the previous week's work orders by their initial creation date (WORKORDER.REPORTDATE).  I'd also like to expand this to a monthly query eventually.  I'm a SaaS environment so this would have to be through the Queries application which is fine.
#Reporting

------------------------------
Ryan Sheeler
------------------------------

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant
------------------------------

here is my Where clause from the Adv Search in Work Order Tracking.  

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)

Can your addition go in there so it would look like this?4

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and createddate between sysdate- 7 days and sysdate)

------------------------------
Ryan Sheeler
------------------------------

Original Message:
Sent: 06-08-2022 11:20
From: pavan Kumar
Subject: Query by dynamic date range

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant
------------------------------

I think you are looking for reportdate, but at any rate, I just ran this to confirm on the 7.6 demo site

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and reportdate between sysdate-7 and sysdate

Works just fine

------------------------------
Christopher Winston
Projetech Inc.
------------------------------

Original Message:
Sent: 06-08-2022 11:28
From: Ryan Sheeler
Subject: Query by dynamic date range

here is my Where clause from the Adv Search in Work Order Tracking.  

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)

Can your addition go in there so it would look like this?4

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and createddate between sysdate- 7 days and sysdate)

------------------------------
Ryan Sheeler
------------------------------


Original Message:
Sent: 06-08-2022 11:20
From: pavan Kumar
Subject: Query by dynamic date range

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant

Thanks that was it - thanks for the help.   Eventually I'd like to extend it to show monthly or quarterly,  and CLOSED work orders instead of newly created ones too.

------------------------------
Ryan Sheeler
------------------------------

Original Message:
Sent: 06-08-2022 11:58
From: Christopher Winston
Subject: Query by dynamic date range

I think you are looking for reportdate, but at any rate, I just ran this to confirm on the 7.6 demo site

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and reportdate between sysdate-7 and sysdate

Works just fine

------------------------------
Christopher Winston
Projetech Inc.
------------------------------


Original Message:
Sent: 06-08-2022 11:28
From: Ryan Sheeler
Subject: Query by dynamic date range

here is my Where clause from the Adv Search in Work Order Tracking.  

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)

Can your addition go in there so it would look like this?4

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and createddate between sysdate- 7 days and sysdate)

------------------------------
Ryan Sheeler


Original Message:
Sent: 06-08-2022 11:20
From: pavan Kumar
Subject: Query by dynamic date range

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant

@Ryan Sheeler What you may want to do is a Query Based Report, with runtime parameters. You will need your report administrator (which may well be you) to update the report after creation, to then add the parameters. An example is in the video:



------------------------------
Christopher Winston
Projetech Inc.
------------------------------

Original Message:
Sent: 06-08-2022 14:54
From: Ryan Sheeler
Subject: Query by dynamic date range

Thanks that was it - thanks for the help.   Eventually I'd like to extend it to show monthly or quarterly,  and CLOSED work orders instead of newly created ones too.

------------------------------
Ryan Sheeler
------------------------------


Original Message:
Sent: 06-08-2022 11:58
From: Christopher Winston
Subject: Query by dynamic date range

I think you are looking for reportdate, but at any rate, I just ran this to confirm on the 7.6 demo site

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and reportdate between sysdate-7 and sysdate

Works just fine

------------------------------
Christopher Winston
Projetech Inc.


Original Message:
Sent: 06-08-2022 11:28
From: Ryan Sheeler
Subject: Query by dynamic date range

here is my Where clause from the Adv Search in Work Order Tracking.  

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)

Can your addition go in there so it would look like this?4

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and createddate between sysdate- 7 days and sysdate)

------------------------------
Ryan Sheeler


Original Message:
Sent: 06-08-2022 11:20
From: pavan Kumar
Subject: Query by dynamic date range

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant

a.  Can you add QBE to a work queue on operational dashboards.

b.  What could you use for reporting by logged in users work group?

As always, thank you.

------------------------------
Maureen Holland
NBC Universal
------------------------------

Original Message:
Sent: 06-09-2022 08:38
From: Christopher Winston
Subject: Query by dynamic date range

@Ryan Sheeler What you may want to do is a Query Based Report, with runtime parameters. You will need your report administrator (which may well be you) to update the report after creation, to then add the parameters. An example is in the video:

------------------------------
Christopher Winston
Projetech Inc.
------------------------------


Original Message:
Sent: 06-08-2022 14:54
From: Ryan Sheeler
Subject: Query by dynamic date range

Thanks that was it - thanks for the help.   Eventually I'd like to extend it to show monthly or quarterly,  and CLOSED work orders instead of newly created ones too.

------------------------------
Ryan Sheeler


Original Message:
Sent: 06-08-2022 11:58
From: Christopher Winston
Subject: Query by dynamic date range

I think you are looking for reportdate, but at any rate, I just ran this to confirm on the 7.6 demo site

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and reportdate between sysdate-7 and sysdate

Works just fine

------------------------------
Christopher Winston
Projetech Inc.


Original Message:
Sent: 06-08-2022 11:28
From: Ryan Sheeler
Subject: Query by dynamic date range

here is my Where clause from the Adv Search in Work Order Tracking.  

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0)

Can your addition go in there so it would look like this?4

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and createddate between sysdate- 7 days and sysdate)

------------------------------
Ryan Sheeler


Original Message:
Sent: 06-08-2022 11:20
From: pavan Kumar
Subject: Query by dynamic date range

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant

Thanks everyone - all of these are really helpful.  I'll probably end up using some of all of them in one way or another!

------------------------------
Ryan Sheeler
------------------------------

Original Message:
Sent: 06-08-2022 11:20
From: pavan Kumar
Subject: Query by dynamic date range

Hi Ryan,

Try using below query

createddate between sysdate- 7 days and sysdate

------------------------------
PAVAN KUMAR
Maximo Consultant
------------------------------

For Oracle Databases, sysdate will work just fine.  But for MSSQL use GETDATE() with DATEADD function something like this:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and reportdate between DATEADD(day,-7,GETDATE()) and GETDATE()) 

I am not sure how to write it in DB2.
thanks!


------------------------------
Eric Godel
Bay Systems Consulting
------------------------------

what database you are using?
if you are using db2 then "((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and reportdate between sysdate-7 and sysdate" it works.
confirm me what db you're using

------------------------------
PAVAN KUMAR
Maximo Consultant
------------------------------

Original Message:
Sent: 06-08-2022 13:04
From: Eric Godel
Subject: Query by dynamic date range

For Oracle Databases, sysdate will work just fine.  But for MSSQL use GETDATE() with DATEADD function something like this:

((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and reportdate between DATEADD(day,-7,GETDATE()) and GETDATE()) 

I am not sure how to write it in DB2.
thanks!


------------------------------
Eric Godel
Bay Systems Consulting
------------------------------

I wonder if you could write the query in such a way that it could be prompted for a date range, instead of just returning the previous week from the sysdate?

------------------------------
Ryan Sheeler
------------------------------

No way to do that in the where clause of an application to generate a prompt. A saved query could however be edited.

A QBR could be edited in report administration as well, to get date prompts.

------------------------------
Christopher Winston
Projetech Inc.
------------------------------

Original Message:
Sent: 06-08-2022 16:54
From: Ryan Sheeler
Subject: Query by dynamic date range

I wonder if you could write the query in such a way that it could be prompted for a date range, instead of just returning the previous week from the sysdate?

------------------------------
Ryan Sheeler
------------------------------

Original Message:
Sent: 6/8/2022 4:55:00 PM
From: Ryan Sheeler
Subject: RE: Query by dynamic date range

I wonder if you could write the query in such a way that it could be prompted for a date range, instead of just returning the previous week from the sysdate?

------------------------------
Ryan Sheeler
------------------------------

I run multiple reports for the previous month. I use a dynamic query that selects records where the date falls in the previous month in my automated reports. I change the data field to reflect the record set I am looking for.

(woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and workorder.reportdate >= add_months(trunc(sysdate, 'MON'),-1) and workorder.reportdate < trunc(sysdate, 'MON')

I run my reports for multiple sites so I do not include a siteid. We are using Oracle.

------------------------------
Kevin Egolf
TechOps
------------------------------

Hi Ryan

I think the following would give you the previous calendar weeks reported work orders (for Oracle db):

historyflag = 0 and istask = 0 and reportdate >= trunc(sysdate, 'IW')-7 and reportdate < trunc(sysdate, 'IW')

Kind Regards

Phil

------------------------------
Phil Naylor
MACS EU
------------------------------

For what it is worth, you could I suppose create a custom app to make it dynamic by creating an app to manage dates. If it just for work orders maybe add a new Tab in Work Order Tracking.  I played in our development and used existing custom app. Although it isn't a simple pop up prompt it would probably be easier then opening a saved query and modifying.

my saved where clause looks like reportdate between (select to_date(userlist.colval1,'MM/DD/YYYY') and (select to_date(userlist.colval2,'MM/DD/YYYY') from userlist where value = 'DYNDATE')

------------------------------
Wes Williams
Northrop Grumman
------------------------------