What is the SQL you are generating? I would like to see that, before commenting.
`WOI:WorkOrder`.`Work Order ID`,
WHEN `WOI:WorkInfo`.`View Access` IS NOT NULL THEN 1
FROM `AR System Schema`.`WOI:WorkOrder`
LEFT OUTER JOIN `AR System Schema`.`WOI:WorkInfo`
`WOI:WorkOrder`.`Work Order ID` = `WOI:WorkInfo`.`Work Order ID`
`WOI:WorkOrder`.`ASGRP` IN ('Accounts Payable')
AND `WOI:WorkOrder`.`Status` IN ('Closed', 'Completed')
`WOI:WorkOrder`.`Work Order ID`
You need to move the calculated field you have created to the 'Where' clause to get a list of Work Orders.
The SQL in its current state is giving results as per your explanation and it is expected.
Shreyas, thank you very much. I am a little confused by what you mean to move the 'Where' clause. Here is a screen shot of what I have.
You can see that beside each work order ID it has the number of Work Info entries, this is working perfectly. I just do not want to see the Work Order IDs.
I want two columns - one showing the number of Work Orders that have 3 Work Info entries associated to it and the other to show 4 or more Work Info entries associated to the Work Order.
Thank you again for your help, it is much appreciated.
1 of 1 people found this helpful
When I said about 'Where' clause, I meant using 'Counting' field under Filters with condition >=3
You could use a section for bifurcating the Counting entries (i.e ones with 3 and ones which are greater than 4)
And instead of Work Order ID, use Count(Work Order ID)
Thanks Shreyas, but this did not work.
I want to see two numbers - column one how many work orders have 3 work info entries. Column two how many work order have 4 or more work info entries.
Three Work Info Entries Four + Work Info Entries
This is all I am looking for, but every way I try and do this report I cannot get just totals.
if Section doesn't work for you, then you can use sub query and append the column.
One query which basically mimics:
Select Count(Work Orders) where WorkInfoEntries = 3
Sub query, which mimics
Select Count(Work Orders) where WorkInfoEntries > 4
Thanks for your help Shreyas!
So there is no way to do a report just to have two columns with totals? I cannot use sub-query as I need to have other fields in my report.
Does anyone else have any suggestions ?