Pass Grand Total from Subreport to Main report 1

[mmwdmr]

I have a main report that totals invoice amounts by account number. My subreport totals payments/credits with no grouping (no, the file structure does not allow me to link these two tables in the same report). I want a grand total of all payments/credits from the subreport to display in the report footer of the main report.

I have tried a number of things, the latest a shared variable between the main and the sub (and experimented with placement, etc.). I have a shared variable in the subreport report footer that sums the dollar amount. I have a formula in the main report that references the shared variable grand total from the sub. If I leave the subreport un-linked to the main, I get a grand total of all payments/credits but the totals are greater than they should be (as expected with no link - I get ALL transactions in that file). However, as soon as I link the two reports by account number, I get no data for a grand total.

I've been at this for about 8 hours straight and my brain can't grasp why this is not working. I figured some "fresh" thought might help.

I am using Crystal 9 This is the last thing that needs to be done on this report that has been weeks in the making so I am desperate!!!

 

[synapsevampire]

Try describing your reports with more technical details, such as:

Main report groupings?
Where is the subreport located within the main report?
How can you limit data to some criteria in the subreport if the subreport has no equivalent linking criteria?

I would guess that the data types for the main and subreport are different, and stating that you don't have anything to link on seems incorrect if both tables have an account number.

I suggest that you verify the data in the account number field for both tables, and you may want to wrap each of th account number columns in a Trim function or some such within the subreports Report->Edit Seelction Formula->Record.

If you supply example data and expected output you'll get a more tailored response.

-k

 

[lbass]

Link the subreport to the main report on account number. The subreport should be placed in the group header or footer for the account number group. In the subreport, your shared variable formula should look something like:

whileprintingrecords;
shared numbervar paycr := sum({table.amt});

You must place this formula on the subreport canvas somewhere.

In the main report, create a formula to sum the subreport values:

whileprintingrecords;
shared numbervar paycr;
numbervar sumpaycr := sumpaycr + paycr;

This must be placed in a section below the one in which your subreport is executing. If the subreport is in GH#1_a, then place this formula in GH#1_b.

Then in the report footer, place a formula:

whileprintingrecords;
numbervar sumpaycr;

If any of your accounts might return a null for the shared variable, then you also need a reset formula in the main report:

whileprintingrecords;
shared numbervar paycr := 0;

This should be placed in a section above the one in which the subreport is executing or in a section below the one in which you have the main report shared variable accumulation formula.

-LB

 

[mmwdmr]

To synapsevampire:
1. In my post, I said the main report is grouped on account number (total amounts by account number).
2. In my post, I said I had experimented with the placement of the sub report (I tried different sections).
3. I can limit the data via criteria but if this works the way I think it should, I shouldn't have to do that-the subreport data should obey the main report's criteria.
4. The data in the account number field is valid that's why I don't understand why it is not working, thus the original post.

To Ibass:
Your solution appears to be exactly what I was looking for. I was on the right track with shared variables but I missed the sumpaycr + paycr part in the main report formula. I will try this first thing in the morning and let you know! Thanks!!!!!!!

 

[synapsevampire]

1. In my post, I said the main report is grouped on account number (total amounts by account number).

Still don't see it, I see that you total by account number, but since you say nothing worked, I wanted to know precisely what you mean, which I suppose now meant that you've grouped by account number and display totals there.

2. In my post, I said I had experimented with the placement of the sub report (I tried different sections).

Right, try to be specific.

3. I can limit the data via criteria but if this works the way I think it should, I shouldn't have to do that-the subreport data should obey the main report's criteria.

The subreport doesn't obey anything from the main report, linking is represented by the subreport->report->Edit Selection formulas->record criteria. If it's linked, or a parameter is passed from the main report, then it's represented there, and that's how the data is filtered/limited.

4. The data in the account number field is valid that's why I don't understand why it is not working, thus the original post.

LB's solution looks fine, except that you explicitly stated that it cannot by linked "However, as soon as I link the two reports by account number, I get no data for a grand total."

So his post is more helpful, but it also did not observe your requirements and observations, which obviously I should have ignored as well and just explained theory, but I wanted clarification as people tend to change requiements if they're vague to begin with.

