Crystal Reports Running Total Through Sub Reports
Hi All,
I am basing my following question of this:
https://www.experts-exchange.com/questions/24292855/Running-Totals-of-Sub-Reports.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.
I am basing my following question of this:
https://www.experts-exchange.com/questions/24292855/Running-Totals-of-Sub-Reports.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}ASKER
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?
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?
ASKER
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?
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?
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
Crystal really doesn't know about pages just records.
Do you have the formula inn both subreports?
mlmcc
Do you have the group header displayed on each page?
mlmcc
mlmcc
ASKER
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.
-- 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.
Can you upload the report?
Make a copy and change the extension to TXT
mlmcc
Make a copy and change the extension to TXT
mlmcc
ASKER
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
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
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
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
ASKER
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.
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.
Which are giving you trouble?
mlmcc
mlmcc
ASKER
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
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
ASKER
Again, the above is only when the reports are in the same section. If I create a new section it works correctly.
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
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
ASKER
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.
The report in different secions like this becomes vertically cascaded taking up a lot of space when they could be put together.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Underlay will only work for charts and maps.
I can't publish the data . . . can you generate some fake data in Excel?
I can't publish the data . . . can you generate some fake data in Excel?
In the Section Expert there is the option to underlay the following section
I just did it here
mlmcc
myreportv2rev1.txt
I just did it here
mlmcc
myreportv2rev1.txt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
Storing and fetching is for CR6 and before. Variables and shared variables were introduced in CR7.
mlmcc
mlmcc
> 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
> 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
ASKER
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.
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.
ASKER
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.
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.
> 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
> 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
ASKER
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.
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.
> 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
> 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
'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