Totals Groups and Sub Reports

B

Bryan Hughes

Hello,

I have a report that has Case Manager Group, and 5 different sub reports
showing cases assigned to the case manager in different programs. Each sub
report has a count total. I need to get the total from each sub report and
then total that to show the total cases for each case manager. How can I do
this?

-Thanks for the help

Bryan
 
A

Al Camp

Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each subreport you had a
subform footer totals called Case1Totals and Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] + NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.
 
B

Bryan Hughes

Al,

Thanks, I figured this out by creating a txtCount_Program for each program,
setting the RunningSum Property to OverGroup, and doing the following in
Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)

Then I have a txtCM_Count in the group footer that adds the totals from each
program txtCount_Program. This shows the totals for each case manager.

Now I need to get all the totals for the Case Mangers to show the totals for
everyone in the report footer.

How should I do this?


Al Camp said:
Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each
subreport you had a subform footer totals called Case1Totals and
Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] +
NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Hello,

I have a report that has Case Manager Group, and 5 different sub reports
showing cases assigned to the case manager in different programs. Each
sub report has a count total. I need to get the total from each sub
report and then total that to show the total cases for each case manager.
How can I do this?

-Thanks for the help

Bryan
Click to expand...
Click to expand...
 
A

Al Camp

Bryan,
Well, I think you're just mimicking the NZ function via the HasNoData property.
Different strokes...
I'd still use
=NZ(rsubPCV_Open_Case_Files.Report!txtCount)

So, you now show the correct totals individual totals of the 5 subs on the main
report...
Try summing those 5 calculated fields in the Manager footer, adding the 5 calculations
for each total.
ex.
=NZ(rsubPCV_1_Open_Case_Files.Report!txtCount)
+NZ(rsubPCV_2_Open_Case_Files.Report!txtCount) + ... etc for all 5
and then use a running total against that group footer field (OverAll) in the report
footer

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Al,

Thanks, I figured this out by creating a txtCount_Program for each program, setting the
RunningSum Property to OverGroup, and doing the following in Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)

Then I have a txtCM_Count in the group footer that adds the totals from each program
txtCount_Program. This shows the totals for each case manager.

Now I need to get all the totals for the Case Mangers to show the totals for everyone in
the report footer.

How should I do this?


Al Camp said:
Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each subreport you had
a subform footer totals called Case1Totals and Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] + NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Hello,

I have a report that has Case Manager Group, and 5 different sub reports showing cases
assigned to the case manager in different programs. Each sub report has a count
total. I need to get the total from each sub report and then total that to show the
total cases for each case manager. How can I do this?

-Thanks for the help

Bryan
Click to expand...
Click to expand...
Click to expand...
 
B

Bryan Hughes

Al,

I did this and now it just shows "#Error".

Bryan


Al Camp said:
Bryan,
Well, I think you're just mimicking the NZ function via the HasNoData
property.
Different strokes...
I'd still use
=NZ(rsubPCV_Open_Case_Files.Report!txtCount)

So, you now show the correct totals individual totals of the 5 subs on
the main report...
Try summing those 5 calculated fields in the Manager footer, adding the
5 calculations for each total.
ex.
=NZ(rsubPCV_1_Open_Case_Files.Report!txtCount)
+NZ(rsubPCV_2_Open_Case_Files.Report!txtCount) + ... etc for all 5
and then use a running total against that group footer field (OverAll) in
the report footer

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Al,

Thanks, I figured this out by creating a txtCount_Program for each
program, setting the RunningSum Property to OverGroup, and doing the
following in Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)

Then I have a txtCM_Count in the group footer that adds the totals from
each program txtCount_Program. This shows the totals for each case
manager.

Now I need to get all the totals for the Case Mangers to show the totals
for everyone in the report footer.

How should I do this?


Al Camp said:
Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each
subreport you had a subform footer totals called Case1Totals and
Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] +
NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello,

I have a report that has Case Manager Group, and 5 different sub
reports showing cases assigned to the case manager in different
programs. Each sub report has a count total. I need to get the total
from each sub report and then total that to show the total cases for
each case manager. How can I do this?

-Thanks for the help

Bryan
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

NZ works on Null values

If there are no records returned, then there isn't any record to have a null
value in the field (control). Basically there is no object in the current
context.


Al Camp said:
Bryan,
Well, I think you're just mimicking the NZ function via the HasNoData
property.
Different strokes...
I'd still use
=NZ(rsubPCV_Open_Case_Files.Report!txtCount)

So, you now show the correct totals individual totals of the 5 subs on
the main report...
Try summing those 5 calculated fields in the Manager footer, adding the
5 calculations for each total.
ex.
=NZ(rsubPCV_1_Open_Case_Files.Report!txtCount)
+NZ(rsubPCV_2_Open_Case_Files.Report!txtCount) + ... etc for all 5
and then use a running total against that group footer field (OverAll) in
the report footer

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Al,

Thanks, I figured this out by creating a txtCount_Program for each
program, setting the RunningSum Property to OverGroup, and doing the
following in Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)

Then I have a txtCM_Count in the group footer that adds the totals from
each program txtCount_Program. This shows the totals for each case
manager.

Now I need to get all the totals for the Case Mangers to show the totals
for everyone in the report footer.

How should I do this?


Al Camp said:
Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each
subreport you had a subform footer totals called Case1Totals and
Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] +
NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello,

I have a report that has Case Manager Group, and 5 different sub
reports showing cases assigned to the case manager in different
programs. Each sub report has a count total. I need to get the total
from each sub report and then total that to show the total cases for
each case manager. How can I do this?

-Thanks for the help

Bryan
Click to expand...
Click to expand...
Click to expand...
 
B

Bryan Hughes

John,

I changed it to look like this:

= IIF( [rsubCAC_Open_Case_Files].Report.HasData,
[rsubCAC_Open_Case_Files].Report![txtCount],0)+
IIF( [rsubCVAC_Open_Case_Files].Report.HasData,
[rsubCVAC_Open_Case_Files].Report![txtCount],0)+
IIf( [rsubFCAP_Open_Case_Files].Report.HasData,
[rsubFCAP_Open_Case_Files].Report![txtCount],0)+
IIf([rsubFST_Open_Case_Files].Report.HasData,
[rsubFST_Open_Case_Files].Report![txtCount],0)+
IIf( [rsubGT_Open_Case_Files].Report.HasData,
[rsubGT_Open_Case_Files].Report![txtCount],0)+
IIF( [rsubPCIT_Open_Case_Files].Report.HasData,
[rsubPCIT_Open_Case_Files].Report![txtCount],0)+
IIf( [rsubPCV_Open_Case_Files].Report.HasData,
[rsubPCV_Open_Case_Files].Report![txtCount],0)

Now it just shows 0 as the total.

Bryan



John Spencer said:
NZ works on Null values

If there are no records returned, then there isn't any record to have a
null value in the field (control). Basically there is no object in the
current context.


Al Camp said:
Bryan,
Well, I think you're just mimicking the NZ function via the HasNoData
property.
Different strokes...
I'd still use
=NZ(rsubPCV_Open_Case_Files.Report!txtCount)

So, you now show the correct totals individual totals of the 5 subs on
the main report...
Try summing those 5 calculated fields in the Manager footer, adding the
5 calculations for each total.
ex.
=NZ(rsubPCV_1_Open_Case_Files.Report!txtCount)
+NZ(rsubPCV_2_Open_Case_Files.Report!txtCount) + ... etc for all 5
and then use a running total against that group footer field (OverAll) in
the report footer

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Al,

Thanks, I figured this out by creating a txtCount_Program for each
program, setting the RunningSum Property to OverGroup, and doing the
following in Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)

Then I have a txtCM_Count in the group footer that adds the totals from
each program txtCount_Program. This shows the totals for each case
manager.

Now I need to get all the totals for the Case Mangers to show the totals
for everyone in the report footer.

How should I do this?


Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each
subreport you had a subform footer totals called Case1Totals and
Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] +
NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello,

I have a report that has Case Manager Group, and 5 different sub
reports showing cases assigned to the case manager in different
programs. Each sub report has a count total. I need to get the total
from each sub report and then total that to show the total cases for
each case manager. How can I do this?

-Thanks for the help

Bryan
Click to expand...
Click to expand...
Click to expand...
 
B

Bryan Hughes

John,

I got it to work.

I made anoter textbox txtTotal1 in the group footer. Have the Running Sum
to Over All and made the source
=[txtCAC_Count]+[txtCVAC_Count]+[txtFCAP_Count]+[txtFST_Count]+[txtGT_Count]+[txtPCIT_Count]+[txtPCV_Count]

Then the source for the report total is texTotal1.

I don't know if there is a better way but this works.

Bryan


John Spencer said:
NZ works on Null values

If there are no records returned, then there isn't any record to have a
null value in the field (control). Basically there is no object in the
current context.


Al Camp said:
Bryan,
Well, I think you're just mimicking the NZ function via the HasNoData
property.
Different strokes...
I'd still use
=NZ(rsubPCV_Open_Case_Files.Report!txtCount)

So, you now show the correct totals individual totals of the 5 subs on
the main report...
Try summing those 5 calculated fields in the Manager footer, adding the
5 calculations for each total.
ex.
=NZ(rsubPCV_1_Open_Case_Files.Report!txtCount)
+NZ(rsubPCV_2_Open_Case_Files.Report!txtCount) + ... etc for all 5
and then use a running total against that group footer field (OverAll) in
the report footer

Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Bryan Hughes said:
Al,

Thanks, I figured this out by creating a txtCount_Program for each
program, setting the RunningSum Property to OverGroup, and doing the
following in Control Source.
=IIf(rsubPCV_Open_Case_Files.Report.HasData,rsubPCV_Open_Case_Files.Report.txtCount,0)

Then I have a txtCM_Count in the group footer that adds the totals from
each program txtCount_Program. This shows the totals for each case
manager.

Now I need to get all the totals for the Case Mangers to show the totals
for everyone in the report footer.

How should I do this?


Bryan,
Say you just had 2 subreports... rptCase1 and rptCase2, and on each
subreport you had a subform footer totals called Case1Totals and
Case2Totals respectively.

An unbound calculated field on the main report with... (all one line)
= NZ([rptCase1.Report![Case1Totals] +
NZ(rptCase2.Report![Case2Totals])
should total up the values from both subs.
NZ() substitutes 0 for any Null Totals.

--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

Hello,

I have a report that has Case Manager Group, and 5 different sub
reports showing cases assigned to the case manager in different
programs. Each sub report has a count total. I need to get the total
from each sub report and then total that to show the total cases for
each case manager. How can I do this?

-Thanks for the help

Bryan
Click to expand...
Click to expand...
Click to expand...
 
Top