Hope it works out.

-k

 

[mmwdmr]

synapsevampire,

I re-read my post and I guess it's a matter of interpretation - Ibass didn't seem to have a problem with the way I explained it. Anyway, I'm in here often and I know you help a lot of people (you've probably helped me with something in the past!!) so I will heed your advise and try to be as explicit as possible in the future. Sometimes it is hard to get an idea across and be brief at the same time.

I didn't mean to offend, honest! And thank you for your suggestions - all feedback is appreciated.

 

[mmwdmr]

Hey Ibass - it worked like a charm! This was the last hurdle on this nightmare report so I want to give you big-big thanks!!

And I did not have to limit data in the subreport. As soon as I linked the sub to the main by account number, the sub obeyed the criteria in the main. Placement of the forumulas and subreport is absolutely key though.

Fan-freakin-tastic!!!!!!!

 

[adabalar]

Hi lbass,

From your notes
***********************
Link the subreport to the main report on account number. The subreport should be placed in the group header or footer for the account number group. In the subreport, your shared variable formula should look something like:

whileprintingrecords;
shared numbervar paycr := sum({table.amt});

You must place this formula on the subreport canvas somewhere.

In the main report, create a formula to sum the subreport values:

whileprintingrecords;
shared numbervar paycr;
numbervar sumpaycr := sumpaycr + paycr;

This must be placed in a section below the one in which your subreport is executing. If the subreport is in GH#1_a, then place this formula in GH#1_b.
********************************************

My question to you is how to refer/access this shared variable values(that gets populated in the sub report) in GH#1_a. I know that they are only available for the sections below GH#1_a. But I have a tricky situation where I need to check that value and based on that I need to supress that header GH#1_a. I really appreciate help from everybody.

 

[mmwdmr]

As I mentioned in my post, the subreport is already linked to the main report. It is not placed in the account number group though. I will try to movethe subreport and see what happens.

I'm still accesting other suggestions as well. Thanks!

 

[lbass]

adabalar,

You would have to insert another group header section and place the subreport in the topmost section. But then you have the problem in lower versions of CR of not being able to eliminate this highest level section containing the subreport. You can suppress all sections within the subreport, remove the border, and resize the subreport and group header section to make it as thin as possible, but I think that'a about all you can do, at least in CR 8.0.

-LB

 

[adabalar]

Hi lbass,
You exactly got my point.My report is working fine.The problem I am having is the blank space when sub report gets suppressed based on a condition. As there are too many of these, we are seeing lot of blank space on the main report at group header section.That is the reason I wanted to use the same condition that sub report using for group header to suppress it.But I am not able get those values as they are part of sub report.I am trying to make my sub report as thinner as possible on the main report.but what else I can do to get rid of the blank space?I am new to Crystal.I appreciate your support.

 

[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.

 

[lbass]

mmwdmr--I believe your last two posts relate to a different thread. Please repost there so we can keep the flow consistent.

adabalar--

The only other thing I can think of that you might try is to use "underlay following sections" on a group header that is above the one containing the subreport.

-LB

 

[mmwdmr]

LB - I don't understand. I started this thread and further posts here by me are only related to my opriginal post. It seems like Alabalar jumped into my thread here - he tried to answer my post and then in his reply, asked a different question entirely. I don't want to start over somewhere else because I will lose the sequence of all of the replies.

Can you please re-read the entire thread and pick up where we left off? I tried your last suggestion and posted my response - that it almost works. The grand totals are accurate but the total credits per client are carrying forward the last "by month total" from the previous page, onto the top of the next page. It should be displaying all of the client credit for all of the months, then start over with the next client.

Thanks!!

 

[lbass]

mmwdmr--

Perhaps I'm wrong, but it looks like your response are to thread767-1008327. Earlier in the current thread, you seemed to have found a satisfactory solution. They may be the same problem presented in a slightly different fashion, but my more current responses have been in that other thread. Am I wrong?

-LB

 

[mmwdmr]

I apologize - I don't know what happened but I was just following the link everytime I received a notification via email. Anyhoo - I have not found a total solution yet so I will post my findings in the other thread. Please go there because we are so close!! I appreciate your help with this, by the way! Thanks!