Grand Totals Subreports in Main Reports 1

[Garyjr123]

Main Report set up:

sub=subreport

GH1aSchedulerName NewAppts(sub) Changed(sub) Canceled(sub)
GH1bsuppressed) @NewAmtSum @ChangeAmtSum @CanceledAmtSum
Dsuppressed)
GF1suppressed)
RF: Grand TotalNewGrandTotal @ChangeGrandTotal @CanceledGrandTot

Subreport formulas:

NewAppts
@NewApptAmtSum: whileprintingrecords;
shared numbervar amt := count({schedlog.appt_id});

Changed
@ChangedApptAmtSum: whileprintingrecords;
shared numbervar amt1 := count({schedlog.appt_id});

Canceled
whileprintingrecords;
shared numbervar amt2 := count({schedlog.appt_id});

Main Report Formulas:

@NewAmtSum: whileprintingrecords;
shared numbervar amt;
numbervar sumamt := sumamt + amt

@ChangedAmtSum: whileprintingrecords;
shared numbervar amt1;
numbervar sumamt1 := sumamt1 + amt1

@CanceledAmtSum: whileprintingrecords;
shared numbervar amt2;
numbervar sumamt2 := sumamt2 + amt2

@NewGrandTotal: whileprintingrecords;
numbervar sumamt;

@ChangeGrandTotal: whileprintingrecords;
numbervar sumamt1;

@CanceledGrandTot: whileprintingrecords;
numbervar sumamt2;

Now, what I want to do is get a grand total for each scheduler (NewAppts + ChangedAppts + CanceledAppts = SchedulerName total) and a grand total of the grand totals (@NewGrandTotal + @ChangeGrandTotal + @CanceledGrandTot = Report grand total). How do I accomplish this?

Gary

 

[lbass]

The grand total of the grand total would just be the total of your current grand totals:

@GrandGrandTotal:
whileprintingrecords;
numbervar sumamt;
numbervar sumamt1;
numbervar sumamt2;
sumamt+sumamt1+sumamt2;

Assuming the subs are linked to the scheduler field, you should be able to simply use the following, placed in GHb or GHc or GF:

whileprintingrecords;
shared numbervar amt;
shared numbervar amt1;
shared numbervar amt2;
amt+amt1+amt2

But you should probably create a reset for GF1b in case there can be nulls from the sub:

whileprintingrecords;
shared numbervar amt := 0;
shared numbervar amt1 := 0;
shared numbervar amt2 := 0;

-LB

 

[Garyjr123]

LB,

For some reason this report will work 95% of the time pulling the correct results in but when I put in the search parameter of 7/16/2011 to 7/16/2011 the report doesn't populate correctly. If I put in the search parameter of 7/15/2011 to 7/16/2011, the results return correctly. I am not sure why this particular date is causing an issue. Any ideas?

Gary

 

[lbass]

Are you applying a date parameter to a datetime field? It might work better if you used a formula like this:

{table.datetime} >= {?Start} and
{table.datetime} < {?End}+1

Then if you use the same value for start and end, it will automatically read 7/16/2011 00:00:00 to 7/17/2011 00:00:00.

-LB

 

[Garyjr123]

Morning LB,

I tried this formula for the parameters but it still is showing the same problem for 7/16-7/16 or 7/16-7/17 but the data populates correctly for 7/15-7/16. There is something about the weekend the report does not like. I have the main report parameters filtering down to the subreport's parameter fields ({?Pm-StartDate} & {?Pm-StopDate} to {?StartDate} & {?StopDate}) and the {?Pm-schedlog.user_name} to {schedlog.user_name}. Do you think it could be an LOJ problem?

Gary

 

[Garyjr123]

LB,

I spoke to soon, I am only using one table for the main report and subreports so there is no linking of tables. Ideas?

Gary

 

[lbass]

Please show the content for your subreport's record selection formula (report->selection formula->record).

-LB

 

[Garyjr123]

New Appt subreport:

{schedlog.user_name} = {?Pm-schedlog.user_name} and
{schedlog.audittype_id} = 2 and
{schedlog.start_datetime} in {?StartDate} to {?StopDate}

