Crystal Reports, Advanced SQL Commands and Parameters

Crystal Reports Pic 4 - Select Data Tables (Photo credit: wmharshana)

I've not written a Crystal Report that required a more advanced query in quite a while, and had a need to union two selects and pass in a parameter to both queries.

I initially just wrote the Union query and attempted to place my {?CustomerName} in both places using the "=" comparison operator, which worked.

When I wanted to add the ability for the user to only add a portion of the customer name in the query and use the LIKE operator, Crystal had some issues and thought I was trying to pass a column name or some such trash.

Eventually I found an article that suggested the use of a DECLARE and SET to hold the parameter that Crystal passes, then hand it off to the query. This also failed until I wrapped the entire UNION in a SELECT and passed in the parameter once.

Yay!

DECLARE @Cust VarChar(200)
SET @Cust = '{?CustomerName}'

SELECT * FROM (
SELECT Name, Address, City, State, Zip From CustArch
UNION
SELECT Name, Address, City, State, Zip From Customers
) a
WHERE Name LIKE '%' + @Cust + '%'