Dynamic Group Sorting

• jbriley88

  SSC Rookie

  Points: 39

  More actions

  May 17, 2017 at 7:30 am

  #323552

  I am attempting to build dynamic group sorting into a report I am working on. I have one parameter "SortGroup1" which lists the fields available to sort by for the group, and one parameter for the order in which to Sort "Group1Order".

  The first paramter gives two options with text values to sort by. The second paramter's values are 1 for "Ascending" and -1 for "Descending" I want to multiply a numeric total by this field only if the option chosen has a numeric value.

  There is possibly a better way to do what I'm wanting to do, so I'm open to suggestions. Below is the sort expression I am using for Group1
  =switch(
    Fields(Parameters!SortGroup1.Value).Value="ProdHrs",
        Sum(
            iif(
                IsNumeric(Fields(Parameters!SortGroup1.Value).Value)=1,
                Fields(Parameters!SortGroup1.Value).Value,
                0
            )
        ) * Parameters!Group1Order.Value,
    Fields(Parameters!SortGroup1.Value).Value="EquipmentShortDesc",
        Fields!EquipmentShortDesc.Value,
    true,
        nothing
)

  The error being returned when running the report is:
  "The sort expression for the grouping 'Group1' contains an error: Input string was not in a correct format.