Solved

Crystal Reports Running Total Through Sub Reports

Posted on 2009-05-11
27
1,127 Views
Last Modified: 2013-11-15
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
Comment
Question by:Kyle Abrahams
  • 13
  • 10
  • 4
27 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Do you have the group header displayed on each page?

mlmcc
0
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Can you upload the report?

Make a copy and change the extension to TXT

mlmcc
0
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Which are giving you trouble?

mlmcc
0
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
Again, the above is only when the reports are in the same section.  If I create a new section it works correctly.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 450 total points
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
Underlay will only work for charts and maps.

I can't publish the data . . . can you generate some fake data in Excel?
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
In the Section Expert there is the option to underlay the following section

I just did it here

mlmcc
myreportv2rev1.txt
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 50 total points
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Storing and fetching is for CR6 and before.  Variables and shared variables were introduced in CR7.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> 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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> 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
 
LVL 39

Author Comment

by:Kyle Abrahams
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
> 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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now