How to reuse an accumulated value in a Report?

[EasyTrieve]

I have a Base Report with a simple column of numbers, and a “accumulated” sub-total. (Setup below).

10
20
30
==
60 (SubTotal)

How can I use this sub-total elsewhere in another Text Box calculation?

For example how would I display half the sub-total’s value with something like:

Data Field Type... Field or Formula
Data Field.........[SubTotal]/2

(given that my Text Box with the accumulation is named SubTotal)?

---

When I try I get nothing output. If I try just 1/2, that works (the leading = is implied).

Workaround: I know that I can generate an extra field in my underlying query with this math in it for every single row, and then sum that new column, but I was hoping to learn how to do simple math in Base reports with the results from accumulations.

---

---

To create the sub-total: Creat a Text box in a Footer, then open Properties & the Data tab, and set as follows:

Data Field Type...Function
Data field........<field name>
Function..........Accumulation
Scope.............<section name>

Tip! There’s currently a minor bug in the user interface. After you edit any of these fields, click somewhere else to set the value before you run the report. Otherwise the run won’t take notice of your change.

[Ratslinger]

Hello,

When an accumulated function is used in a Text Box, internally it is given a name:

This example has one with a name of AccumulatuionCostJob ID in a text box named CostTotal.

You can use the VALUE of that field for manipulation. Result:

Another use of the accumulated figure. This time applied to the detail:

You can open image in a different tab for a better view.

Field (CostTotal) containing accumulation set up used above:

Edit 1/22/2018:

Slightly different but same area. This is for Running Totals. Added Function to ‘Job ID’ header:

Defined as:

Then the text field in detail line: VALUE([CounterJob ID]) (entered in ‘Data field’).

Produces this report:

Value resets upon a new “Job ID” so each invoice has running total.

Thought this may interest you.

[EasyTrieve]

Very good. I saw that the stuff in the Report Navigator window a bunch of times but It never crossed my mind to double click on the stuff. Wow. Also was surprised that I had to use VALUE again out side the returned values (which must come back as strings again??), like this VALUE(VALUE([Accumulation1])-VALUE([Accumulation]))/2 Thank you!! (BTW, nasty deadly snake when resizing the Report navigator window. Crashed my window manager.)

[Kavita]

This is not working and i am unable to reuse accumulation in my report. Can you please help me in this and attach sample file. It is not showing nothing in test box when using the accumulation one more time. i.e. accumulation value + one more value giving nothing.

[Ratslinger]

@Kavita,

There is a similar example with sample in this post → Libre Data - multi-level report. It also contains user functions. If you have further problems, please post as a new question with details and a sample if possible. Never include personal or confidential information.

[builderkf]

Is it possible to use the value of an accumulated field from a group in the report footer? i.e. I have 3 different group totals, I only need the total from one of the groups in a calculation in the report footer.

[Ratslinger]

@builderkf

@builderkf

Is it possible to use the value of an accumulated field from a group in the report footer?

.
This is too vague. Report footer is end of report. What are you attempting to do with it. Redacted sample may be of help.

[builderkf]

Thanks for getting back so quick.

I’ve attached the report with a screen shot of the group function I’m trying to reuse. I’m trying to get the accumulated total of only the cabinets in order to calculate the number of containers needed for shipping. It’s in the report footer because that’s where I’m trying to sum up all of the costs. I tried Value([AccumulationTotal CabinetsProduct]/400). But, that isn’t working.

Thanks in advance for your help. Much appreciated.
Doc1.odt (72.5 KB)

[Ratslinger]

@builderkf
.
Should have been more clear. A sample is a file - Base, Writer, Calc etc.
.
Images generally are useless as they do not show details. Without this sample you are asking me to re-do this report based upon a picture. In the future please post example file showing the problem.
.

@builderkf

I tried Value([AccumulationTotal CabinetsProduct]/400).

This should work:

VALUE([AccumulationTotal CabinetsProduct])/400

.
Also to avoid many, many, many issues, do not use spaces in any names. It will come back to bite you!

[builderkf]

I edited all of the tables names due to having spaces. The names were a big problem. I pretty much started fresh and built back to where I was before. I realized the accumulated total I was trying to use yesterday wasn’t right. Not even sure where that function came from. Anyway, I’m still trying to accomplish the same thing. Which is, I need the value of the accumulated total of a category called “Boxes” to be calculated in the report footer where I then need to divide that by 400. I’ve highlighted it yellow in the report.

Thanks again for all your help. This is a head scratcher.
CabinetEstimater3.odb (24.5 KB)

[cpb]

@builderkf,

your function needs to parse the entire report.

to create:

1. open the navigator and left click ‘Functions’ below ‘Report’.
2. right click ‘Functions’ below ‘Report’.
3. select ‘New Function’ from the context menu, a new function called ‘Function’ has been created.
4. left click ‘Function’ and add the info as shown in the pic.
5. left click the text box next to ‘Total Containers’.
6. Properties > Data > ‘Data Field Type’ = ‘User defined Function’ > ‘Function’ = ‘SumBoxes’.

[builderkf]

Ya’ll are amazing. Thanks for everything.

[Ratslinger]

@builderkf
.
Please do not mark an answer as a solution unless you asked the original question. What you have now done is to apply a solution to the original question that is not related to that question and removed the originally accepted answer from years ago.