asked
Custom Sorting in Matrix report on particular column
I have created a matrix report. I have a column 'Model' and values are (Classic, semi classic, Grand, twin , full, king , queen). Requirement was to split these values in columns. I created column group for that and write expression like that: **=Switch(Fields!model.Value = " ", "NULL" , Fields!model.Value = "classic", 1, Fields!model.Value = "semi classic", 1,Fields!model.Value = "Grand", 1,Fields!model.Value = "Twin", 1,Fields!model.Value = "Full", 1,Fields!model.Value = "King", 1,Fields!model.Value = "Queen", 1)** Now I have to sort these models into particular order ( not in A-Z or Z-A). order should be : **twin, Grand, Queen, Classic , Full, semi classic, king** Can some one help me in this how to twist expression or some property. Thanks
I am not sure if what I have bouncing around in my head is a viable option for you, but do you have the ability to add a table or a column to an existing table?
As @Mrs_Fatherjack mentioned, you can write a case in your query. You can also create a lookup table with the values and their orders and then use a select with join to your lookup table to return expected order - this will be probably the easiest way for maintaining future requirements to change the order. But if there could be some reason, you cannot modify the query or you rely on result of stored proc, which you cannot affect or for whatever reason, I would write a custom Code in the Report propertis: Public Shared Function GetColumnSortOrder(val As Object, unknownOnEnd As Boolean, ParamArray valuesOrder As Object()) As Integer For i As Integer = 0 To valuesOrder.Length - 1 If val.Equals(valuesOrder(i)) Then Return i End If Next If unknownOnEnd = True Then Return Integer.MaxValue Else Return Integer.MinValue End If End Function Then easily use a below code in the Sort Options of the group: **`=Code.GetColumnSortOrder(Fields!model.Value, true, "twin", "Grand", "Queen", "Classic" , "Full", "semi classic", "king")`** The above function returns position of the Field value within the list of values provided, so in general the order of values defines the final order. There is also parameter whether unknown values (values not in list) should be orderd on the end (true) or on the beginning (false). Of course, you can even use even your solution with switch, you only need to return correct order values: `=Switch(Fields!model.Value = "classic", 4, Fields!model.Value = "semi classic", 6,Fields!model.Value = "Grand", 2,Fields!model.Value = "Twin", 1,Fields!model.Value = "Full", 5,Fields!model.Value = "King", 7,Fields!model.Value = "Queen", 3)` However whatever value which is not handled by the switch will return `Nothing` and order it at the beginning. If you would like to have such values at the end then you will have to make the expression more complex: `=IIF(IsNothing(Switch(Fields!model.Value = "classic", 4, Fields!model.Value = "semi classic", 6,Fields!model.Value = "Grand", 2,Fields!model.Value = "Twin", 1,Fields!model.Value = "Full", 5,Fields!model.Value = "King", 7,Fields!model.Value = "Queen", 3)), 9999, Switch(Fields!model.Value = "classic", 4, Fields!model.Value = "semi classic", 6,Fields!model.Value = "Grand", 2,Fields!model.Value = "Twin", 1,Fields!model.Value = "Full", 5,Fields!model.Value = "King", 7,Fields!model.Value = "Queen", 3))`
I would add a case statement to the query adding in the sort order e.g. select . . . . case when Model = 'twin' then 1 when Model = 'Grand' then 2 when Model = 'Classic' then 3 when Model = 'Full' then 4 when Model = 'Semi Classic' then 5 when Model = 'King' then 6 else 7 end as [SortOrder] twin, Grand, Queen, Classic , Full, semi classic, king and then sort on this new field.
thanks Pavel and Mrs_Fatherjack,,,,
4 People are following this question.
