[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Can a running total that is placed in a group footer be used in a formula in the Group Header?

Basically I have a group header with three detail lines below that each have an order qty.
In the Group Footer I have a running total that is adding up those three qty's. (I have to use a running total because I am evaluating using the Previous function to eliminate a few duplicates.)

Now, I have this formula which is developing a total cost based on many different things:

if {BAQReportResult.SE-CalcIssQty} > 0
        and {BAQReportResult.SE-CalcIssQty} <> {BAQReportResult.JobMtl.RequiredQty}
        and {BAQReportResult.PORel.XRelQty} > 0
        and {BAQReportResult.PORel.XRelQty} < {BAQReportResult.SE-RemainingQty}
        and {BAQReportResult.PORel1.XRelQty} = 0;        
then {BAQReportResult.PORel.RelQty}/{BAQReportResult.PORel.XRelQty}*{BAQReportResult.PODetail.DocUnitCost}* {#XRelQtySum} +
                {BAQReportResult.JobMtl.TotalCost}


One of the things it needs to take into account is the running total (in bold in the above formula). However, I need to place this formula in the Group Header, in which case the running total is not accumulating all of the data in the detail sections..  (If I place the formula (it's called CurrentCost) in the Group Footer, it does calculate out correctly)

So is their a way for me to modify the formula to tell it to calculate the running total from the number it ends up with at the group footer rather than the number it calculates out to in the group header?

Thanks for the help!
0
SeyerIT
Asked:
SeyerIT
  • 3
  • 2
  • 2
1 Solution
 
mlmccCommented:
In a word NO.

Running totals are calculated as the report is built so the value available in the group footer isn't fully calculated until all the records for the group are processed.  Thus the value is not available when the group header is built because the records for the group haven't been processed.

WHat is it that requires you to use a running total for that calculation?

mlmcc
0
 
SeyerITAuthor Commented:
I have to use a running total because I am evaluating using the Previous function to eliminate a few duplicates.

I guess I can just hide the Detail section and make the GH4 and GF4 appear as though they are one section :( That won't be too much trouble, but I like to see what my possibilities are..
0
 
mlmccCommented:
The only other way is to add a subreport to the group header that does the calculation.  It could return the value or do the full calculation and show it.

mlmcc
0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
SeyerITAuthor Commented:
I know how to add a subreport but thats about it..  Would you be able to tell me how I would setup the subreport? Do I need to get my formulas from my main report into my subreport somehow?
0
 
James0628Commented:
I would probably handle this by inserting the current report as a subreport.  Then you can delete all of the "extra" stuff that the subreport doesn't need (fields, tables, etc.).  That way the subreport will already contain all of the same formulas and your running total.  If you're using global variables (as in another of your questions), those will be separate in the main report and subreport.  IOW, setting a global variable in the subreport will not affect the main report, and vice versa.

 If your main report has any parameters, the subreport will have the same parameters, and you can link the main report parameters to the subreport parameters.  And since you want the subreport to produce the total for a single group, you would link whatever fields are required to the subreport - Probably the fields for that group, and any group levels above that.  For example, if you're looking for a total for group 4, you'd probably pass the subreport the fields for groups 1 - 4, so that it could include one specific group at a time.  I would create parameters in the subreport for those group fields, use the parameters in the subreport's record selection formula and link the group fields in the main report to those parameters, but you can also link the group fields to the subreport fields in the "Change subreport links" window (and CR will create the subreport parameters for you).

 Then, in the footer of the subreport, you'll need to put a formula that uses your running total to set a shared (not global) variable.  Something like:

Shared RelQtySum;
RelQtySum := {#XRelQtySum} ;
""


 Then you'd use that shared variable in your formula in the main report, instead of {#XRelQtySum}.

 A few other details.

 The subreport should be in a section of the main report that comes before the section where you're going to use that shared variable.  If they're in the same section, the formula may be evaluated before the subreport is run, in which case the variable won't be set.  So, for example, if your formula in the main report in is GH4, you would create a new GH4 section and put it above the current one, and put the subreport there.

 You may want to reset the shared variable to 0 for every group.  If the subreport will always produce a value, then it should set the variable to a new value with each new group and resetting the variable shouldn't be necessary.  In this case, I think the main concern would be if the subreport was not going to read any records.  If that happened, it might not set the variable (and the variable would be left with the value from the previous group).  But that doesn't seem to be a problem here, since the subreport is just reading the same records as the main report, so there should always be at least one record.  But if you wanted to reset the variable to 0, you could create a formula that set it to 0 and put that formula in a group header section in the main report that's above the section with the subreport, or in a group footer section in the main report.

 Since the subreport is just there to set a variable, you presumably don't want it to produce any actual output on the report, but if you simply suppress the subreport, or the section that the subreport is in, the subreport won't be run at all.  Instead, you need to suppress the sections in the subreport (the running totals, formulas, etc. in the subreport should still be evaluated), then go to the subreport format options in the main report and check the "Suppress blank subreport" option.  If the subreport is in a section by itself, you can go to the format options for the section and check the "Suppress Blank Section" option.


 So, there you go.  Simple, huh?  :-)

 James
0
 
SeyerITAuthor Commented:
Wow James.. Thanks for the rundown on this!
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now