string parameter to int

[tezzyr]

Im using cr IX on an sql server db which I cannot modify in any way ie change datatypes, add fields etc as an existing application uses the database as it stands at the moment.

I have 2 db fields, USER and COMPANY set up as follows
USER:-
field type
userid int
username text
companyid int

COMPANY:-
field type
ID int
NAME text

the two db tables are linked as user.companyid=company.id

USER table stores a value (comp) as a number, which maps to the ID number for a company in the COMPANY table.

I'm looking to set up my report so that the user is asked to enter the name of the company they want the report data to show, eg if the user enters microsoft in the popup box when the report runs, then the report filters out all data so that only microsoft data is shown on the report.

when setting up a parameter field, how do i enable the user to enter a string as a company name, bearing in mind that the company name text is stored as a text datatype.

when running the report and entering microsoft I keep seeing an error saying that a digit is required.
the report works if i enter a digit (companyid) but not if i enter a text string.

how can i set up a parameter field so that the user can enter text and have this text value resolve to the companyid so that i can filter out the data i dont need?
thanks in advance for your help

 

[lbass]

If you want to use text for filtering, then change the parameter type to string and use a record selection formula like:

{company.name} like "*"+{?CoName}+"*"

The wild cards will allow users to use part of a company name or the entire name.

-LB