Data Connectivity
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Data Connectivity
Message Icon Topic: Assessment Post Reply Post New Topic
Author Message
dsenger
Newbie
Newbie


Joined: 17 Jul 2012
Online Status: Offline
Posts: 8
Quote dsenger Replybullet Topic: Assessment
    Posted: 02 Oct 2013 at 5:43am
I am trying to pull the last assessment completed on an individual.  I use the Assessment_Seq but it still pulls all of them.  Any suggestions??
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 03 Oct 2013 at 9:26am
How good are you SQL skills?
 
There are two ways that I know of to do this:
 
1.  Instead of linking tables together in your report, use a Command.  A Command is just a SQL select statement.  The trick is that you MUST include all of the fields that are required for your report.  There will be a big performance hit if you link a command to either another command or to any tables.  If you need to filter your data based on parameters, you must create the parameters in the command editor and use them in the Where clause of the command - parameters create in the main report will not work.  However, in the main report you can modify parameters created in the command editor to make them dynamic or or set other properties.  Using the Select Expert to filter a command will also cause a performance issue.
 
2.  Create a View in the database that will pull all of the data for the report.  Then build the report based on the view.
 
The basic logic for the query/command will be something from this:
 
Select <all required fields>
from <primary table>
  <inner or left> join <next table> on <linking fields>
  ...
  inner join (
    select personId, max(assessment_date) as last_date
    from assessment_table
    group by personId) as max_date
    on assessment_table.personId = max_date.personId
      and assessment_table.assessment_date = max_date.last_date
where <filter conditions>
 
Using the inner join from the data to the max_date query will automatically filter the assessments to only the most recent.
 
-Dell
IP IP Logged
dsenger
Newbie
Newbie


Joined: 17 Jul 2012
Online Status: Offline
Posts: 8
Quote dsenger Replybullet Posted: 09 Oct 2013 at 4:44am
Thanks so much for the quick response but I can't get it to work.  Here is what I entered for the formula:  inner join (select {OFFENDER_BOOKINGS.ROOT_OFFENDER_ID}, max({OFFENDER_ASSESSMENTS.NEXT_REVIEW_DATE})
where <{OFFENDER_ASSESSMENTS.NEXT_REVIEW_DATE} is <= {?Next Review Month}
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 09 Oct 2013 at 4:57am
Where are you putting this?  This is Crystal syntax and you need the syntax for your database if you're creating a command.  The join in that SQL would look something like this:
inner join (
  select 
    ROOT_OFFENDER_ID,
    max(NEXT_REVIEW_DATE)  as maxReviewDate
  from OFFENDER_BOOKINGS
  where NEXT_REVIEW_DATE is <= {?Next Review Month}
  group by  ROOT_OFFENDER_ID) as reviewDates
  on OFFENDER_ASSESSMENTS.ROOT_OFFENDER_ID = reviewDates.ROOT_OFFENDER_ID
    and OFFENDER_ASSESSMENTS.NEXT_REVIEW_DATE =reviewDates.maxReviewDate
 
Please post the whole SQL that you're using in the Command and I'll see what I can do to help you with it.
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
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



This page was generated in 0.031 seconds.