If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Multiple queries from same table in one report

Started by KLJernigan600, 08 Dec 2022 06:09:54 PM

Previous topic - Next topic

KLJernigan600

I have a work order table.  I need to know work orders scheduled, open and complete. 

Query 1 All work orders with work type PM with a TargCompDate between 2 dates returns a count of work orders scheduled.

Query 2 - All work orders with a work type of PM and a work status of In Progress.

Query 3 - All work orders with a completion date between 2 dates and a status of Comp.

Need Query 4 to tie all these together I guess grouped on work order location then work order crew id.

I have tried structuring these queries multiple ways to get this output below but no good.

Location          Crew      WO Scheduled         WO Open          WO Complete
Bldg1              HVAC             10                         5                      5
Bldg 1             PLUM              9                          3                      6
Bldg 2             HVAC              20                       9                       11
Bldg 2             PLUM              5                          2                      3

MFGF

Quote from: KLJernigan600 on 08 Dec 2022 06:09:54 PM
I have a work order table.  I need to know work orders scheduled, open and complete. 

Query 1 All work orders with work type PM with a TargCompDate between 2 dates returns a count of work orders scheduled.

Query 2 - All work orders with a work type of PM and a work status of In Progress.

Query 3 - All work orders with a completion date between 2 dates and a status of Comp.

Need Query 4 to tie all these together I guess grouped on work order location then work order crew id.

I have tried structuring these queries multiple ways to get this output below but no good.

Location          Crew      WO Scheduled         WO Open          WO Complete
Bldg1              HVAC             10                         5                      5
Bldg 1             PLUM              9                          3                      6
Bldg 2             HVAC              20                       9                       11
Bldg 2             PLUM              5                          2                      3

Hi,

Maybe just do this in one query, with the WO Scheduled, WO Open and WO Complete values defined as calculations?

eg

First calculation - WO Scheduled
if ([Work Type] = 'PM' and [TargCompDate] between <your start date> and <your end date>) then (1) else (0)

Second calculation - WO Open
if ([Work Type] = 'PM' and [Work Status] = 'In Progress') then (1) else (0)

Third calculation - WO Complete
if ([Completion Date] between <your start date> and <your end date> and [Work Status] = 'Comp') then (1) else (0)

You will probably need to bring in the items from the package rather than the query, so you'll see [Your Package].[Your Query Subject].[Work Type] in your expression rather than just [Work Type].
You will probably also need to change the Detail Aggregation and Summary Aggregation properties of your calculations to be Total, also.

Does this work for you?

Cheers!

MF.
Meep!

KLJernigan600