Dynamic SQL Command Queries Using Parameters 1

[tdoman]

I'm using Crystal Reports 11 and am trying to dynamically build part of the "Where" clause in the SQL command query to filter data on the server before the results are sent to the client. I'm doing this to decrease the load time of my reports by not having to send a large set of data to the client, then filter the results on the client side.
I want to create a parameter list that is dynamically generated from a table in a database and allows you to select mutiple values from that list. For example: I have two tables. One employee table and one employee type table. The employee table lists all employees and their related employee types (full-time, part-time, and contractor). The employee type table lists the employee types that will dynamically be loaded into the parameters list.

Employee Table
ID Name TypeID
1 Bob 2
2 Steve 1
3 John 3
4 Bill 1

Employee Type Table
ID Type
1 Full-Time
2 Part-Time
3 Contractor

Assuming the user selects ‘Full-Time’ and ‘Contractor’ from the parameter list, the SQL command query I want to generate is as follows:
SELECT *
FROM emp, emp_type
WHERE emp.ID = emp_type.ID and emp.TypeID in (‘1’, ‘3’)

I tried building the following SQL command query so it would dynamically replace {?EmpType} with the ID’s of the types the user selected but it doesn’t work. Any ideas how to fix this?
SELECT *
FROM emp, emp_type
WHERE emp.ID = emp_type.ID and emp.TypeID in ({?EmpType})

 

[lbass]

I think you are linking the wrong fields, for one thing. Try:

SELECT *
FROM emp, emp_type
WHERE emp.typeID = emp_type.ID and
emp.TypeID in {?EmpType}

Ordinarily, you would put single quotes around a string command parameter, but in this case, because you want multiple values and command parameters can only accept one value, you would have to instruct the user to enter the parameter values in a string, exactly as follows, including the parens:

('1','3')

-LB

 

[tdoman]

good catch on linking the wrong fields...that was a typo.

As far as your other comment goes...that's what I thought too but I just wasn't sure if that was the only way to do it. I hesitate to have the user type in the string using the SQL syntax (i.e., parenthesis, quotes, etc.). But it is the only option I can see at this point since I'm loading the reports in a browser using the Crystal Reports web server.

The only other way to solve it that I can think of is to load the reports in a VB app and use VB to create my own parameter input screen to generate the dynamic SQL string then pass that to the Crystal Report on the fly.