[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1146
  • Last Modified:

Crystal Reports Running Total Through Sub Reports

Hi All,

I am basing my following question of this:
http://www.experts-exchange.com/Database/Reporting_/Crystal_Reports/Q_24292855.html

Essentially I have the above working for inside my groups.  On a change of group: I have it so that it will reset back to 0.  I now want an aggregate for in the report footer.

Do I need to specify another shared variable and not reset it?  Or is there a way to do a running total based on my formula field?

Again, the code works fine in groups, however I would like to do a running total to get an aggregate, preferably without having to declare another shared variable in each sub report as there are 4 - 5 sub reports that need to be touched.

Thanks.


'MAIN REPORT
'BASIC SYNTAX
whileprintingrecords
shared invCost as Number
 
'reset number cost on base id.
if (PreviousValue ({my_sp;1.id}) <> {my_sp;1._id} ) then
   invCost = 0
else
Formula = invCost
end if
 
 
//SUB REPORT:
//CRYSTAL SYNTAX:
whileprintingrecords;
Shared numbervar invCost;
 
invcost:=invcost + {my_spv2_Inv;1.some_cost}

Open in new window

0
Kyle Abrahams
Asked:
Kyle Abrahams
  • 13
  • 10
  • 4
2 Solutions
 
mlmccCommented:
Add another shared variable that you don't reset to 0.

'MAIN REPORT
'BASIC SYNTAX
whileprintingrecords
shared invCost as Number
shared totinvcost as Number


//SUB REPORT:
//CRYSTAL SYNTAX:
whileprintingrecords;
Shared numbervar invCost;
Shared numbervar totinvcost;
 
totinvcost := totinvcost +{my_spv2_Inv;1.some_cost};
invcost:=invcost + {my_spv2_Inv;1.some_cost}


Why do you need to test for the reset to 0?
If you put that in the group header it will always be true.

mlmcc
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
I need 2 running totals . . . 1 at the group level and a report aggregate.  

My test is definitely off though.  For some reason it's not totalling all of the data.  Is the previous field limited to a page?

Essentially I have 3 sub reports in 3 columns that report on different costs.  Any one of the subreports can extend multiple pages while the others will stop.  Not sure why just one of my values is not getting added properly even though the record before it and after it are added just find.  Grouping is correct as well.

Any suggestions?
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Yeah, with these running totals, if 1 of my sub reports goes more than 1 page long Crystal doesn't include the values.  

EG: subreport 1 is 3 pages, sub report 2 ends on the first page.  Custom running total doesn't include the values in sub report 2.

Why is this?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mlmccCommented:
Where does the formula go in the subreport?

Crystal really doesn't know about pages just records.

Do you have the formula inn both subreports?

mlmcc
0
 
mlmccCommented:
Do you have the group header displayed on each page?

mlmcc
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Where does the formula go in the subreport?
-- I have tried both in details and doing a sum(field) in report footer.  Same effect.

Do you have the formula inn both subreports?
I have different formulas in each subreport.  None of the formulas are shared to their siblings.


Do you have the group header displayed on each page?
Not always.  one  of the sub reports (A) goes for 3-4 pages, and it's then that Crystal magically forgets the value in the other sub report (B).

Update:
If I create different details sections, then the totals come through as expected.  The problem with this is it will cascade vertically.  Is there a way to allign the sub reports in the different sections or get this working properly with 1 detail section?

Thanks for the help.
0
 
mlmccCommented:
Can you upload the report?

Make a copy and change the extension to TXT

mlmcc
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
I had to clean it up /anonymize a little bit.  It's a work in progress so not all totals are in place.  

Focus on Labor costs. in the middle.

This works when in different sections.  As soon as I move them all into one section totals are skewed.




myreportv2.txt
0
 
mlmccCommented:
I think I see the problem.

You haven't declared the total cost variable in the main report before you use it.

The attached report has changes highlighted in rec

mlmcc
myreportv2rev.txt
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
I have incorporated the changes.  Still the same effect.  

Why does having it in different section make a difference.  Note:  If the sub reports don't go over a page long, then everything works fine in 1 section.  Thinking this is the pointer to the root cause.
0
 
mlmccCommented:
Which are giving you trouble?

mlmcc
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Labor cost (costs in the middle).

I put the total in the details just to see what was going on.  

It seems when group 4 changes it disregards the last value added?  Don't see a reason for that though.  Take a look at this screenshot.  Don't see a reason why it's going back to the original total.  It's like it's skipping over.  

The Group change could just be a coincidence  . ..  there is overlapping from SubReportA on this page as well (continued from page 17).
overlap.jpg
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Again, the above is only when the reports are in the same section.  If I create a new section it works correctly.
0
 
mlmccCommented:
I only looked at the cost values.

I don't know.  If you have ot working in separate sections then I would leave it that way assuming the report is otherwise acceptable.

mlmcc
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
It is for now, but am still looking to fix it.  There's gotta be some reason things are getting confused when they all go into the same section.  

The report in different secions like this becomes vertically cascaded taking up a lot of space when they could be put together.  
0
 
mlmccCommented:
One thing you can try is to set the details a and b to underlay the following sections.

Without data i can't really test to see what is happening

mlmcc
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Underlay will only work for charts and maps.

I can't publish the data . . . can you generate some fake data in Excel?
0
 
mlmccCommented:
In the Section Expert there is the option to underlay the following section

I just did it here

mlmcc
myreportv2rev1.txt
0
 
James0628Commented:
I'm not sure why your totals from the subreports are correct when the subreports are in separate detail sections, but not when they're in the same detail section, but I do have a couple of observations (they may or may not be related to that problem).

 1)
 I think there is a problem with how/where you reset your variables.

 For example, in GF2 in the main report, the @laborHours formula resets the variable to 0 if the previous base_id <> the current base_id.  Since this is the base_id group footer, meaning that the section is only used when you've reached the last record with a given base_id, the previous value will only be different when there was only one detail record for a base_id.
 When there is only one detail record for a base_id, the formula in GF2 will reset the variable, which will erase the total that subreport myreportv2_b just put in there for that detail record.
 When there is more than one detail record for a base_id, the previous record when you get to the group footer will be the next to last record with that base_id, so the previous base_id will be the same as the current one and the variable will not be reset by the formula in GF2 (However, see #2, below).

 The formulas in GF2 should just output the shared variables set by the subreports.  They should not try to reset them.  You should have other formulas in GH2 that reset the variables at the start of each new group.  Note that if you reset the variables in the group header:
 a) You can reset all of the variables in one formula.  You don't need a separate formula for each variable.
 b) You don't need to check the previous value of base_id.  The group header is only used at the start of each new group.  The exception to that is if you use the "repeat group header on each page" group option.  If you use that, then the group header will be shown at the start of each new page, which would reset the variables at the start of each new page.  If you use that option, you have to make allowances for that.


 2)
 I also noticed that you have formulas like @laborHours included in the links to subreport myreportv2_b.  That's odd, but I don't know if it's a problem.  You don't seem to actually be using those links in the subreport (eg. in the record selection), so they don't seem to be a problem from that POV.  And, as mentioned earlier, @laborHours, and @laborCost, reset the shared variables when base_id changes, so if those formulas are evaluated every time subreport myreportv2_b is executed, that would reset the variable for the first record in a new base_id.  So, if the laborHours and laborCost variables are being reset for each new base_id, that may be what's resetting them, in which case including them in the subreport links may serve a purpose after all.

 My recommendation would be to remove those formulas from the links to myreportv2_b and create one or more new formulas in GH2 to reset the variables (as mentioned earlier).

 James
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
mlmmc:  I set the underlay, but it had no effect.

