0

I have a report that I need to add an interactive sorting, but the column that I need to add the sorting has a LookUpSet =Join(LookupSet(Fields!ReportUNC.Value, Fields!ReportUNC.Value, Format(Fields!cntSelfService.Value, "###,#######0"), "ExecutionCount")) expression that has a hyperlink to another report. When the interactive sort is clicked the numbers are not sorting correctly.

enter image description here

Arsee
  • 651
  • 2
  • 11
  • 36

1 Answers1

0

The issue is that the FORMAT function is converting the cntSelfService field to a string and then it is sorted as a string by characters instead of number (where "89" is less that "9"). 

=Join(LookupSet(Fields!ReportUNC.Value, Fields!ReportUNC.Value, Format(Fields!cntSelfService.Value, "###,#######0"), "ExecutionCount"))

Can you remove the FORMAT and use the Text Box's FORMAT Property to format the data like you want?

=Join(LookupSet(Fields!ReportUNC.Value, Fields!ReportUNC.Value, Fields!cntSelfService.Value, "ExecutionCount"))

enter image description here

I don't think there's another way since you're using Interactive sorting.

Now that I look at it again, do you ever get more than 1 record with the LookUpSet? If so both your and my expression would fail. If not, you could just use LookUp and not use JOIN.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39