Monday, March 12, 2012

Matrix grouping

I have a report with the Month attribute as the column group and specific measures as the row groupings. Now, here's my delima. The months are not being displayed in order. They look like this:

Jan Feb May Jun Jul Aug Sep Oct Nov Dec Mar Total

Why is it doing this?

Here's a view of my matrix in layout view...

Month_Name

Fatal Crashes =sum(fatal_crashes.value)

Injury Crashes =sum(injury_crashes.value)

Property Damage =sum(Prop_Damage.value)

Total Crashes =sum(Total_crashes.value)

Chicago Crashes =sum(Chicago_crashes.value)

Crashes Located =sum(located_crashes.value)

% Located =sum(percent_located.value)

any help would be greatly appreciated!! THANKS!

Try changing the sorting of the matrix to use the month number if available.

http://msdn2.microsoft.com/en-us/library/aa179319(SQL.80).aspx

If it's not available, you may be able to build an ugly IIF statement to provide the row numbers, or use a function to return them.

cheers,

Andrew

|||

I tried to hardcode in a switch statement such as this one :

=Switch((Fields!Month_Name.Value) = "Jan", 1, Fields!Month_Name.Value = "Feb", 2, Fields!Month_Name.Value = "Mar", 3, Fields!Month_Name.Value = "April", 4, Fields!Month_Name.Value= "May",5, Fields!Month_Name.Value= "Jun",6, Fields!Month_Name.Value= "Jul",7, Fields!Month_Name.Value= "Aug",8, Fields!Month_Name.Value="Sep",9, Fields!Month_Name.Value="Oct",10, Fields!Month_Name.Value="Nov",11, Fields!Month_Name.Value="Dec",12)

But it didnt change anything. I also changed the sort to Fields!Month_Name.key and Fields!Month_Name.Level and nothing changed as well. I wonder if its because i added extra rows to the matrix ...but still it should work, i have added extra columns before and never had this problem.

No comments:

Post a Comment