ansaurus

Question

Answer 1

+1 A:

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.

Mark Bannister 2010-08-17 11:14:22

after applying this sorting on my table I get the following error: The sort expression for the table 'table1' contains an error: Argument 'Length' must be greater or equal to zero

niao 2010-08-17 12:53:15

@niao - it sounds as though some of the shelf numbers don't have a `-` in them. Apart from strings formatted like `#-#`, what other formats can the shelf number be?

Mark Bannister 2010-08-17 13:01:52

it can also be an empty string

niao 2010-08-17 13:08:54

@niao - amended, to allow for an empty string.

Mark Bannister 2010-08-17 13:28:44

still the same problem.. I also noticed that there are some rows where shelfnumber is "A4-24"

niao 2010-08-17 13:45:52

@niao - probably a different error message (unless the underlying database is Oracle, in which case the empty string is being treated as NULL, not as an empty string). A4-24 is a different format again (neither `#-#` nor ""), so the existing coding doesn't cover it; you also haven't indicated how you want it to be sorted.

Mark Bannister 2010-08-17 13:55:32

hmm..thanks for your help Mark. It seems that it will be very hard to achieve this kind of sorting, especially that shelf numbers are of different formats.

niao 2010-08-18 06:02:06

I've removed all letters from database and values does not contain any letters. However when using sort expression from above I have the following error:The sort expression from the table 'table1' contains an error: Argument 'Length' must be greater or equal to zero

niao 2010-09-09 11:15:09

@niao: does ShelfNumber include values with no "-" (eg. "100", "02001", etc.)? If so, the sort expressions will need to be modified to take account of it - although you will need to decide whether the whole number should be used in the first sort item, or whether it should be split up by length (ie. should "02001" be sorted as 2001, 0 or as 20, 1).

Mark Bannister 2010-09-09 12:04:29

Mark, all shelNumbers contains "-". Although the problem I can see is connected propably with empty values. When all values have for instance :"22-11" then it all works fine. The same with "x-x","x-xx","xx-x" formats

niao 2010-09-10 06:04:24

@niao: it shouldn't be completely empty values, as the code in the answer made allowances for them. It might be the case that some ShelfNumbers have the `-` at either the start or the end of the string - I have now edited the answer to make allowances for this, too.

Mark Bannister 2010-09-10 12:35:26

@niao: a further thought - are there any NULL ShelfNumbers? Also, are you reporting from a SQLServer database or from some other form of SQL?

Mark Bannister 2010-09-10 13:01:55

I am reporting from SQL Server. No, I've made an SQL query to group all shelf numbers and there are no NULL values - only empty strings.

niao 2010-09-13 05:57:13

I've tried with your expression. It only works when I get shelf numbers that have value (non empty strings). However it also doesn't work correct unfortunately. It seems like values are non sorted - looks like randomly values;/ (3-3,4-4,7-7,8-8,8-8,6-6,13-13,1-1)

niao 2010-09-13 06:14:43

@niao, having tested my expressions, I found out something I hadn't realised about SSRS: `IIf` expressions evaluate **both** then *and* else arguments for all records, so both arguments have to be valid, even though only one will be used for any given record! I have now amended the expressions to take account of this; they seem to work with the ShelfNumber values I have tried, which are ('3-3','4-4','7-7','8-8','6-6','13-13','1-1','','-1','1-','0001-20','1000-').

Mark Bannister 2010-09-14 06:46:52

Thanks for an anser Mark. However I have manage to resolve it differently. In my SQL Query I added two more columns like: ShelfNumberMajor and ShelfNumberMinor which will contains values from xx-yy format. If shelf number does not contain any value or format is incorrect (integer-hyphen-integer) then I change values to -1, -1. It works almost correct but I don't know how to sort by two values in one Sort Expression. Please read my another post that can be found here:http://stackoverflow.com/questions/3706732/reporting-services-sort-expression

niao 2010-09-14 07:06:00

ansaurus

tags:

views:

answers:

Reporting services - custom format

related questions