Hide Detail Section while still allowing WhilePrintingRecords to work with Variables, or How can I use Sum or create a Running Total of a Shared Variable

lpii
lpii used Ask the Experts™
on
My report has a sub-report for Freight Hours using a formula as a lookup.  I get the hours, but need a Shared NumberVar so I can use the hours (which all works fine) and multiply by it's Unit Cost.

My main report needs to summarize the detail lines in a Group Footer using sum (without showing the detail lines which also contains the sub-report for the Freight Hours).  Since CR won't let you use Running Totals on Shared Variable I had to use create Formula Fields using WhilePrintingRecords for summing Freight Cost for the Item, Customer & Company (adding these to details section b with reset Formula Fields setting the Variable to 0 in its respective Header)

The problem is, if I hide or suppress the detail lines, WhilePrintingRecords will not evaluate so my Group sums correctly the Qty, Sale Price, Cost of Goods Sold, but shows the Freight Cost (the Sub-Report) as 0.  Therefore the Profit/Loss does not deduct the Freight Cost.

If I show all the details a, b, c, the report runs perfectly (but they only want to see the summary not every detail line)

Is there a way to hide the detail lines, but let CR calculate the Fromulas based on WhilePrintingRecords (I don't want to shrink the detail lines as some Items might have 50 detail lines others 1, so even minimal detail height will still make the report's look inconsistent)

Other than creating another Sub-Report to bring in the summary line, with a the current Sub-Report for the Freight Hours is there another way of getting the report to treat the Sub-Report value so that it can be summed or use Running Totals?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Yes.  Add another detail section and put the summing formula in it but have it return nothing (empty string)

You can then "hide" the section with the HIDE EMPTY SECTION option in the SECTION EXPERT.

mlmcc

Author

Commented:
I am not following you in regards to "put the summing formula" in another details line.  Do you mean create a new formula that equals "", or are you referring to one of my summing formulas that I have already?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Use the one you already have and add the '' as the last line so it doesn't display anything.

mlmcc
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Author

Commented:
I am still not quite following you.  If I have multiple sums, which would display a number, how would I turn it into a null by adding ''.  Are you saying I would need to add formulas for each sum to turn them into null, each detail line has it's own section expert, so I don't know how adding 1 extra detail line, adding a blank formula or somehow getting it to become null, how it will blank out Detail sections a, b, c (and the new section d)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you upload the report so I can add the formulas for you so you can see how it is done

Author

Commented:
I had to scrap that report previously and approach it from a different manner because I had to get it done, this report that I just made is a similar situation in that I need to hide the Group Header 3, but still have the Sub-Report create the shared var Qty_Paid in the function 1_Qty_Paid, so I can create not only display 1_Qty_Paid, but also use it in the formula 1_Delta (both in the details line)
Job-GainFade-Report-3.rpt

Author

Commented:
The last part of the sentence should read
so I can not only display 1_Qty_Paid, but also use it in the formula 1_Delta (both in the details line)
Commented:
I don't know if this would have worked in the original issue of details, but on the report I uploaded, I had to hide the Sub-Report detail line, in the Common Tab in section Expert in the Group Header, I had to Suppress the blanks sections (didn't work showed the Group Header 3 as a blank line) I then had to check off Underlay Following Section, why, I don't know, but I know have the report without blank lines.

There has to be a better way of passing values from a Sub-Report to the main report, allow the Sub-Report Shared Variable to calculate while hiding a line instead of all this extra work just to get blank lines or sections to not show up.  Very poor system of sharing variables.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial