July 12, 2011 at 10:31 am
I have a matrix that lays things out according to an aging schedule - current, 30, 60, 90, etc. It ages the accounts receivables.
I've tried ordering based on this column, but 120, 150, 30, 60, 90, current is the result. It's doing it correctly technically, but I want to list Current, 30, 60, 90, 120, 150 in that order. So I tried an switch statement in the order for that group:
=switch(
Fields!AR_Bucket_Name.Value = "Current", 0,
Fields!AR_Bucket_Name.Value = "30Days", 1,
Fields!AR_Bucket_Name.Value = "60Days", 2,
Fields!AR_Bucket_Name.Value = "90Days", 3,
Fields!AR_Bucket_Name.Value = "120Days", 4,
Fields!AR_Bucket_Name.Value = "150Days", 5)
but it changes based on which facility I've chosen because of the order of the data in the query. Some facilities don't have certain buckets, so it either shows last, or in some other order. I have the table it's referencing in my query and it's using a description field to show the column name. Am I missing something?
July 26, 2011 at 5:48 am
mcushing (7/12/2011)
...but it changes based on which facility I've chosen because of the order of the data in the query. Some facilities don't have certain buckets, so it either shows last, or in some other order. I have the table it's referencing in my query and it's using a description field to show the column name. Am I missing something?
Conditionally check if it exists with an IIF statement, samples here: http://msdn.microsoft.com/en-us/library/ms157328.aspx
I have tried this in the past with varying results and in the end used ROWNUMBER in the source dataset to alleviate such issues.
Chris Hays blog has some very useful tips: http://blogs.msdn.com/b/chrishays/
gsc_dba
July 26, 2011 at 5:50 am
Convert the column to an int and the order will work... and be faster than the switch.
July 26, 2011 at 12:18 pm
If you have any unique column in that table means add that column in your select statement result and in the group properties add that unique column to order by.You will get the result as you expect.
30,60,90,120 etc...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply