SSRS matrix group by error

Question

Post reply

SSRS matrix group by error

SqlServerNinja

Ten Centuries

Points: 1238
More actions
November 18, 2013 at 4:19 pm

#300276

I have an ssrs matrix with total in rows and months as columns .. something like this .
Jan Feb March
Total1
Total2
I can choose a month as a parameter and my matrix shows the results for the month and the two months before the current month.
When I choose August :
I can see June July August
When I choose September :
I can see July August September
But when I choose October
I see October August September
My client wants to see it as August September October
I don't know the reason for this behavior .But can anyone help me with this ??
I tried doing the sort for the tablix as follows :
=(Fields!Month.Value)
Month is a number like 1 for Jan, 2 for Feb. I add it as a sorting expression but I dont see any change in the sorting.
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

mister.magoo SSC-Forever Points: 47174 More actions · Answer 1

Where did you put that sort? On the column group? It needs to be on the column group...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

lynns Say Hey Kid Points: 695 More actions · Answer 2

It appears as if Fields!Month.Value is a text string, since it is sorting alphabetically. Do you have a corresponding date field which would allow you to pull the month using DatePart then convert it to and integer for proper sorting?

SqlServerNinja Ten Centuries Points: 1238 More actions · Answer 3

SqlServerNinja

Ten Centuries

Points: 1238

November 19, 2013 at 11:52 am

#1667523

Thanks !! That works !

mister.magoo SSC-Forever Points: 47174 More actions · Answer 4

So this:

SqlServerNinja (11/18/2013)
Month is a number like 1 for Jan, 2 for Feb. ...

was not correct?:crazy:

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);

SqlServerNinja Ten Centuries Points: 1238 More actions · Answer 5

lynns (11/19/2013)
It appears as if Fields!Month.Value is a text string, since it is sorting alphabetically. Do you have a corresponding date field which would allow you to pull the month using DatePart then convert it to and integer for proper sorting?

Yes ! I have a date part , which I have used for sorting ! Thanks !

lynns Say Hey Kid Points: 695 More actions · Answer 6

This falls into the "make it work" category.....Sometimes they look like numbers but sort like text. I just convert to integer to make sure the sort works correctly.