Select all records for one parameter for summary, but exclude others

[beacon5]

Hi everybody,

I'm using Crystal Reports 8.5 against an Access database.

My issue is that I'm trying to find a summary for all records in a date range and then compare that number with a second (group) parameter. The summary is the only part that I'm having trouble with.

My report has a {?DateRange} parameter and a {?Specialist} parameter. When the user enters the date range, I want to capture all the number of records that occurred during the range and store it. Then, I want the report select only those records in the date range for the specialist chosen.

Here's an example:
Date Range - 05/01/09 to 05/25/09
Specialist - Joe Smith and Mary Moore

Total records for date range - 300
Total records for Joe Smith for date range - 25
Total records for Mary Moore for date range - 50

In the report footer, I have a few summary fields setup, but once I added the Specialist parameter, the number of records logically were reduced to just the Specialist so that it looks like this instead:
Date Range - 05/01/09 to 05/22/09
Specialist - Joe Smith and Mary Moore

Total records for date range - 75
Total records for Joe Smith for date range - 25
Total records for Mary Moore for date range - 50

As you can see, if I'm trying to compare the specialists to all the other specialists, my totals will be wrong. Is there any way, without having to suppress records, that I can loop through the records for the date range, store the total, then pass through according to the group parameter?

Thanks,
beacon

 

[Turkbear]

Hi,
One way is to add a subreport for the Specialist information and link it by the main reports date range and Specialist parameters..

Main would have your summary data and sub would have the details on the chosen specialist(s)

Or create a Sql Command that does both the Count and the specialist info - something like (not real code follows but adapt it to your database):

Select counting.TotCount,specdetail.* from
(Select count(*) TotCount from table where Date in Date1 to Date2 )counting,
(Select * from table where specialist in ?Specparameter) specdetail

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[beacon5]

If I create a subreport and pass the date range to it from the main report, how can I pass the summary values back to the main report?

Essentially, if I'm understanding correctly, I will probably have to create two detail sections, put a field to count all records for the date range in one section, and the subreport in the second detail section.

Subsequently, on the subreport, there will end up being a few summary fields based on the date range AND the specialist that I will need to pass back to the main report to compare to the total for the date range.

How would I do this?

 

[lbass]

If you limit the records in the record selection formula to those in the date range, you can write separate formuals per specialist like this:

if {table.specialist} = "Joe Smith" then 1

Then you can insert a summary (sum, NOT count) on this at the report footer level.

You could alternatively insert a crosstab in the report footer and use a formula like the following:

if {table.specialist} = {?parm} then {table.specialist} else "Others"

Add this formula as your row field, and then insert a count on a unique ID field as your summary.

-LB