Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Yearly total verses Monthly total

    Hi guys I am using report services for this. I got a matrix set up and that wil shiow the department reports, the boss wants to see the monthly totals per field and the yearly totals per field also. Here is what I have so far but it only gives me the month and not the year. so for the Exclusions lets say that month there we 11 excluions but through out the year could be 112. Does that make sense???


    Code:
    SELECT     SUM(Exclusions) AS Exclusion, SUM(Arrest) AS Arrests, SUM(Criminal) AS Criminals, SUM(Regulatory) AS Regulartory, SUM(Internal) AS Intern, 
                          SUM(GrandTheft) AS GrandThft, SUM(PettyTheft) AS PettyThft, SUM(AppFoundProp) AS Appfound, SUM(DefraudInkeeper) AS Defrauding, 
                          SUM(Counterfeits) AS Counterfeit, SUM(Vandalism) AS Vandalism, SUM(Other) AS Other, SUM(OpenSlotDoor) AS OpSltDr, SUM(EmpViolation) 
                          AS EmpVio, SUM(GamingViolation) AS Gviolation, SUM(Drug_Alcohol) AS D_A, SUM(PatronDispute) AS Patron, Start_Date, End_Date
    FROM         GamingInspectors
    GROUP BY Start_Date, End_Date
    HAVING      (Start_Date = CONVERT(DATETIME, '2008-12-01 00:00:00', 102)) AND (End_Date = CONVERT(DATETIME, '2008-12-31 00:00:00', 102))

  2. #2
    Join Date
    Aug 2006
    Location
    San Francisco, CA
    Posts
    136
    If you are using SQL Server Reporting Service you can display all the monthly info in a group and have RS add up the monthly totals (monthly group) in another yearly group.

    Using Expressions in Reporting Services

Posting Permissions

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