Print Page | Close Window

Trying to pull data that meets specific criteria

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19467
Printed Date: 08 May 2024 at 12:19pm


Topic: Trying to pull data that meets specific criteria
Posted By: Tonyak74
Subject: Trying to pull data that meets specific criteria
Date 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..
Thanks,
TK


Replies:
Posted By: Gurbs
Date Posted: 25 Apr 2013 at 10:49pm
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

Posted By: hilfy
Date 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.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Tonyak74
Date Posted: 26 Apr 2013 at 5:06am
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 Tongue)
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.
 

Posted By: Tonyak74
Date Posted: 26 Apr 2013 at 5:15am

SQL skills are not good, but hopefully I have a colleague that will be able to assist..

Thank you...

Posted By: hilfy
Date Posted: 26 Apr 2013 at 7:44am
Here some possible logic to use in nested select statements:
Select Equipment ID and any other fields needed for the report from
(
  Select Equipment ID, sum(case servicecode = 20, 1, 0) from
  (
    Select Equipment ID, Service Date from
    (
      Select Equipment ID, Service Date from service order by Service Date descending.
    )
    where rownum <= 3
  )
  having sum(case servicecode = 20, 1, 0) = 3
) join to any other tables you need to get the data.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window