Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Select Latest Records For Each Individual / Site Post Reply Post New Topic
Author Message
elGroomio
Newbie
Newbie


Joined: 12 Apr 2013
Online Status: Offline
Posts: 4
Quote elGroomio Replybullet Topic: Select Latest Records For Each Individual / Site
    Posted: 21 Aug 2014 at 12:30am
Hi There,

I have a report that runs from 4 tables in the database.

Table 1 = SiteDetails
Table 2 = SubSiteDetails
Table 3 = SubSiteDetails2

The relationship between 1 and 2 and 1 and 3 is one to one.

Table 4 = VisitDetails

The relationship between 1 and 4 is one to many. I.e. a site can have multiple visits.

I am trying to run a report that pulls off site details from the first three tables along with details of their last visit. However, when I run the report it creates multiple lines per site as many have had multiple visits.

How can I return just one line per site with the details for the last occurring visit for each site?

Any help will be most appreciated!

Many thanks,
Andrew
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet Posted: 21 Aug 2014 at 12:40am
you could group your report on your site ID, and than use a MAX on your visit date/time
IP IP Logged
elGroomio
Newbie
Newbie


Joined: 12 Apr 2013
Online Status: Offline
Posts: 4
Quote elGroomio Replybullet Posted: 21 Aug 2014 at 1:53am
Hi Gurbs,

Thanks for your reply.. I couldn't quite figure the maximum function out. When I tried to use it it produced errors such as 'function cannot be used as it must be evaluated later'.

However, I think I have came up with a workaround. I have grouped by site ID, added the fields in the group header, then descending sort on the date field and hidden the details section. It seems to have worked..

However, if you can provide more information I'd be interested in learning of a better way!

Thanks again.

Andrew
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet Posted: 21 Aug 2014 at 2:04am
If you group your report in site ID, you get 2 extra sections in your report: Group Header and Group Footer. You can insert a summary here. If you select your date/time for your visit, you can select MAX as summary type.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Aug 2014 at 9:36am
Gurbs is indicating how to get a singular field value as a maximum.
I believe you want all of the values from the row with the max date (for that group/site).
Your approach of using sorting and then placing the field on the GH works fine.
When you place fields in a header or footer they will display the first(header) or last (footer) record that was before the header or footer weas printed.
Remember that duppressing rows (or fields) do not exclude them from the report or any calculations that you are doing.
YOu can alos use Gurbs suggestion in conjunction with a suppression formula, assuming you only have opne row that meets your maximum.
The suppression formula would be something like
datetimefield<>maximum(datetimefield,groupfield)
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.