James:
The linking is there because of the shared variables.  When I removed linking the totals didn't work for me.

Essentially what I'm trying to do is grab running totals from the sub-report, and put them in the main report.  

Essentially there could be any number of detailed records for GF5, and I need to get values for the entire report and for the GF2 level.

The values are reporting correctly in all instances except where one of the sub reports extends to 2 pages.  

The problem is not with reseting as much as it is ignoring.  If you look at my 2 screenshots you'll notice the header total was 5098.12 after that row was added.  But on page 18, with the same base_id, the header total goes down to 4968.94 after that first row is added when it should be added to the first value.

One of the other group values has changed during that time, but I know for certain that GF2 hasn't been reached.  

I've seen some links about storing and fetching variable values . . . would that apply here or was that for older versions of crystal?
0
 
mlmccCommented:
Storing and fetching is for CR6 and before.  Variables and shared variables were introduced in CR7.

mlmcc
0
 
James0628Commented:
> The linking is there because of the shared variables.
 > When I removed linking the totals didn't work for me.

 That's not normally necessary.  You didn't say what was wrong with the totals, but my guess would be it was what I was saying before - You reset the variables in those formulas and when you didn't include the formulas in the links, the variables didn't get reset when they should.

 I don't see any reason why the number of pages in a subreport would affect the totals.  I do see problems, or potential problems, with how you're resetting the variables (as described in my previous post), and that could affect your totals.  My suggestion is still to change where you reset the variables.  It may not fix your problem, but in my mind, the totals that you're getting will be suspect until you do that.  It's possible that what you're doing works fine for the data you have, but since I can't see your data, I have to consider all possibilities, and there are situations where what you're doing now will not reset the variables properly.

 James
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Just an update.  Sorry for going dark but had a bunch of things pop up.  

Have not tried resetting the formulas as of yet, will hopefully get to it within next day or two.

James:
>>You didn't say what was wrong with the totals
If you go back to my jpgs you will notice that from 5098 it drops down to 4968 on the next line.  It doesn't reset to 0 so I know it's not the formula, but its like it forgets that it added the first value to get to the 5098.

0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
Quick update.  

Running the Underlay on my local still made it cascade.  When I published it to the server and using a Crystal reports viewer the underlay took effect, consolidating the space.  

Still not sure why having it in different sections makes a difference, but, it does and the report is working.

Thanks very much for your time.
0
 
James0628Commented:
> If you go back to my jpgs you will notice that from 5098
 > it drops down to 4968 on the next line.  It doesn't reset
 > to 0 so I know it's not the formula, but its like it forgets
 > that it added the first value to get to the 5098.

 Well, if the variable was getting reset at the wrong point, _after_ the first value was added, that might account for it.  I really don't know.  Without seeing your data, I don't know if that makes any sense.  But it's the first thing that comes to mind - The first value(s) is added, and then the variable is reset.


 If the report is working now and you're happy with it, fine.  It still might be worth taking a look at how/where the variables are reset, when/if you have the time.

 James
0
 
Kyle AbrahamsSenior .Net DeveloperAuthor Commented:
I had to reset them anyway because if there was one record for the group it would report 0 instead of the correct value.  

I believe the option was keep together in the section part that was messing things up for me . . . it was trying to fit everything on the page which wasn't possible and so the printing/calculations were being corrupted.  

Again just a theory but it's working and on to the next task.  Thanks again for the help.
0
 
James0628Commented:
> I had to reset them anyway because if there was one record
 > for the group it would report 0 instead of the correct value.

 Again, that sounds like the scenario I described, where maybe it was reading one record and then the total was being reset (at the wrong point).

 Interesting point about "keep together".  I never thought of that, but if it was affecting the paging, I guess it could have been a factor.

 Anyway, like you said, on to the next problem.  :-)

 James
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 13
  • 10
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now