vrijdag, januari 22, 2010

Reporting services Picklists based on models

To force a picklist to have a fixed length :

1) In the picklist-query, add the field a second time (you'll use this one as label on your parameter) -- change the code for that field by right-clicking it and changing the formula (on the query panel)

for the example, I'll use a string-field that should be 7 characters long and should get leading zeroes -- a specific case I used this for.

2) change the formula to :
left("0000000"; 7 - length([nameofthefield])) & [nameofthefield]
if the field is numeric :

left("0000000"; 7 - length(text([nameofthefield]))) & text([nameofthefield])

That also sorts the picklist, but if you simply want to sort, just add the same field a second time.


Geen opmerkingen: