Shared variables not totaling correctly on main report

[mmwdmr]

I’m working with a main report and a subreport. The main report is grouped by client account number, then Invoice Month. The details section of the main report contain the invoice details. The subreport contains all of the Credit (payments) detail.

There is already a shared variable that keeps the subreport credit detail with the appropriate Invoice month (group 2). For example, the main report lists all of the invoice information for January 2005, then the subreport prints credit detail for January 2005. It is like this for every month. The shared variable formula for this is:

@ByMonthLink
date(year({RHEADER.INVOICE_DATE}), month({RHEADER.CREATE_DATE}),01)

I placed this formula in the subreport and main report canvas and linked the main and the sub by this formula (@ByMonthLink)

Now what I want to do is total everything up. I already have accurate totals for the amount invoiced by client account number and invoice month as well as a grand total of invoices. I have been trying to pass the totals from the credit detail subreport into the main report as subtotals and a grand total. No matter what section I put the shared variable formula, it displays the total from the group (invoice month) instead of the total credits for all invoice months. My shared variable formulas are as follows:

In the subreport:

@TotalCreditsFromSub1
WhilePrintingRecords;
Shared NumberVar TotalCredits:= sum({RHEADER.TOTAL_DOLLAR})

In the main report:

@TotalCreditsFromSub1
WhilePrintingRecords;
Shared NumberVar TotalCredits

In the main report as a reset of TotalCredits:
@Reset_TotalCreditsFromSub1
WhilePrintingRecords;
Shared NumberVar TotalCredits:=0

I’m sure this all has to do with placment of all three if these formulas but no matter what combination I try, I don’t get the correct totals. After I get a total of the credits for all the months for a client, I then need to do a grand total.

I am using Crystal version 9 with a database that does not use SQL. Also, I tried to include a picture of what my reports look like but they wouldn't paste in. Any and all suggestion would be welcome!!! Thanks!

 

[synapsevampire]

Bot sure why you're using the linking formula:

"I placed this formula in the subreport and main report canvas and linked the main and the sub by this formula (@ByMonthLink)"

If anything that will filter to just the first day of a month.

You state "The shared variable formula for this is:

@ByMonthLink
date(year({RHEADER.INVOICE_DATE}), month({RHEADER.CREATE_DATE}),01)

I placed this formula in the subreport and main report canvas and linked "

That isn't a shared variable, so that confuses me as well.

Also please state specifically where the variables are located, and at what level you want the summaries returned for.

Your formulas seem OK in design/syntax.

If you want a total from the subreport, then the shared variable in the sub might look like:

@TotalCreditsFromSub1
WhilePrintingRecords;
Shared NumberVar TotalCredits:= TotalCredits+sum({RHEADER.TOTAL_DOLLAR})

A shared varaible returns one per time it's fired, it does not automatically aggregate itself.

Hopefully this helps, otherwise please post specifics about where the variables are, and what you need.

Example data and expected output tend to work better than descriptions.

-k

 

[lbass]

In the main report, change your shared variable formula to:

//{@TotalCreditsFromSub1} to be placed in the Month group footer:
WhilePrintingRecords;
Shared NumberVar TotalCredits;
Numbervar ClientTotal := ClientTotal + TotalCredits;
Numbervar GrandTotal := GrandTotal + TotalCredits;

Keep your current shared variable reset formula in the Month Group header (or footer)(either before or after the subreport has executed), but add a different reset formula for the Client Group header:

//{@resetclient}:
whileprintingrecords;
numbervar clienttotal := 0;

Then create a display formula for the Client group footer:
//{@displayclient}:
whileprintingrecords;
numbervar clienttotal;

And a display formula for the report footer:
//{@displaygrandtotal}:
whileprintingrecords;
numbervar GrandTotal;

-LB

 

[mmwdmr]

Synapsevampire - I'd like you to see the jpg's I have of the main and subreport. I captured them for the purpose of including them with my post. I've seen other posts with images of a report screenshot. Any idea on how to include an image in the post for an example?

 

[vidru]

mmwdmr:
You can use the [ignore][/ignore] tag to link to your hosted image:
[ignore][img]http://www.yourwebserver.com/yourimage.gif[/ignore]

Try to keep the images at a reasonable size. Wide images make the post get all wonky...

-dave

 

[mmwdmr]

LB - tried your solution but the shared credit variables are returning zero. I also tried one part of Synapse's code to fetch the total (by invoice month, please) from the subreport.

@TotalCreditsFromSub1
WhilePrintingRecords;
Shared NumberVar TotalCredits:= TotalCredits+sum({RHEADER.TOTAL_DOLLAR})

Otherwise, LB's logic sure looks like it should do the trick. Do you think some of the grouping options or Section settings might have something to do with it? Sometimes my Credits subreport flows onto the next page. I have lost track of what should be in the Section Editor and the Grouping Options because I have messed around with those too. I really need to get a screen shot in my next post. Can anyone tell me how to do that? Then you could see my placement of the formulas, etc.

