Thursday, April 25, 2013

Crystal Reports, Advanced SQL Commands and Parameters

Crystal Reports  Pic 4 - Select Data Tables
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 + '%'
Enhanced by Zemanta

3 comments:

Anonymous said...

But you can get the same result by just creating a command like this

SELECT * FROM (
SELECT Name, Address, City, State, Zip From CustArch
UNION
SELECT Name, Address, City, State, Zip From Customers
) a
WHERE Name LIKE '%' + '{?CustomerName}' + '%'

Philips Huges said...


Its very useful to me. Wonderful blog.. Thanks for sharing informative Post.

Installment loans
Payday loans
Title loans

Philips Huges said...



Its a wonderful post and very helpful, thanks for all this information. You are including better information regarding this topic in an effective way.Thank you so much

Personal Installment Loans
Payday Cash Advance loan
Title Car loan
Cash Advance Loan