Changed Appts subreport:

{schedlog.user_name} = {?Pm-schedlog.user_name} and
{schedlog.start_datetime} in {?StartDate} to {?Stopdate} and
{schedlog.audittype_id} in [1, 10]

Canceled Appts subreport:

{schedlog.audittype_id} = 3 and
{schedlog.user_name} = {?Pm-schedlog.user_name} and
{schedlog.start_datetime} in {?StartDate} to {?StopDate}

Hope this helps.

 

[lbass]

So you are using the dropdown in the linking screen to select the main report {?Start}(not the default {?pm-?Start}) to link it to the subreport {?Start} for each subreport, correct?

I would still change the formulas to this syntax:

{schedlog.user_name} = {?Pm-schedlog.user_name} and
{schedlog.audittype_id} = 2 and
{schedlog.start_datetime} >= {?StartDate} and
{schedlog.start_datetime} < {?StopDate}+1

I'm assuming these ARE date parameters, not datetime parameters.

-LB

 

[Garyjr123]

LB,

I completely changed the way this report prints. Here are the SQL queries for the main and subreports:

Main Report:
SELECT "schedlog"."user_name", "schedlog"."audittype_id", "schedlog"."start_datetime", "appt"."resunit_id"
FROM "phsprod"."dbo"."schedlog" "schedlog" INNER JOIN "phsprod"."dbo"."appt" "appt" ON "schedlog"."appt_id"="appt"."appt_id"
WHERE ("schedlog"."user_name"='dbrow130' OR "schedlog"."user_name"='jkelle37' OR "schedlog"."user_name"='tneal8') AND ("schedlog"."audittype_id"=1 OR "schedlog"."audittype_id"=2 OR "schedlog"."audittype_id"=3 OR "schedlog"."audittype_id"=10) AND ("schedlog"."start_datetime">={ts '2011-07-25 00:00:00'} AND "schedlog"."start_datetime"<{ts '2011-08-03 00:00:00'}) AND ("appt"."resunit_id"=5 OR "appt"."resunit_id"=6 OR "appt"."resunit_id"=7)

New Appt Subreport:

SELECT "schedlog"."user_name", "schedlog"."audittype_id", "schedlog"."appt_id", "schedlog"."start_datetime", "appt"."resunit_id"
FROM "phsprod"."dbo"."schedlog" "schedlog" INNER JOIN "phsprod"."dbo"."appt" "appt" ON "schedlog"."appt_id"="appt"."appt_id"
WHERE "schedlog"."audittype_id"=2 AND "schedlog"."user_name"='dbrow130' AND ("appt"."resunit_id"=5 OR "appt"."resunit_id"=6 OR "appt"."resunit_id"=7)

Changed Appt Subreport:

SELECT "schedlog"."user_name", "schedlog"."audittype_id", "schedlog"."appt_id", "schedlog"."start_datetime", "appt"."resunit_id"
FROM "phsprod"."dbo"."schedlog" "schedlog" INNER JOIN "phsprod"."dbo"."appt" "appt" ON "schedlog"."appt_id"="appt"."appt_id"
WHERE "schedlog"."user_name"='dbrow130' AND ("appt"."resunit_id"=5 OR "appt"."resunit_id"=6 OR "appt"."resunit_id"=7) AND ("schedlog"."audittype_id"=1 OR "schedlog"."audittype_id"=10)

Canceled Appt Subreport:

SELECT "schedlog"."user_name", "schedlog"."audittype_id", "schedlog"."appt_id", "schedlog"."start_datetime"
FROM "phsprod"."dbo"."schedlog" "schedlog"
WHERE "schedlog"."user_name"='tneal8' AND "schedlog"."audittype_id"=3

What I would like to do is have a total for the new appt, changed appt, and canceled appt (subreports) for each day for each scheduler.

New Appt (as well as changed appts and canceled appts subreports)

Scheduler 1
19
Scheduler 2
21
Scheduler 3
10

scheduler 1 + scheduler 2 + scheduler 3 = 50 (the days total)

Also, I would like to total across subreports: total of new appts + total of changed appts + total of canceled appts = total for each scheduler for the day.

