CODEWITHSUNDEEP
reporting-services
niao
niao

Reputation: 5070

Reporting services - custom format

Greetings, inside one of the column I have values like the following:

9-7
9-18
9-142

At the moment, when I sort based on this column I have to following:

9-142
9-18
9-7

I would like to sort these values to be ordered as follows:

9-7 (so it would be 9-007)
9-18 (so it would be 9-018)
9-142

I tried to following format: 

=Format(Fields!ShelfNumber.Value,"000-000")

But it doesn't work. Can someone help me, please?

Upvotes: 0

Views: 887

Answers (1)

user359040
user359040

Reputation:

Sort on the following expressions:

=Cint(IIf(InStr(Fields!shelfnumber.Value,"-")<=1,"0",Left((Fields!shelfnumber.Value & "-"), InStr((Fields!shelfnumber.Value & "-"),"-")-1)))
=Cint(IIf(Fields!shelfnumber.Value="" or Len(Fields!shelfnumber.Value)=InStr(Fields!shelfnumber.Value,"-"),"0",Right(Fields!shelfnumber.Value, Len(Fields!shelfnumber.Value) - InStr(Fields!shelfnumber.Value,"-"))))

The first converts the characters before the - to a number, the second converts the characters after the - to a number.

EDITED, to allow for an empty string.

FURTHER EDITED, to allow for ShelfNumbers with - at the start or end of the string.

FURTHER EDITED, following testing.

Upvotes: 1

Related Questions