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?

 

[lbass]

Please identify the fields you are grouping on in the main report, identify in which group section the subreport is located, and how it is linked to the main report (on what fields).

-LB

 

[TheBig1980s]

In this report, I have a subreport that pulls the item quantities from an Excel spreadsheet. The item IDs for these quantities are in the main report in a group footer grouped by item ID, of course. And, I have placed the subreported quantities into this same group footer as well. This is all in Group Footer #4. When I generate this report, the data in both the main report and this subreport are correct.

In this main report, in group footer #2, 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. This group footer is grouped by customer number.

The problem is that the item quantities that I am trying to total are in a separate subreport in group footer #4. So,I need to find a way to total those quantities for each customer in group footer #2. Hence, I am trying to find a way to bring in a total from one subreport to another. The problem is that those quantities are from a subreport in another group footer--not simply a field in the main report.

Subreports are wonderful. But, I wish that the technology were there to have the subreports be used as regular Crystal fields. Boy, it would be wonderful to simply right-click on a subreport in a main report and choose "Insert...Summary", just like you can for a regular Crystal field.

 

[lbass]

This still isn't clear. I think you misworded one of your sentences. It sounds like you want to total quantities from from the sub in GF4 and show the result in GF2--but why are using a subreport for that in GF2?

If you set up the item quantities as a shared variable in the report footer of the item subreport like this:

whileprintingrecords;
shared numbervar itemqty := {table.qty}; //maybe it's a sum?

...then in the main report, in in GF4b (with the sub in GF4a), you would add a formula like this:

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

In the GF#2, you would use a formula like this to display the total:

whileprintingrecords;
numbervar sumitemqty;

You would need a reset formula in GH#2:

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

You should also reset the shared variable in GH4:

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

-LB

 

[TheBig1980s]

I'll try this. Thanks! Just for your edification, though, the reason that I have a subreport for the quantities is because the quantities are in the Excel spreadsheet and I need to pull those quantities onto the report.

I re-checked the wording of what I said. It's all accurate. I think that where the confusion is lies in the fact that this is not a new report. This is a huge report that we created a year ago containing subreports, formulas, and now Excel-based data.

TheBig1980s

 

[lbass]

I cannot tell why or where a second subreport becomes involved in the calculation--reread your second to the last paragraph in your 8:49 post.

-LB

 

[TheBig1980s]

I wish that I could attach a file to this case without having to create a free "Media Fire" account. So, I'm going to describe my attachment, as best as I can.

The attachment would show the problem that I'm running into. I was able to get this shared variables stuff to work, except for one thing. In the lower-right hand corner of the attachment is a figure: 2,220. This is the total field that I need (i.e. "Customer Totals"). But, it should be 2,220 plus 1,098. What's happening is that the Customer Totals field is only pulling the first record (i.e. the first inventory item in the report for each customer). For this particular customer in my attachment, there are two inventory items: one for a quantity of 2,220 and the other for 1,098. The Customer Totals field should add these two fields together. But, it should be totaling all items for each customer. It should be adding 2,220 plus 1,098.

I am using these shared variables, as best as I can. I don't know what further info to give other than to say that the subreport is in Group Header 2 which represents customer ID, the header declaring the shared variables is in the Report Header, and my Customer Totals field is in Group Footer 2.

 

[TheBig1980s]

I tried all of those formulas that were suggested on 04/07/10, but the Customer Totals came out as 0. So, unfortunately, I'm no better off after having tried that.

 

[lbass]

Please answer specifically the questions from my first post:

1) What fields are you grouping on in your main report?
2) In which section is the subreport located?
3) How is the subreport linked to the main report?

And also, within the subreport, what fields are you grouping on?

-LB

 

[TheBig1980s]

Hi There:

Below are the answers:

1) What fields are you grouping on in your main report? Customer ID and Item ID.
2) In which section is the subreport located? Group Header 2.
3) How is the subreport linked to the main report? By customer, item, and item class.

And also, within the subreport, what fields are you grouping on? None.

 

[lbass]

I'm confused--what happened to your group #4 in earlier posts?

I also don't think you should be linking by item class in the subreport, since the main sub if placed in the item group header will only show results for the first class in the item group.

-LB

 

[TheBig1980s]

Group footer 4 contains the subreport that shows the quantities per item. It is for these items that I need to have a customer total for. That's why I created a new subreport that sits in group header 2, so that I could create shared variables.

So, that should also address the questions that you had earlier on the two subreports that I'm using. Group footer 4 has the quantities per item and those quantities are in a subreport. Group header 2 is where I have the subreport with the shared variables to be used for creating an accurate formula that will sit in group footer 2. So far, I cannot get a good formula. I have been only able to have that formula show the quantity for the first item for each customer rather than the total for all items for each customer.

 

[lbass]

My point is that you have identified ONLY two groups in the main report, so what are group #3 and Group #4 fields that you are grouping on???

-LB

 

[lbass]

Also, I do not see why you are adding a second subreport--why do you think this is necessary? If you want to total the items per customer, you don't need a second subreport to do that.

Please respond to both of these posts.

-LB

 

[TheBig1980s]

Group 2 is customer number, group 4 is item number. Did I not mention that earlier? I apologize, in advance, if I did not.

I don't have a problem with not adding a second subreport. But, can you add a shared variable formula to a subreport that is already displaying data? I did not know that you could have shared variables in a subreport that has other fields in it.

 

[lbass]

Please identify ALL groups by the fields that are being grouped on--I don't know how to ask for this any more clearly.

This is a simple issue, but I have to understand the layout of your report. Yes, you can add shared variables to subs that displaying results.

-LB

 

[TheBig1980s]

Well, what other groups are you looking for? Beyond the relevant ones that I just said, Group 1 is salesperson ID and Group 3 is Item Class ID.

There's no need to get upset. I'll find a way to get this work through whatever resource I have to. If this is upsetting you, then I'll understand if you'd prefer not chiming. I'm just a man trying to get his work done for a client. I came here first as I have found great value in the past from it.

 

[lbass]

I am simply frustrated that you are not providing the basic information that I have requested--because it IS relevant to the problem at hand--and I did request it in my very first post.

First, if the sub is in Group footer #4a, it should be linked on salesperson, customer, item class, and item.

Then in the subreport report footer, you should have this formula:

whileprintingrecords;
shared numbervar itemqty := sum({table.qty});

Then follow my previous instructions:

//{@accum} to be placed in GF#4b.
whileprintingrecords;
shared numbervar itemqty;
numbervar sumitemqty := sumitemqty + itemqty;

In the GF#2, you would use a formula like this to display the total:

whileprintingrecords;
numbervar sumitemqty;

You would need a reset formula in GH#2:

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

You should also reset the shared variable in GH4:

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

-LB

 

[TheBig1980s]

Well, I tried all this and it just gave me 0 for the customer totals.

 

[lbass]

Please recheck the placement of all formulas, and I guess maybe post them here--so I can troubleshoot them. Make sure you have consistently used "shared" for the itemqty variable and that you have used the correct variable names in each place.

Also make sure that you did not suppress or hide the subreport or the section that it is in.

-LB