Sum of group with criteria

Posted on 2011-05-10
Last Modified: 2012-05-11
I'm using Visual Studio 2005

I've attached a print out of the report I'm working on.  I would like to have totals of the shifts(for the day) below the totals for the day.  I've tried several things(add a group, add a subreport, summary field...) and have struck out so far.  I have a group #1 for the date and a group #2 for the Sorter(machine).  Then I have the Sums for the day showing in the group #1 footer. I also want the totals for each shift to show here.  What can I try to accomplish this?

Question by:coperations07
    LVL 100

    Expert Comment

    The easiest way to accomplish this is to use a subreport that just produces the summary you want.

    Another way would be to use variables for each shift.

    Can you show what you want the totals per shift to look like?


    Author Comment

    Something like this for each day/page....

    Totals      100,000      500      400      50      2500      96,550
    Shift 1      50,000      400      100      20      1500      47,980
    Shift 2      30,000      100      100      10      500      29,290
    Shift 3      20,000      0      200      20      500      19,280
    LVL 100

    Accepted Solution

    Are the number of columns fixed?

    Unless you use a subreport to get it you will need to create formulas for each total for display and calculation.

    Do the columns change between runs?

    One away to do this is to create formulas like
    If ({ShiftField} = 1) then

    You can then summarize the formula in the report footer.

    You would need a formula like above for each total field then a summary field to display them.


    Author Comment

    The number of columns is fixed.  The column names don't change...

    I'm trying to get the summary in the group1 footer (date)

    There's  a query on an Access database that I'm using as the datasource.  I included it in the code portion below.
    SELECT tbl_sorter_perf.Date, Max(tbl_sorter_perf.Shift_Feed) AS MaxOfShift_Feed, Max(tbl_sorter_perf.Shift_Excess) AS MaxOfShift_Excess, Max(tbl_sorter_perf.Shift_All_Drop) AS MaxOfShift_All_Drop, Max(tbl_sorter_perf.Shift_No_Read) AS MaxOfShift_No_Read, Max(tbl_sorter_perf.Shift_Blk_Cht) AS MaxOfShift_Blk_Cht, Max(tbl_sorter_perf.Shift_Net) AS MaxOfShift_Net, tbl_sorter_perf.SORTID, tbl_sorter_perf.Shift
    FROM tbl_sorter_perf
    GROUP BY tbl_sorter_perf.Date, tbl_sorter_perf.SORTID, tbl_sorter_perf.Shift;

    Open in new window

    LVL 100

    Expert Comment

    If you need a summary,
    Right click the field
    Click INSERT --> SUMMARY
    Put it in the group1 footer


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Suggested Solutions

    As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now