I'm currently using the other formulas you gave me:

NewAppts
@NewApptAmtSum: whileprintingrecords;
shared numbervar amt := count({schedlog.appt_id});

Changed
@ChangedApptAmtSum: whileprintingrecords;
shared numbervar amt1 := count({schedlog.appt_id});

Canceledwhileprintingrecords;
shared numbervar amt2 := count({schedlog.appt_id});

Main Report Formulas:
@NewAmtSum: whileprintingrecords;
shared numbervar amt;numbervar sumamt := sumamt + amt

@ChangedAmtSum: whileprintingrecords;
shared numbervar amt1;
numbervar sumamt1 := sumamt1 + amt1

@CanceledAmtSum: whileprintingrecords;
shared numbervar amt2;
numbervar sumamt2 := sumamt2 + amt2

@NewGrandTotal: whileprintingrecords;
numbervar sumamt;

@ChangeGrandTotal: whileprintingrecords;
numbervar sumamt1;

@CanceledGrandTot: whileprintingrecords;
numbervar sumamt2;

Let me know if you need more info.

 

[lbass]

I don't know what the current problem is.

-LB

 

[Garyjr123]

LB,

How do I total the schedulers numbers in the subreports into the main report so:

Main Report

GF1 schedlog.start_datetime
GF2a schedlog.user_name
GF2b
Subreport 1 Subreport 2 Subreport 3

Scheduler 1 Scheduler 1 Scheduler 1
50 30 10
Scheduler 2 Scheduler 2 Scheduler 2
30 20 20
Scheduler 3 Scheduler 3 Scheduler 3
20 10 30

Main Report (I want the total for each day in my date range search from the subreport in the main report)

GF1
20+30+50 = 100 30+20+10 = 60 10+20+30 = 60

 

[lbass]

So for each subreport you have some shared variable that you are displaying in GF2b of the main report, correct? I don't know the name of it (not wanting to sort through all the info above),so let's say the variable for sub1 is called "sub1". In your display formula in the main report, change the formula to:

whileprintingrecords;
shared numbervar sub1;
numbervar sumsub1 := sumsub1 + sub1;
sub1; //add this so the display is just for the current value

Then in the GF1, add this formula:

whileprintingrecords;
numbervar sumsub1;

Repeat these steps for the other two subs.

-LB

 

[Garyjr123]

LB,

This is doing a running total for me which I want and have in place in the RF but what I want is each day to show a total so if I do a date search I want to see this:

8/1/11 - 8/2/11

Main Report

GF1 8/1/11
GF2a Scheduler 1
GF2b 10 (subreport)
GF2a Scheduler 2
GF2b 20 (subreport)
GF2a Scheduler 3
30 (subreport)

GF2 Suppressed
GF3 sched 1 + sched 2 + sched 3 = total for 8/1/11

GF1 8/2/11
GF2a Scheduler 1
GF2b 20 (subreport)
GF2a Scheduler 2
GF2b 30 (subreport)
GF2a Scheduler 3
40 (subreport)

GF2 Suppressed
GF3 sched 1 + sched 2 + sched 3 = total for 8/2/11

RFa 8/1/11-8/2/11 running total for each subreport
RFb Grand total

 

[lbass]

Add a reset formula in the group header #1:

whileprintingrecords;
numbervar sumsub1 := 0;
numbervar sumsub2 := 0;
numbervar sumsub3 := 0;

Please take a look at how these variables are used--basically reset, accumulation, display--for future reference.

-LB

 

[Garyjr123]

LB,

Everything is working great, thank you. One more question: How do I add rows across the 3 subreports for each scheduler for each day?

Main Report
GF1 8/1/11
GF2a schedlog.user_name
GF2b
Subreport 1 Subreport 2 Subreport3 Main Report

Scheduler 1 Scheduler 1 Scheduler 1
50 30 10 Total 90
Scheduler 2 Scheduler 2 Scheduler 2
30 20 20 Total 70
Scheduler 3 Scheduler 3 Scheduler 3
20 10 30 Total 60

 

[lbass]

In the obvious way--add the shared variables together:

