Displaying Totals Based on Another Subreport

[TheBig1980s]

I have a Crystal XI report containing inventory items. These items come from a SQL 2005 table.

In this report, I also have a subreport that pulls the item quantities from an Excel spreadsheet. These items and quantities are in a group footer. When I generate this report, the data in both the main report and the subreport are correct.

In this main report, below this group footer, I have placed another group footer that needs to show customer totals. I need to have this footer show the quantities, in total, for all items for each customer.

I have tried adding subreports to do this, along with the shared variables "stuff". Unfortunately, all that this customer totals footer is giving me is 0.

Any ideas?

 

[TheBig1980s]

I used the same formulas that you gave. So, it's useless to repost them.

 

[TheBig1980s]

Hi LB:

You're a genius! I finally got this to work. There were two things that led to this success. First, as you said, I needed to simply use the same subreport that I had. Secondly, I needed to suppress some key fields and sections. Most notably, I needed to suppress the report footer of the subreport that contained the summed item quantity.

Anyhow, for all of our reference, below are your correct instructions annotated with my notes on the fields and sections that I suppressed.

Thanks so much, for sticking with me on this!

DO NOT CREATE A NEW SUBREPORT! Simply, place the following formula in the report footer of the subreport that you’re trying to get customer totals for, and suppress that footer:

whileprintingrecords;
shared numbervar itemqty := sum({table.qty});
//in the above formula, the table.qty field is the formula (called “@Budget”) that shows the item //quantities in the subreport.

Then, place the following formula (called “Accum”) in Group Footer 4b and suppress that section:

whileprintingrecords;
shared numbervar itemqty;
numbervar sumitemqty := sumitemqty + itemqty;

In the Group Footer 2, you would use a formula like this to display the total:

whileprintingrecords;
numbervar sumitemqty;

You would need a reset formula in Group Header 2, and you need to suppress this formula but not the section:

whileprintingrecords;
numbervar sumitemqty;
if not inrepeatedgroupheader then
sumitemqty := 0;

You should also reset the shared variable in Group Header 4, and you need to suppress this formula but not the section:

whileprintingrecords;
shared numbervar itemqty;
if not inrepeatedgroupheader then
itemqty := 0;

Finally don’t forget to place a formula in the Report Header to declare the “itemqty” and the “sumitemqty” variables. You can suppress this formula, but don’t suppress the section:

WhilePrintingRecords;
Shared NumberVar itemqty;
Shared NumberVar sumitemqty;
''

 

[lbass]

Suppressing subreport sections would not affect your results. Nor is the last formula necessary.

-LB

 

[TheBig1980s]

Yes, it does make a difference. Before, data was getting doubled-up and was affecting my Customer Totals.

But, as Hannibal said to B.A in the old "A-Team" tv show, "Have it your way, B.A. The best offense is the best defense." Mr T., then, smiled and said "Thanks, Hannibal! Thanks!" It was cute! My buddies and I in junior high school the next day laughed all the way to school on the bus when we talked about that episode.

 

[lbass]

It really wouldn't have an impact--suppression does not exclude records from totals. Did you remove the old second subreport? If you reference an accumulating variable twice, it will execute twice and inflate totals. Suppressing the subreport would eliminate that effect.

So if you remove any old unnecessary subreports and unsuppress the section and remove the initialization formula in the RH, you should still get the correct totals.

I'm glad you got it to work though.

-LB

 

[TheBig1980s]

Oh, OK. Maybe that was it. You're right. I did remove that old second subreport.

Again, thanks for all of your help and for all that you do! I still say that you're a genius! You would be the "Murdock" on my A-Team. No matter how the writers portrayed his character, he truly was the smart and technical one on the Team!