01-16-09, 19:27 #1Registered User
- Join Date
- Feb 2004
- Alpine Califormia
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???
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))
01-16-09, 20:40 #2Registered User
- Join Date
- Aug 2006
- San Francisco, CA
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