whileprintingrecords;
shared numbervar sumsub1;
shared numbervar sumsub2;
shared numbervar sumsub3;
sumsub1+sumsub2+sumsub3

-LB

 

[Garyjr123]

LB,

I tried the obvious way first (before you ever responded)in GH2b:

whileprintingrecords;
numbervar sumamt6;
numbervar sumamt7;
numbervar sumamt8;
sumamt6 + sumamt7 + sumamt8;

GH2e (Main Report) has for each subreport (each number increasing for each subreport):

whileprintingrecords;
shared numbervar amt6;
numbervar sumamt6 := sumamt6 + amt6

whileprintingrecords;
shared numbervar amt7;
numbervar sumamt7 := sumamt7 + amt7

whileprintingrecords;
shared numbervar amt8;
numbervar sumamt8 := sumamt8 + amt8

Then tried to add a reset formula in GH1b:
(@NewChangedCanceledApptSumReset)

whileprintingrecords;
numbervar sumamt6 := 0;
numbervar sumamt7 := 0;
numbervar sumamt8 := 0;

The results for doing this looked like this

GH1a 8/1/11
GH1b suppressed (@NewChangedCanceledApptSumReset)
GH2a Sched Name
GH2b Sub 1 Sub 2 Sub 3 Main Report
Sched 1 Sched 1 Sched 1
10 20 30 0.00
Sched 2 Sched 2 Sched 2
40 50 60 60.00
Sched 3 Sched 3 Sched 3
70 80 90 210.00

And I want them to look like this:

GH1a 8/1/11
GH1b suppressed (@NewChangedCanceledApptSumReset)
GH2a Sched Name
GH2b Sub 1 Sub 2 Sub 3 Main Report
Sched 1 Sched 1 Sched 1
10 20 30 60
Sched 2 Sched 2 Sched 2
40 50 60 150
Sched 3 Sched 3 Sched 3
70 80 90 240

Basically add across the 3 subreports and not give me a running total.

 

[lbass]

It's always a good idea to explain what you have tried and the resulting problem. Without that, it appeared to me you were just waiting for the answer without trying on your own. Sorry if I misinterpreted.

Create a separate variable for the sum of the three variables then and reset it in GH2b. I'm not following your variable names, so substitute the correct ones:

whileprintingrecords;
shared numbervar sumsub1;
shared numbervar sumsub2;
shared numbervar sumsub3;
numbervar sumgrp2 := sumsub1+sumsub2+sumsub3;

Then add a reset for sumgrp2 to GH2:

whileprintingrecords;
numbervar sumgrp2 := 0;

-LB

 

[Garyjr123]

LB,

No problem, I fear sometimes that I might get two verbose with my explanations. I'm definitely trying to learn from everything you show me. I actually save all this to a file folder at work then go back and read up on it more in my Crystal Reports XI book I bought then play.

Ok, I tried this and got the same results. Maybe the formula in the subreports is wrong. Each one is set up similarly to what you see below:

Subreport 1, 2, 3
GH1 suppressed start_datetime
GH2 suppressed user_name
GH3 suppressed appt_id
D suppressed
GF3a suppressed (formula)
GF3b suppressed (formula)
GF3c suppressed (formula)
GF2 suppressed
GF1 suppressed
RF [count of schedlog.appt_id]

formula in GF3c is
whileprintingrecords;
shared numbervar amt6:= count({schedlog.appt_id});//again all 3 subreports are set up similarly; Subreport 2&3 use amt7 and amt8

Then in the Main report I added the formulas from the last post into the GH you specified.

Main Report

GH2a user_name with formulas:
whileprintingrecords;
numbervar sumamt6;
numbervar sumamt7;
numbervar sumamt8;
numbervar sumgrp2 := sumamt6+sumamt7+sumamt8;

GH2a (suppressed field) Reset Formula:
whileprintingrecords;
numbervar sumgrp2 := 0;

GH2e suppressed with formula:

whileprintingrecords;
shared numbervar amt6;
numbervar sumamt6 := sumamt6 + amt6//one for each subreport; sub 1 & 2 uses amt7 and amt8.

Does this look correct to you?