Alpha Video Training
Results 1 to 13 of 13

Thread: calculate nested group totals

  1. #1
    Member
    Real Name
    Derek Caldwell
    Join Date
    Aug 2008
    Posts
    11

    Default calculate nested group totals

    How do I calculate multiple group totals and a grand total on a report? I tried the total(tablename, GRP ->) but can't get it to work.

  2. #2
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: calculate nested group totals

    Hi Derek,

    First, welcome to the forums. Without more information / sample database it will be hard to answer such a general question...but instead, have you allowed the Genie to create the calculation for you?? Drag whatever field you want totaled to the footer region of the area you want totalled and then when the popup genie screen appears, choose Total. You can do this in a detail footer, group footer, report footer......
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  3. #3
    Member
    Real Name
    Derek Caldwell
    Join Date
    Aug 2008
    Posts
    11

    Default Re: calculate nested group totals

    Mike,

    I figured it out. Thanks for the reply.

  4. #4
    Member
    Real Name
    Derek Caldwell
    Join Date
    Aug 2008
    Posts
    11

    Default Re: calculate nested group totals

    Mike,

    I jumped ahead of myself. My groupins don't see mto be matching. I've attached a screen shot of a sample PO and a sample QBuilder report against that PO. My questions is why does my QB PO show two separate items for the same job on a single PO (as it should) BUT my report displays by group the correct job but incorrect groupings for the items. Bascially, I need a item report grouped by job and the details of the item on the PO needes to be displayed. So in the case of the attached examples the group should have one job, two groupings for each items and a single details piece in each group. Can you take a look at this?

    Regards,

    Derek

    p.s. I tried attaching the report definition for that report but this forum wouldn't allow it. If needed Ican email it to you.

  5. #5
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: calculate nested group totals

    Derek,
    I'm not completely understanding yet.

    one job, two groupings for each items and a single details piece in each group
    Please give an Exact want. Right now it sounds like you want all the data in the screenshots to be one job whereas now it appears to be at least 3 separate ones...etc....

    IOW, what should the report show--use actual data to describe. Maybe after this is done, I might need the report definition and will PM my email to you if necessary.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  6. #6
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,561

    Default Re: calculate nested group totals

    Quote Originally Posted by derekcaldwell View Post
    p.s. I tried attaching the report definition for that report but this forum wouldn't allow it. If needed Ican email it to you.
    Derek

    Zip the qrbr file and attach - the messageboard allows zip files...
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  7. #7
    Member
    Real Name
    Derek Caldwell
    Join Date
    Aug 2008
    Posts
    11

    Default Re: calculate nested group totals

    attached is the zip file. thanks for the phone message also.

  8. #8
    Member
    Real Name
    Derek Caldwell
    Join Date
    Aug 2008
    Posts
    11

    Default Re: calculate nested group totals

    the jobs are grouping correctly it's the items that aren't grouping. I need a item report grouped by job then item. The details of the report are the actual item descriptions from the PO. So if there is a job with 10 line items - 5 for item a and 5 for item b the report would be grouped by JOB 1 Item a - then the details would be item a-1,item a-2,item a-3,item a-4,item a-5. Then the same for item b - the details are item b-1, item b-2, item b-3, item b-4, item b-5.

  9. #9
    Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,561

    Default Re: calculate nested group totals

    Quote Originally Posted by derekcaldwell View Post
    the jobs are grouping correctly it's the items that aren't grouping.
    You are sorting by name and then by sales line due date. Change the sort to be by item instead of due date.

    Edit the report and navigate - report - select records - in the order box change to the field that you want to sort on....
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  10. #10
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: calculate nested group totals

    Hi Derek,

    Am guessing I'm just a bit dense today.... :)

    From the screenshot what is wanted. Every item I see is different and see no way to group them

    Is it the Vendors you want them grouped by??

    Tell me what it should look like.

    Attachment 18441
    Last edited by MikeC; 08-26-2008 at 06:36 PM. Reason: spelling
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  11. #11
    Member
    Real Name
    Stuart Clark
    Join Date
    Sep 2008
    Posts
    2

    Default Re: calculate nested group totals

    We're trying to do something similar. I didn't know whether or not I should start a new thread.

    The attached report displays each vendor with its invoices under the vendor name heading. Each invoice consists of multiple lines, each representing an item. On the right side there is a column "Item Price * Qty," which has a value for each item. We want to add two calculated values. The first is the invoice total, which should be the sum of the "Item Price * Qty" values per invoice. The second should be the vendor total, which should be the sum of the invoice totals for all invoices listed under each vendor.

    I've tried placing a calculated field in various level of the report. No matter where I put the calculated field, I got a grand total, rather than the total for the invoice or the vendor.

  12. #12
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: calculate nested group totals

    Stuart,
    Get rid of all calculations you are not using first off--creates conflicts (report errored out).

    Then try this in your invoice group footer--it totals all invoices for each vendor....

    total(Invoicelineamount,GRP->Invoice)
    Last edited by MikeC; 09-02-2008 at 08:18 PM.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  13. #13
    Member
    Real Name
    Stuart Clark
    Join Date
    Sep 2008
    Posts
    2

    Default Re: calculate nested group totals

    MikeC,

    Thanks for the help -- worked like a charm.

    -Stuart

Similar Threads

  1. calculate sub totals
    By Fred Beck in forum Alpha Five Version 4
    Replies: 2
    Last Post: 10-06-2003, 03:30 PM
  2. Group Totals
    By BWilliams in forum Alpha Five Version 5
    Replies: 2
    Last Post: 10-06-2003, 06:57 AM
  3. Group Totals in Report Footer
    By Alan Lucas in forum Alpha Five Version 5
    Replies: 8
    Last Post: 05-25-2003, 04:59 PM
  4. Pre Process of Group Totals
    By Chuck Kirkpatrick in forum Alpha Five Version 5
    Replies: 1
    Last Post: 03-31-2003, 03:31 PM
  5. Sorting Report On Group Totals
    By Chuck Kirkpatrick in forum Alpha Five Version 5
    Replies: 2
    Last Post: 03-07-2003, 06:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •