Topic: Trying to pull data that meets specific criteria Posted: 25 Apr 2013 at 7:26am
I am running a work order report for equipment with several different inspection types.
I am trying to pull only the equipment were the last 3 inspection meet the specific criteria. (Equipment may have 20 or more different inspections associated to it, I only want the one where the last 3 inspection were missing(service.servicecode = 20) so that the equipment can be removed from inventory)
Initially I was trying to go to select expert and just report the work orders where service.servicecode = 20 and was then going to supress records that count was less then 3.
This did not work becase I was only looking at that service code not the last 3 service codes..
New to crystal reports.. Any help would be much appreciated..
Are the services inspections on fixed dates, or do they occur random? If they occur every week for example, you could say that the service date has to be bigger then currentdate-21
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Posted: 26 Apr 2013 at 3:48am
How are your SQL skills? Unfortunately, this is not the type of filter that can be handled well using Crystal functionality. So, you're going to have to either write a Command, which is just a SQL Select statement using the syntax from your database, or create a Stored Procedure in your database that will return the data that you're looking for.
The equipment inspections are on a set schedule usually a weekly, monthly, semi-annual or annual. However, I have over 200 different types of equipment and the schedules for each type varies.
I am reporting on over 200,000 peices of equipment
How I have narrowed it down so far.
When Service.servicecode = 20 it does change the equipment.equipmentstatkey to 9. So in select expert I have it so that I am now only displaying equipment where the equipment.equipmentstatkey = 9 (This took my report from over 5,000 pages down to 80 )
I still would like to only see equipment where the last 3 inspections done used service.servicecode = 20 so any suggestions are very much appreciated.
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
)