Ask Your Question
0

How to reuse an accumulated value in a Report?

asked 2018-01-21 10:58:52 +0200

EasyTrieve gravatar image

updated 2018-01-22 00:45:33 +0200

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.

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
1

answered 2018-01-21 22:54:02 +0200

Ratslinger gravatar image

updated 2018-01-22 20:17:26 +0200

Hello,

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

image description

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:

image description

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

image description

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

Field (CostTotal) containing accumulation set up used above:

image description

Edit 1/22/2018:

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

image description

Defined as:

image description

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

Produces this report:

image description

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

Thought this may interest you.

edit flag offensive delete link more

Comments

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.)

EasyTrieve gravatar imageEasyTrieve ( 2018-01-22 08:43:46 +0200 )edit

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.

Kavita gravatar imageKavita ( 2019-06-14 07:09:18 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2019-06-14 17:49:24 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-01-21 10:58:52 +0200

Seen: 146 times

Last updated: Jan 22 '18