Sub Report Summary values in Main Report

[Room58]

I have a sub report which lists sets of timesheet entries. In the report, I group the entries by Entry Type. I then put the sub report in the main report which contains a list of all the members of staff. My problem is that on the main report I wish to take certain values (group summary values) and run some simple addition/subtraction calculations.

How can I access the summary values on the sub report to use in the main report? I'm guessing I need to use a formula and variable but I can't find a way to pick specific values out of the sub report and pass them to the main report.

Can anyone help?

 

[synapsevampire]

They are called share variables in Crystal.

An example would be:

whileprintingrecords;
shared numbervar SubValue:=sum({table.field})

Thn in the main report AFTER the subreport has run you can use it's value:

whileprintingrecords;
shared numbervar SubValue

You might need to reset the variable in the group header prior to running the subreport if the subreport is in a group section and you want individual numbers.

Hard to say, you don't state where the subreport is, what version of Crystal, basically nothing technical, just a description.

Successful posters take the time to describe their environment and requirements rather than rtying to explain them:

Crystal version
Database/connectivity used
Example dtaa
Expected output

Keep in mind that subreports are often a bad idea (especially at the group level) and the result of a poor understanding of databases and Crystal, so try to avoid them.

-k

 

[Room58]

Apologies

I'm running Crystal XI with SQL2000 (OLEDB (ADO))

I've got a summary in the sub report as follows:

Overtime: 20hours
SleepIn: 10hours
Sick: 5 hours
SleeInSick: 2hours

In my main report I need to get the summary values from the sub report and display

Net OverTime: Overtime - Sick (15 hours in this example)
Net Sleep: SleepIn - SleepInSick (8 hours in this example)

 

[synapsevampire]

Well again you don't display how or where in the subreport you are getting these values, rather showing an example of what they might look like in the subreports output, not very meaningful.

I asked wher the subreport was, you ignored, I asked for example data, you ignorede, yoiu shouldn't expect reasonable answers when you are unwilling to take the time to supply technical information.

Anyway, whereever you are building these values in the subreports, replace them with formulas using shared variables. Can't help much beyond that since I have too little to go on.

Keep in mind that you'd be better eserved using a stored proceudre, Views or a subqeury to replace the values being returned by the subreport.

Crystal often suggests using subreports out of laziness or to ensure that people get tied to their product, but it's inefficient and doesn't promote reusability nor ease of maintenance.

-k

 

[Room58]

Right then, let me try again. Thanks for your help so far.

My sub report has the following tables:

EntryLog
--------
EntryID (1, 2, 89, 100)
StartTime (Decimal such as 8.5 for 8:30)
EndTime (ditto)
UserID (4, 5, 6, 7, 8)
NominalID (56, 67, 68, 69)

Nominals
--------
NominalID (56, 67, 68, 69, 80, 90)
NominalName (string)

Users
-----
UserID
DisplayName

EntryLog is Inner Joined on UserID and NominalID

I also have another Users table and a Nominals table (both aliased) so that I can display all the of the Nominals for all of the users.

My sub report shows:

Display Name (grouped by UserID)
NominalName (grouped by NominalID)
EntryLog entries

I am selecting the following:

{TT_EntryLog.UserID}={TT_Users.UserID} and
{TT_EntryLog.NominalID}={TT_Nominals_1.NominalID}

although this doesn't appear to return all Nominals. I am also changing the sign of some of the summary information depending on the NominalID.

I tried to do this using only left joins but couldn't get this to work. I am unsure if this is best practice.

My main report I have the following:

Users
-----
UserID
DisplayName

Nominals
--------
NominalID
NominalName

I wish to put the sub report in for each staff member (including those without any entries in the sub report) displaying all Nominals.

I then wish to display a summary of certain Nominal codes as shown in my previous post.

Both the sub report and the master report on local reports on my machine. I terms of providing sample data:

Sub report
Pete Jones
Day Rate 14.50
Day Rate 5.50
Day Rate 9.00
Day Rate 0.00
Holiday 0.00
Holiday 0.00
Holiday 0.00
Other 1.50
Other 0.50
Other 0.50
Other 0.00
Other 0.50
Overtime 3.50
Overtime 3.50
Overtime 0.00
Overtime 0.00
Sickness Unpaid 3.50
Sickness Unpaid 3.50

Sub report Summary (detail suppressed)

Pete Jones
Day Rate 14.50
Holiday 0.00
Other 1.50
Overtime 3.50
Sickness Unpaid 3.50
Sleep In 63.50
Sleep In Absence 0.00
Training Overtime 3.50


Is that any better?

 

[Room58]

Can anyone help on this?

 

[Room58]

Here is the forumla I'm using in my sub report to populate the shared variable. I intend to have a shared variable for each summary field i'm interested in.

SubSummaryHoliday (formula)
---------------------------
WhilePrintingRecords;
if {TT_EntryLog.NominalID} = 88 then
Shared numberVar SummaryHoliday := Sum({@Hours})
else
Shared numberVar SummaryHoliday := 0

But this totals all of the fields in the report. How can I get the total for just one group total?

 

[lbass]

If you use running totals in the subreport (placed in the subreport report footer), they will be available at the same level for calculations in the subreport or in the main report. For example, select sum of {Table.hours}, evaluate using a formula:

{table.hourstype} = "Days" //change for each hours type

Reset never (since the subreport is linked by your group fields, you don't need to reset per group).

Then your shared variable formula might look like:

whileprintingrecords;
shared numbervar dayhrs := {#dayhrs};
shared numbervar overtime := {#OT}; //etc.

The calculations would need to be done in a section below the subreport (GH_b, for example) if you do them in the main report.

You should also have a reset formula in your main report for the shared variables, like:

whileprintingrecords;
shared numbervar dayhrs := 0;
shared numbervar overtime := 0;

This should be placed in an employee group section before the subreport executes or after the shared variabe calculations are done.

-LB

 

[Room58]

Thanks lbass! I think I have it sorted now!