I have a table and want to sort the 1st column which is called "WorkType" in a particular order. At present it looks like this. Type Admin Tasks Leave & Public Holiday Projects Idea/IA Development BAU Support Enhancement/Small Request GAP I am wanting it to look like this order Type Project BAU Support Enhancement/Small Request Idea/IA Development Leave and Public Holidays Admin Task GAP

Do you have any other column coming from query which has Order related information?If not, you can write IIF() expression to define order provided all these are Same value fields (static).Thanks, Jayesh > Vote for answer if it helps you.

No other columns have any order. You say use an iff() expression. But I dont know how to write if.. Below is what Ineed to obvouisy the Syntax is no where near correct=iif(Fields!WorkType.Value = "Project" Display 1st=iif(Fields!WorkType.Value = "BAU Support" Display 2nd

Using IIF=IIF((Fields!WorkType.Value= "Project", 1,IIF(Fields!WorkType.Value= "BAU", 2,IIF(Fields!WorkType.Value= "Enhancement/Small Request",3,4))Use this as calculated field or directly as sorting expression.You can use switch also similarly.Thanks, Jayesh > Vote for answer if it helps you.

Thanks for the advice but did'nt work. This is what I tried as an expression. There is a red underline on the 1st comma after the double quotes between the ",1, Syntax Error=IIF((Fields!WorkType.Value= "Project", 1, IIF(Fields!WorkType.Value= "BAU Support",2,IIF(Fields!WorkType.Value= "Enhancement/Small Request",3,IIF(Fields!WorkType.Value= "Idea/IA Development",4,IIF(Fields!WorkType.Value= "Leave and Public Holidays",5,IIF(Fields!WorkType.Value= "GAP",6,IIF(Fields!WorkType.Value= "Admin Tasks",7))Its 99% there just one little thing. Any help would go a long way. Can I also thank you for the help so far. This was the error message [rsCompilerErrorInCode] There is an error on line 17 of custom code: [BC42104] Variable 'rtnvalue' is used before it has been assigned a value. A null reference exception could result at runtime.[rsCompilerErrorInExpression] The Value expression for the textbox WorkType_1 contains an error: [BC30198] ')' expected.

you can also use a switch statement: =Switch(Fields!WorkType.Value= "Project" , 1 ,Fields!WorkType.Value= "BAU Support" , 2 ,Fields!WorkType.Value= "Enhancement/Small Request" , 3 ,Fields!WorkType.Value= "Idea/IA Development" , 4 ,Fields!WorkType.Value= "Leave and Public Holidays" , 5 ,Fields!WorkType.Value= "GAP" , 6 ,Fields!WorkType.Value= "Admin Tasks" , 7 ) Make sure you put this in the Sorting of the group in the Group Properties dialog and set the sort order to be "A to Z". it should be your row group that you need to put this in.Hope this helps,--JoshJosh George LiveLogic, LLC

Josh this is in SSRS 2005 not sure where you mean. I was just right clicking on the column then expression. If I right click the box and click properties there is an interactive Sort tab. Then an Add a Interactive Sort Action to this Textbox, right below that is a Sort Expression box.Is that the one.

I just tried to run your expression and it removes the current values from the Type column and replaces them with numbers. So the switch function switches values, that is not what I am wanting to do. It is really simple and straight forward what I am wanting to do.I will explain again what I want. So below here is the report that is produced Fig 1.1. All I want to do is re sort the "Type" column in a particular order please see fig 1.2 for the order I want them to be displayed as in the report. Fig1.1 NEW_Team Utilisation Type Target % Actual % Status Hours Comments Admin Tasks <3% 5.9% 1119.6 BAU Support 15-20% 25.% 4766.8 Enhancement/Small Request 15-20% 2.8% 527.2 GAP 0% 9.4% 1780.2 Idea/IA Development <10 4.2% 807.4 Leave & Public Holiday 8-10% 36.% 6856.1 Projects 40-50% 16.9% 3214.4 Fig 1.2 NEW_Team Utilisation Type Target % Actual % Status Hours Comments Projects 40-50% 5.9% 1119.6 BAU Support 15-20% 25.% 4766.8 Enhancement/Small Request 15-20% 2.8% 527.2 Idea/IA Development <10 9.4% 1780.2 Leave & Public Holiday 8-10% 4.2% 807.4 GAP 0% 36.% 6856.1 Admin Tasks <3% 16.9% 3214.4 All I want to do is change the order in which the report displays the Type Column

Hi Greg,Josh is right.We can follow these steps to do so:1.Right-click on textbox "Type"2.Select "Properties"3.Go to "Interactive Sort" tab.4.Check "Add an Interactive sort action to this textbox"5.Click "Fx" button, and then apply the following query:=Switch(Fields!Type.Value= "Project" ,1 ,Fields!Type.Value= "BAU Support" , 2 ,Fields!Type.Value= "Enhancement/Small Request" , 3 ,Fields!Type.Value= "Idea/IA Development" , 4 ,Fields!Type.Value= "Leave & Public Holiday" , 5 ,Fields!Type.Value= "GAP" , 6 ,Fields!Type.Value= "Admin Tasks" , 7 )6.Click "OK" to apply.Now, we can click "Type" to sort type in the specified order.Or, we can follow these steps:1.In design mode, go to "Data" page.2.Click ellipsis button beside "Dataset" dropdownlist3.In the dialog "Dataset", go to "Fields" tab.4.Add a new field manually.Move mouse to the blank "Field Name" under the last valued field. Type a name such as "OrderBy". Go to "Type" option in the same row, selected "Calaculated Field". Then go to "Value", type the same expression we provided in step5 in the above solution.5.Click OK to apply.6.Now, set the "Interactive Sort" of "Type" textbox to be sort by:=Fields!OrderBy.ValueIf you have any more questions, please feel free to ask.Thanks,Jin ChenJin Chen - MSFT

Jin,Thanks for clarifying. I don't have 2005 installed on this box and was looking at 2008 when I wrote it.Greg,The goal behind this method is to set the grouping sorting order based upon an expression. So you want to create your table like you normally would and then add this to the deatils grouping "Interactive Sort". On the "Interactive Sort" tab you should have a place to select a field to sort on. Beside that you should have the expression button which you can enter the switch statement into.Hope this helps out,--JoshJosh George LiveLogic, LLC

Josh it did'nt work. Thank you for your detailed instructions, I carried out the following up to step 6, then when you mentioned "Now, we can click "Type" to sort type in the specified order." Where do I do this part ? In the Type field I have the following =Fields!Type.Value We can follow these steps to do so:1.Right-click on textbox "Type"2.Select "Properties"3.Go to "Interactive Sort" tab.4.Check "Add an Interactive sort action to this textbox"5.Click "Fx" button, and then apply the following query:=Switch(Fields!Type.Value= "Project" ,1 ,Fields!Type.Value= "BAU Support" , 2 ,Fields!Type.Value= "Enhancement/Small Request" , 3 ,Fields!Type.Value= "Idea/IA Development" , 4 ,Fields!Type.Value= "Leave & Public Holiday" , 5 ,Fields!Type.Value= "GAP" , 6 ,Fields!Type.Value= "Admin Tasks" , 7 )6.Click "OK" to apply.

Hi Greg,I have uploaded a sample report here:http://cid-3c7e963ff6ccd974.skydrive.live.com/self.aspx/.Public/CustomizedSortOrder.zipThat may help you to understand the two solutions.In my previous post, I have posted two solutions. We can use any one of them to accomplish the target.For you last question "Where do I do this part", we should do it:Preview the report, there will have a Sort Mark beside "Type" text. We can click on it to sort the column.If we want to sort the table directly(not an Interactive Sort), we can use the same expression in the "Sort" property of the dataset to do so.For more information, please see:How to: Add Interactive Sort to a Table or Matrix (Reporting Services): http://msdn.microsoft.com/en-us/library/cc627509(SQL.90).aspxHow to: Sort Data in a Matrix (Report Designer): http://msdn.microsoft.com/en-us/library/ms157462(SQL.90).aspxThanks,Jin Chen Jin Chen - MSFT

I will have to look at your links tonight as work have them blocked.I was going to carry out the following but don't understand it Or, we can follow these steps:1.In design mode, go to "Data" page. Where is the data page and what is it. Do you mean go to the report data menu2.Click ellipsis button beside "Dataset" dropdownlist. Do you mean right click dataset and add a database field ?3.In the dialog "Dataset", go to "Fields" tab. There isnt a dataset field in SSRS 20054.Add a new field manually.Move mouse to the blank "Field Name" under the last valued field. Type a name such as "OrderBy". Go to "Type" option in the same row, selected "Calaculated Field". Then go to "Value", type the same expression we provided in step5 in the above solution.5.Click OK to apply.6.Now, set the "Interactive Sort" of "Type" textbox to be sort by:=Fields!OrderBy.Value

Basically the easiest way for me was to right click on the table > Props > Sorting Tab >Expression =Switch(Fields!WorkType.Value= "Project" ,1 ,Fields!WorkType.Value= "BAU Support" , 2 ,Fields!WorkType.Value= "Enhancement/Small Request" , 3 ,Fields!WorkType.Value= "Idea/IA Development" , 4 ,Fields!WorkType.Value= "Leave & Public Holiday" , 5 ,Fields!WorkType.Value= "GAP" , 6 ,Fields!WorkType.Value= "Admin Tasks" , 7All works perfect. Thank yoy all for your help.Greg

Hi Greg Walsh, As per my understanding plz do it in sql query like case Type when 'Projects' then 'A' when 'BAU support' then 'B' so on .. end as DisplayOrder. and then put the sort order of the report group as 'DisplayOrder' field and select 'A-Z' in Report It will solve the problem ...Do let me know I did'nt understand ur problem. Best of Luck Mark answer if I m right or helpfull.