By the way, SynapseVampire - I stand corrected on the ByMonthLink being a shared variable. It is actually a subreport Link. A solution given to me by LB a number of days ago. I am working with this same subreport in this posting thread.

Thanks!!!

 

[mmwdmr]

Vidru,

Do I type this right into my post? Gee I sound stupid, sorry!

 

[lbass]

Please just provide your group structure if different from your original post, and indicate what section you have placed the subreport in. We can go over where the various formulas should be after we know those basic things.

-LB

 

[mmwdmr]

OK here goes:

GH#1a: RACCT.ACCOUNT (Client account number)
GH#1b: RACCT.ACCOUNT (Client account number)
These groups contain client detail

GH#2: RHEADER.INVOICE_DATE (invoice month)

GH#3: ROPIT.DOCNUM (invoice number) suppresed

Details: Invoice line item detail

GF#3: Summaries of invoice line item detail

GF#2a: Total dollar amount of open invoices

GF#2b: SUBREPORT of credit line item detail (there is no grouping in the subreport)

GF#1: Subtotal if invoice amounts and credit amounts (not yet, though) amounts by client account number. The ByMonthLink formula is here to make January's credits print with January's invoices.

RF#1: Number of clients, Grand Totals of invoices and credits (soon I hope).

Main report selection criteria:
RHEADER.Client Type = 1

Subreport selection criteria:
{RHEADER.TYPE} <> "I" and
{RHEADER.CLIENT} = {?Pm-RACCT.ACCOUNT} and
{@ByMonthLink} = {?Pm-@ByMonthLink}

I hope this enough detail to go on. Thanks!!

 

[mmwdmr]

Oh, and the subreport is linked to the main report by client account number and the @ByMonthLink paramter.

 

[mmwdmr]

Oh, and the subreport is linked by client account number and teh @ByMonthLink parameter...

 

[lbass]

You must place the shared variable formula somewhere on the subreport canvas. Then in the main report, insert another group footer section GF#2_c where you will place:

//{@TotalCreditsFromSub1}:
WhilePrintingRecords;
Shared NumberVar TotalCredits;
Numbervar ClientTotal := ClientTotal + TotalCredits;
Numbervar GrandTotal := GrandTotal + TotalCredits;

In GF#2_a you will place:
//{@Reset_TotalCreditsFromSub1}
WhilePrintingRecords;
Shared NumberVar TotalCredits:=0

In GH#1 you will place:
//{@resetclient}:
whileprintingrecords;
numbervar ClientTotal := 0;

In GF#1 you will place:
//{@displayclient}:
whileprintingrecords;
numbervar ClientTotal;

In the report footer you will place:
//{@displaygrandtotal}:
whileprintingrecords;
numbervar GrandTotal;

-LB

 

[mmwdmr]

LB - this almost works! The grand totals are correct however, the total credits by client is picking up the last amount from the previous page instead of the total of all months. Also, these totals are printing at the top of the next page before the next client. They should print at the bottom of the page after the credits and before the next client group starts.

Also, how do you give someone a star?

Thanks!

 

[lbass]

I'm not sure what's going on. Do you have your group #1 (client) set to "repeat group headers on each page"? If so, you should change the reset formula by adding a clause:

//{@resetclient}:
whileprintingrecords;
numbervar ClientTotal;

if not inrepeatedgroupheader then
ClientTotal := 0;

-LB

 

[mmwdmr]

I did have it set. And the NOT clause did work. Everything looks great and I'm manually tallying up a sample of 3 clients but I can tell it is totaling correctly.

This might be a stupid question but how did the NOT clause stop my repeated group header just for the pages with the totals?

 

[mmwdmr]

OK - everything totals up now but since I put the Not InRepeatedGRoupHeader clause, I am getting blank pages after the totals by client.

 

[mmwdmr]

LB - still having a minor problem. See my last post about blank pages. Would it be easier to just remove my InRepeatedGroupHeader formula? Thanks and really, this is the last thing...thanks!

 

[lbass]

The reset formula should not affect pagination. I think this is a separate issue. In the section expert, do you have "new page after" set for the client group footer? Is there more than one blank page after each client group footer? I would also check to see if you might have new page after checked for more than one section. You could also format the sections to "Suppress if blank".

Finally, I would go into the design tab and see if you have extra space in any of your sections. The subreport can be resized to be small, since it has the "can grow" feature, and you can also suppress all unused subreport sections.

Other than that I'm not sure what's going on, but I do think it is fixable without removing the repeated group header. You might see what happens if you uncheck "Keep group together" in the change group expert, too.

-LB

 

[mmwdmr]

LB - yup, I had two sections marked with "new page after". This report has been weeks in the making and I checked and unchecked so many options for different reasons, I lost track of what worked and what didn't.

The report is done - couldn't have completed it without your help on these last few things. Big, big thanks!!

mmwdmr