Totals in Reports

[ricran]

I've created a simple report with a column containing sterling values which I would like to total and enter the result in the footer section. I've created a text box in the footer to hold the total and in the data section of the properties box have created a function e.g =sum('invvalue'). This only results in placing the value of the first item in the report.

This sort of thing is easy in access but I can't see how to do it in OpenOffice. What am I doing wrong?

[Villeroy]

Install the Sun Report Builder extension (SRB).
It's based on a professional 3rd party report engine.

[ricran]

Thanks. I've loaded the SRB extension but am even more confused. I am able to place a box in the form delineated by the six green placement spots but am unable to place text in text boxes or a formula in a formatted field. None of the icons or menu items seem to provide the properties for the new entry. My operating system is Windows XP. Is this the reason as I see the post refers to Vista?

[nv_vss]

I've created a simple report with a column containing sterling values which I would like to total and enter the result in the footer section. I've created a text box in the footer to hold the total and in the data section of the properties box have created a function e.g =sum('invvalue'). This only results in placing the value of the first item in the report.

This sort of thing is easy in access but I can't see how to do it in OpenOffice. What am I doing wrong?

Hi Ricran,

I'm with you - trying to do a simple sum formula, which I used countless times in Access, but can't get it to work in Openoffice - even with the Sun Report Builder. My formula is =SUM([Order Value Pre GST]) and is placed in the report footer. It is supposed to give me a total of all the order values in the query/report. All it does is gives me the amount of the last entry in my database?? There are no groups setup - I don't believe I need them in this report since it's just a list of jobs in progress.

Any ideas anybody?

Thanks in advance,
NV

[r4zoli]

SUM must be work, please upload your file, without sensitive/confidential info, to look into.

[nv_vss]

Apologies,

I have solved my problem. I used the "Accumulation" function. I just need to make sure there are no blank dollar values.

[imackee]

The Accumulation function does work, after a fashion, but there is a trap. As nv_vs implies, if there are blank entries in the column in question an incorrect value is given by the Accumulation. It seems to total only those entries up to a point at which there are a series of blank entries. This is not what most people want. It is possible that adding an argument such as WHERE "Payment" IS NOT EMPTY might work but there is no facility to add an argument to the Accumulation function in Sun Report Builder.
The only way I have found to get a correct result is to create a query whose sole function is to sum the column, in my case it is a Payments column. A report created using this query does give the correct sum of all entries even when there are blank entries.
I have not found a way of getting this data into another report, which is also listing names and date of entry etc. Many methods have been tried.

[eremmel]

One can change the formula easily and make sure that NULL values are changed in correct values. Have a look to the picture and the following steps:

Make accumulate function NULL value prove

0: open report in edit mode
1: open the form navigator (F5)
locate your formula and select the properties (right-mouse)
2: Edit the formula
3: The formula wizard opens
4: Change your formula to make it 'empty value' prove (in this example empty value is replaced by 100,000, but 0 is more logical).
Save the value
5: do the save for the initial value.

I think that it is a bad idea to hide data-inconsistencies in your data model behind functions like accumulate. When you have bad data you have to take care for it. I agree with having an extra function like 'accumulate-with-NULL-as-zero'.

[imackee]

Many thanks to eremmel for the helpful advice on coping with null values. In fact, after additional searching on this forum I changed all null values in the calculated column to zero and made zero the default for new records. Since this was done the Accumulate function gives a correct total.
Your alternative method will be useful but I am not sure that I get the same screen options on Mac OSX.
I should say that the Accumulate function was used via the Sun Report Builder which requires OO 3.2. This has been very useful, there are certainly bugs, but the Report Builder has allowed the successful creation of reports with features that I had previously been trying to achieve but with no success.

[chrisb]

Another way to overcome the problems associated with null values is to use the 'COALESCE' function.

I think it's sensible to tailor any query specifically for use with the report in question.
In the case of an accumulation function within Report Builder we simply need to replace null values with zeros.

1. Open report for edit.
2. Open navigator (F5).
3. Click the top line 'Report'
4. Ensure properties pane is visible. (F4) to toggle on/off.
5. Hit 'Data' tab.
6. Hit '…' gadget to right of 'Content' and Query Design window opens up.
7. Hit the triangle gadget to move from GUI to SQL.
8. Find relevant column within Select clause.
9. Change to 'COALESCE ( ColumnName , 0 )'. Replace 'ColumnName' with the actual name of your column.
10. Save and close window.

Now any NULL values will be replaced by zeros on all operating systems.
The down side of this method is that if the column is a visible column then zeros will be displayed where previously there was a blank space.

[SBCCTech]

Has anyone solved this. I have not been able to get totals in the report footer. I can get it in the group footer but when I move it to the report footer I only get the first record amount.

[r4zoli]

You need separate counter for groups, and another one for whole report.

[SBCCTech]

I think I have been doing that. I looks like the grouping and functions have changed over the years so I may not have it correct. Below is what the navigator looks like.

Thanks

ORB Nav.odg

(19.76 KiB) Downloaded 382 times

[SBCCTech]

Sorry Here's the Navigator

Here's the Sub Total Function

Here's the Total Function

[r4zoli]

You put the report total into page footer, the better place is report footer, if you have no specific reason to put into page footer.
See my example file atteched here.

[SBCCTech]

Thank you! I see that. Thanks for the example!
The only reason I liked the page footer was to fix the total to the bottom of the page.