Re: [libreoffice-users] How to calculate a grand total for entire report

On 12/30/2012 04:13 PM, Dan Lewis wrote:
> On 12/30/2012 12:34 AM, Yahoo Account wrote:
> > I am using LO 3.6.4 in Mac OS X 10.8.2.
> >
> > In Base I have a very simple, single table assets database.  In a 
> > report, I am trying to calculate a grand total for all records.
> >
> > I have a text field for each record in the report which has a 
> > calculated amount for the record (i.e. in Report Detail there is a 
> > field called ItemTotalValue which is bound to a function which has 
> > the formula [quantity]*[price]).
> >
> > The report runs and shows a value for ItemTotalValue for each record 
> > correctly.  What I want to do is get a grand total which in effect 
> > accumulates ItemTotalValue for the whole report.
> >
> > I have successfully created a field which accumulates the price field 
> > (which is in the table) but, I can't figure out how to accumulate a 
> > field that is created in the report (i.e. it's not in the source table).
> >
> > I have created a function at the Report level called "Calculate Grand 
> > Total Value" which has the formula [Calculate Grand Total 
> > Value]+[ItemTotalValue].  I then bound a text box in the report 
> > footer to "Calculate Grand Total Value".  But the report footer is a 
> > blank.  So, "Calculate Grand Total Value" seems to be empty.  I can't 
> > figure out how to get it working.
> >
> > Garry
> >
> > P.S.  I can't find any rules for posting to this list.  Can any one 
> > advise where the rules are ?
>      Your posting of your question was what it should be: you stated 
> your problem clearly and contained enough information for someone to 
> answer it if they have the knowledge needed to do so.
>      I think that you are making your report more complex than it 
> needs to be. The secret is in the plan and design of the database. It 
> appears that you are using the table as the source for the report. I 
> suggest that you create a query based upon the table and then create 
> the report based upon the query.
>      The query contains three fields, two of which come directly from 
> the table: quantity and price. The third field is "quantity" * 
> "price". If you create the query in SQL View, this is the SQL that 
> will create the query (I chose to use Table1 as the name of the table. 
> If you use a different name for your table, replace "Table1" with your 
> table's name in double quotes.)
>      This query when run  has the values you want for the report: a 
> column containing the quantities for each item, a column containing 
> the prices for the items, and a column containing of the quantity for 
> each item times the price of each.
>      In the report, you can use Report Builder's accumulation function 
> to add all the products in the report footer.
>      This is rather simplistic but it should get you started.
>
> --Dan
>        SELECT "quanity", "price", "quanity" * "price" FROM "Table1"
Slight error in spelling and adding an alias!!!

SELECT "quantity", "price", "quantity" * "price" AS "Subtotal" FROM "Table1"

--Dan

--
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted