Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Summary Count, Don't Suppress 0 Post Reply Post New Topic
Author Message
omerso
Newbie
Newbie


Joined: 03 Apr 2012
Online Status: Offline
Posts: 3
Quote omerso Replybullet Topic: Summary Count, Don't Suppress 0
     Posted: 03 Apr 2012 at 2:31am
I've created a report (Crystal XI) with basic info from health care facilities in a given geographic area. What my client wants is a statistic of how many times her organization services each facility in a given date range. When I inserted the summary count of that data, the number is correct for each facility, but facilities that were not serviced in that date range are hidden because the summary count value is 0.
For example, if my date range is 1/1/12-4/2/12, the first entries are:
Name                            Count
Abbey Care Center             2
Ackert Manor                    14
Advanced Endoscopy         2
 
But if my date range is 1/1/11-4/2/12, the first entries are:
 
Name                             Count
Abbey Care Center             28
Abi Surgical Services          4
Ackert Manor                     23
Advanced Endoscopy          2
The client wants to export this data to a separate Excel spreadsheet, then copy and paste the count, but I need the list of facilities to remain constant. Is it possible to keep the summary from suppressing facilities with a count value of 0, so that everytime the report is generated, the same list of facilities appear? In the above example, I would want Abi Surgical Services to appear in the first report (date range 1/1/12-4/2/12) with a count of 0).
 
Thanks for your help. It's my first post, so I apologize if I've overlooked something important.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 03 Apr 2012 at 3:08am
Rather than filtering the records by the date range you could display all the records (so all facilities are always in the dataset) Then apply a conditional count based on the date range instead.
 
You'd still have a parameter for the date range, and a formula as follows;
 
if {table.datefield} in {?daterangeparam}
then 1
else 0
 
Then perform your summary (sum rather than count) on that field.
 
Regards,
Ryan.
 
[edit]
If there's too many records to not filter by date the only other option would be a SQL command to fetch your data, If you'd rather do it that way provide names of the all the tables and fields you're currently using and I'll write you a query which should fetch what you need.


Edited by rkrowland - 03 Apr 2012 at 3:15am
IP IP Logged
omerso
Newbie
Newbie


Joined: 03 Apr 2012
Online Status: Offline
Posts: 3
Quote omerso Replybullet Posted: 04 Apr 2012 at 2:55am
If the second option is available, here's the info below. Thanks for your help.
Tables:
Facilities
Facility_Types
Trips
 
Fields:
Facilities.name (String)
Facilities.faddr (String)
Facilities.fstate (String)
Facilities.fzip (String)
Facilities.fphone (String)
Facilities.faxphone (String)
Facility_Types.descr (String)
 
The current selector for the number of visits to a facility is Count of Trips.RunNumber (Number). It's this record value of 0 that causes the facilities to show or not.
 
Let me know if you need more/different information. I've only worked with Crystal for a few and haven't had any training, but I'm starting to get the hang of it. Thanks again.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 04 Apr 2012 at 3:03am
How are the tables joined and which field do you use to base your date criteria on?
 
Regards,
Ryan.
IP IP Logged
omerso
Newbie
Newbie


Joined: 03 Apr 2012
Online Status: Offline
Posts: 3
Quote omerso Replybullet Posted: 04 Apr 2012 at 3:11am
Tables are linked as follows:
 
Facilites.ftype = Facility_Types.code
facilities.code = Trips.ofac
 
Date is determined by Trips.tdate.
IP IP Logged
rkrowland
Senior Member
Senior Member
Avatar

Joined: 20 Dec 2011
Location: England
Online Status: Offline
Posts: 259
Quote rkrowland Replybullet Posted: 04 Apr 2012 at 3:23am
Hi, I've done this for you quickly - when selecting the data source for your report instead of adding tables, click the "Add Command" button.
 
Paste the following code into the dialog box, you will also need to create a startdate and an enddate parameter (datatype date) for the date lookups.
 
SELECT
f.name,
f.faddr,
f.fstate,
f.fzip,
f.fphone,
f.faxphone,
ft.descr,
f.code,
isnull(x.count,0)
FROM Facilities f
JOIN Facility_Types ft
on f.ftype = ft.code
LEFT JOIN (
SELECT
f.code,
count(t.runnumber) [count]
from facilities f
JOIN trips t
ON f.code = t.ofac
AND t.tdate >= {?startdate}
AND t.tdate <= {?enddate}
GROUP BY
f.code) x
on f.code = x.code
 
This will return an entire list of all facilities in your database and then perform the count of how many trips were made to each (showing 0 for facilities where no trips were made.
 
Let me know how you get on.
 
Regards,
Ryan.


Edited by rkrowland - 04 Apr 2012 at 3:26am
IP IP Logged
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