?
Solved

Width of parameter on MSSQL 2005 Reporting

Posted on 2009-12-16
6
Medium Priority
?
441 Views
Last Modified: 2012-05-08
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.
0
Comment
Question by:Klagpoo
  • 3
  • 3
6 Comments
 
LVL 7

Assisted Solution

by:technofile
technofile earned 2000 total points
ID: 26065796
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
 

Author Comment

by:Klagpoo
ID: 26066089
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
 

Accepted Solution

by:
Klagpoo earned 0 total points
ID: 26066341
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 7

Expert Comment

by:technofile
ID: 26066654
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
 

Author Comment

by:Klagpoo
ID: 26066709
Sorry about that technofile - I am new at this - I am hoping that you are rewarded for the poitns.
0
 
LVL 7

Expert Comment

by:technofile
ID: 26071591
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question