Width of parameter on MSSQL 2005 Reporting

I am creating a simple one table report with some parameters that have menus associated to them.  The menus are query results from another table - for instance "Company Name".  Due to a previous design the company name field is 254 characters long although the companies selected vary from only 5 characters to approximately 30 characters.  Unfortunately the parameter field a top the report expands the width of the parameter field to 254 characters.  Is there a way to control the width of the parameter field either through a property setting or through SQL.  I am stuck using an ODBC as the middle piece.

Thank you in advance.
KlagpooAsked:
Who is Participating?
 
KlagpooAuthor Commented:
Hi Technofile,
Thanks for the help.  It is an ODBC issue but I was able to work around it based on some suggestions from you... if someone else comes across this... what I did was:
1. I opened up the dataset properties and under fields (original field "Company" which I have a parameter for... should already be there as part of your SELECT statement) I created a calculated field called "Company2".  The value I gave it was to first convert it to a string and then use a LEFT function as:
=Left(CStr(Fields!company.Value),30)
2. Under Layout -> Report Parameters... I select the "Company" parameter with available values being "From Query".  I select the proper dataset and for the value of the field I put "Company" and for the Label of the field I put "Company2". So... the width of the parameter triggers off of the label but the query still triggers off of the value.
-Klagpoo
0
 
technofileCommented:
Trim might help you otherwise show me your code and I should be able to help
http://www.1keydata.com/sql/sql-trim.html

Also is CompanyName a Char(254) or a VarChar(254)?
If it is a Char I would seriously suggest changing it to a varchar, if your not allowed to do that, do a when you doing your report copy the records into an identical table that has a varchar instead of a char and it will go away.
0
 
KlagpooAuthor Commented:
Hi Technofile,
I think it is an ODBC issue in part.  I can not use the LTRIM feature in my select statement, nor in the expression area for reporting.  It is VarChar(254).  And When I changed it to 60 for testing it did decrease the width.  Any other thoughts? Or any other way to control the size - I tried converting it to text as well to no avail.
 
Thanks.
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
technofileCommented:
In the future accept your own answer and select the other anwser as an assist you can still award point anyway you want and it won't require someonelse to do anything someone is going to have to do a little cleanup the way you "resolved" the question
0
 
KlagpooAuthor Commented:
Sorry about that technofile - I am new at this - I am hoping that you are rewarded for the poitns.
0
 
technofileCommented:
As long as the moderators read the request correctly and allow it I think it should, its not a huge deal its just another thing that the volunteers here will have to do instead of the automated system handling it. Like i said just for the future.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.