Sum fields on a Report based on a Query with criterion

[ukhupacha]

Hello,

I have a query based on an invoice table and a customer table.
I would like to make a report based on the unpaid invoices of said query(Criterion of customerID).

SELECT “tbPeladoras”.“PeladoraID”, “tbPeladoras”.“Apellidos”, “tbPeladoras”.“Nombres”, “tbEntregas”.“Fecha de Recepcion”, “tbEntregas”.“Verde”, “tbEntregas”.“Pelado Entero”, “tbEntregas”.“Pelado Partido”, “tbEntregas”.“Soles”, “tbEntregas”.“Cancelado”, “tbEntregas”.“Comentarios” FROM “tbEntregas”, “tbPeladoras” WHERE “tbEntregas”.“PeladoraID” = “tbPeladoras”.“PeladoraID” AND “tbPeladoras”.“PeladoraID” = :PeladoraID AND “tbEntregas”.“Soles” > 0 AND “tbEntregas”.“Cancelado” = FALSE

I have made the report but I cannot create the total amount of invoices.
I tried to add a text box at the footer but with no success.

I’m using version 6.4.6.2 and zorin os (based on ubuntu)

Hope someone can come to help me.

Thanks!

[RobertG]

You could use a Text Box in the Group Footer or the Report Footer.

Draw the box and switch to Properties → Data (will be shown on the sidebar).
Chose Data Field Type → Function.
Chose Data field → "your field for the accumulation" .
Chose Function → Accumulation.
Scope will be chosen automatically. If the field is part of the Report Footer the Scope will be the whole report.

[ukhupacha]

Hello RobertG,

I have tried to do that but with no sucess, both in the Report Footer of the Report Footer.
But the inserted Text Box does not have the property of of Data Field Type.

I think it is because I’m not using the Oracle based reporter, instead for reports it opens LO Writer or one of the extensions for libreoffice base.
I have not the answer yet

[RobertG]

You will need to install the report builder. It is also available for Ubuntu, but won’t be installed together with all other components of LO.

The old legacy report isn’t available on other systems for creating a report. I could only execute such a report on my system (OpenSUSE 15.2 64bit rpm Linux).

Other possibility: You have to accumulate the values in the query. So you will get the sum and print it out in the header of your report. Think the legacy report will allow 3 groups in the header, isn’t it?

I don’t know which is the field you wanted to accumulate, so here one example:

SELECT "a".*, 
(SELECT SUM("amount") FROM "Table_Invoice" 
   WHERE "Invoice_ID" = "a"."Invoice_ID") 
   AS "Total" 
FROM "Table_Invoice" AS "a"