Sorting String Numeric Numbers in SSRS Matrix

0
votes

I have a column in my matrix table that is a varchar containing numeric values (stepName). In the report, it prints off as 1,10,11... 2,20,21,22... etc instead of 1,2.. 10, 20.

I found another question that suggested either converting the varchar values to int and then sorting, or adding a datatype numeric column and using that to sort. (sorting string numeric values in SSRS 2008)

I can't convert the varchar values to int as I have about 50/50 varchar/numeric values. I actually have a numeric sequence column, but when I tried to use this as a sort both in the query, the column tablix properties and the list tablix properties, it still didn't sort. What am I missing? Thank you!

select hrss.salaryScheduleCode, hrss.salaryScheduleName, st.stepName, st.seq, sl.laneName
from dbo.HRSalarySchedule hrss
left join dbo.HRSalaryScheduleStep st on st.hrSalaryScheduleID = hrss.hrSalaryScheduleID
left join dbo.HRSalaryScheduleLane sl on sl.hrSalaryScheduleID = hrss.hrSalaryScheduleID
order by hrss.salaryScheduleCode, st.seq, sl.seq

enter image description here

1
sortingmatrixreporting-servicesssrs-2012
Can you edit your post to show a sample of your data and your entire report design. Also state where you are setting the sort and if possible, a screen shot of the output you are currently getting. This is probably really simple to fix but without more info its too much guess work. – Alan Schofield
I think your list should be sorted on the salaryScheduleName since that what it seems to be grouping on. Your SORT should be on the stepName Row GROUP since it's from the Step table. – Hannover Fist

1 Answers

1
votes

Change the sorting of the tablix to use an expression:

=CInt(Fields!stepName.Value)

Edit:

Ok, I misunderstood the 50/50 thing with varchar/numeric values, so here's a better solution.

The Val function evaluates a leading numerical value, so I suggest to create 2 sorting columns:

  1. Expression =Val(Fields!stepName.Value)
  2. Field stepName

This will for example create a sort order like this:

Sorted Example

Here's a screenshot to clarify what to configure:

enter image description here