How to pass a calculated total on a sub report to a main report

[BradW]

Good afternoon,

I am trying to pass a total from a sub report back to the main report to use it in the selection formula for the main report. Is it possible to do something like this?

 

[synapsevampire]

Not in the record selection formula of the main, the subreport fires AFTER the main report has performed it's select.

You might use it to suppress rows back in the main report if you fire the sub in the report header.

Anyway, what you seek is a shared variable to pass values between Main and Subreports, as in a formula containing:

shared numbervar ShrAmount := {subtable.value}

Then after the sub fires you can use this:

shared numbervar ShrAmount

in the main report to obtain it's value.

I think that your subreport might be better served as the main report, and link the main to the subreport based on a field, or use shared variables to suppress rows.

-k

 

[BradW]

Thanks, I have spent too much time staring at reports today. When I wrote it, I was saying to myself, that won't work, but I CAN use it to do group selection can't I? That would be perfect.

Thanks for your help and for being so quick today!

 

[synapsevampire]

Probably...

Perhaps if you describe what you are trying to do with example data and expected output someone would be able to assist you.

-k

 

[BradW]

Sure,

Master Table contains records on incidents that have happened, each incident has one record

Child Table contains records that contain activity relating to the incidents, each incident will have from 0 to an unlimited number of activity records in this table

Relationship is, of course 1 to many from master to child

Join is left outer since activity records may or may not be present

In the master table, I need to select records based one specific activity which has occured, by date, typically the prior day

However, I only want incident records where a certain activity has NEVER occured which means that I have to go back and check ALL of the child records even the ones that occured before the date range for the container report. I am doing this in a sub-report and have it calculating if the record is present just fine, I just need to pass that answer from the sub-report back to the container report and then use it to eliminate those records that should not be there.

I know that this may be somewhat confusing, and I appreciate your help, please ask if you have questions.

 

[synapsevampire]

You may not need a subreport for this.

There are different approaches you might take, here's an example that might get you there:

In the Group Selection Formula
Sum ({@condExists}, {Customer.Customer Name})=0

@condExists formula
if {activity.condition}) = "Ignore these groups" then
1
else
0

Hopefully this demonstrates how you can filter rows if a condition exists, without the overhead and complications of a subreport.

If this doesn't resolve for you, please post example data and expected output, as in:

Incident Table
ID Name
001 Burned the Wonder bread Velveeta pizza casserole
002 Stained my new moo-moo with melted bon-bons

Activity table
IncidentID condition date
001 1 10/13/98
002 2 10/13/98

And then state that you want to eliminate any incidents with a condition 2. Makes it very easy to understand and discuss.

-k

 

[BradW]

Thanks for the help, I think I got it now.

 

[lbass]

In case you don't quite have it yet, this is how I'd approach it. Let's assume that you want to select only incidents with activity A on a certain date, but only if the incident has no activity B on any date.

1-Create a date parameter {?date}

2-Record select: {activity.type} in ["A","B"] and
{activity.date} <= {?date}

3-Group on {Incident.ID}

3-Create a formula {@inAnotinB}:

if {activity.type} = &quot;A&quot; and
{activity.date} = {?Date} then 1000 else
if {activity.type} = &quot;B&quot; then 1 else 0

You could substitute (currentdate -1) for {?Date} if you always want yesterday's data.

4-Add the following as a group select statement:

remainder(sum({@inAnotinB},{incident.ID}),1000) = 0 and
sum({@inAnotinB},{incident.ID}) > 0

-LB