I'm in need of some assistance with trying to get a query, or actually end result would be as a report, to display cumulative sum of counts for each month, rolling months.
And of course would like to have the titles/labels display the month name as the label.
I was able to find some information however I am getting an error and I am unsure how to combine what I've found to display in the final result with the month labels.
Here is what I have so far and I am getting an #Error:
Which I've come across that the MS article this came from http://support.microsoft.com/kb/290136 is incorrect in the first placeCode:RunTot: DSum(Count([attribute_PersonMembership].[PersonId]),"DatePart('mm',[PaymentDate])<=" & [SortMonth] & " And DatePart('yyyy',[PaymentDate])<=" & [Year] & "")
Here's the full query:
Attempt at a cross-tab query and I get "Data Type Mismatch" error:Code:SELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType, Format([PaymentDate],"yyyy") AS [Year], Format([PaymentDate],"mm") AS SortMonth, Format([PaymentDate],"mmm") AS [Month], DSum(Count([attribute_PersonMembership].[PersonId]),"DatePart('mm',[PaymentDate])<=" & [SortMonth] & " And DatePart('yyyy',[PaymentDate])<=" & [Year] & "") AS RunTot, Count(attribute_PersonMembership.PersonId) AS NoOfMembershipsFROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId WHERE (((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate)>=DateAdd("yyyy",-1,Date())) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4)) GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description, Format([PaymentDate],"yyyy"), Format([PaymentDate],"mm"), Format([PaymentDate],"mmm") HAVING (((lookup_MemberTypes.MemberGroup)="Regular Member"));
Code:TRANSFORM DSum(Count([attribute_PersonMembership].[PersonId]),"DatePart('mm',[PaymentDate])<=" & [SortMonth] & " And DatePart('yyyy',[PaymentDate])<=" & [Year] & "") AS RunTotSELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType, Format([PaymentDate],"yyyy") AS [Year], Format([PaymentDate],"mm") AS SortMonth FROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId WHERE (((lookup_MemberTypes.MemberGroup)="Regular Member") AND ((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate)>=DateAdd("yyyy",-1,Date())) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4)) GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description, Format([PaymentDate],"yyyy"), Format([PaymentDate],"mm") ORDER BY Format([PaymentDate],"mm") PIVOT Format([PaymentDate],"mmm");
With the basic cross-tab query:
I get this which is the way the report needs to display.Code:TRANSFORM Count(attribute_PersonMembership.PersonId) AS CountOfPersonIdSELECT lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description AS MemberType FROM lookup_MemberTypes INNER JOIN attribute_PersonMembership ON lookup_MemberTypes.Id = attribute_PersonMembership.MembershipTypeId WHERE (((lookup_MemberTypes.MemberGroup)="Regular Member") AND ((lookup_MemberTypes.IsIndividual)<>-1) AND ((attribute_PersonMembership.PaymentDate)>=DateAdd("yyyy",-1,Date())) AND ((lookup_MemberTypes.Id)=1 Or (lookup_MemberTypes.Id)=2 Or (lookup_MemberTypes.Id)=4)) GROUP BY lookup_MemberTypes.MemberGroup, lookup_MemberTypes.Description ORDER BY lookup_MemberTypes.Description PIVOT Format([PaymentDate],"mmm");
I would like a Report to display this BUT cumulative sums of the count AND displaying the rolling month labels each month in order:
This is showing the total counts for each month separately for the last 12 months.
So May should be the sum of Apr (122) + May (74). May column needs to show 122+74 = 196 and so on.
And next month it should start with May - Apr and so on.
I'm uploading the db.Code:
MemberGroup MemberType Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Regular Member First Family 122 74 14955 8801 2321 1649 1775 849 330 382 333 209 Regular Member Introductory 131 95 924 823 521 433 507 312 166 274 238 232 Regular Member Subsequent Family 100 80 6698 4540 1228 731 875 454 185 194 191 157
The 3 queries and the report (which is in WIP for formatting) are in the db.
I'm thinking do the summing for each month in the report design?
Also, the report will always report from Jul (previous year) - Jun (current year).