Question Re: Using a parameter in a command in Technology Q&A

Using a parameter in a command

Former Member — Tue, 09 Aug 2011 16:19:50 GMT

I'm running Crystal Reports XI against an Oracle database. I created a command which initially used a static value for one of the where comparisons. Now I have created a parameter with static values and I want the command to make the comparison against the entered selection for the parameter. After searching the internet, I tried many variations but so far none of them work. HERe is the where clause from the command with the last thing I tried (a like statement):

WHERE U.INSTRUMENT_TYPE_CD IN ('Future EFP Unposted')

AND (U.TRADE_NUM = S.TRADE_NUM AND U.TRADE_NUM = A.TRADE_NUM AND U.TRADE_NUM = T.TRADE_NUM AND U.TRADE_NUM = X.TRADE_NUM)

AND (U.TRADE_NUM = TTU.TRADE_NUM)

AND (S.EXTERNAL_KEY = A.CASHFLOW_NUM AND S.ITEM_STATUS_IND = 1 AND S.REPLACED_ITEM_HDR_NUM IS NULL)

AND S.COST_TYPE_CD = 'Primary Settlement'

AND U.STRATEGY_NUM = O.STRATEGY_NUM

AND O.PROCESS_GROUP_CD like {?LOB}

AND O.PARENT_STRATEGY_NUM IN (1080,1078,1081,1084,1007,2343)

AND (I.IND_NAME = 'buy_sell_ind' AND U.BUY_SELL_IND = I.IND_VALUE)

AND S.LOCATION_NUM = L.LOCATION_NUM

AND U.COUNTERPART_COMPANY_NUM = C.COMPANY_NUM

AND T.BROKER_COMPANY_NUM = C1.COMPANY_NUM

AND U.TRADER_PERSON_NUM = P.PERSON_NUM

AND (X.EXCH_CONTRACT_NUM = E.EXCH_CONTRACT_NUM AND X.QUOTE_DEF_NUM = E.QUOTE_DEF_NUM)

AND U.MTM_QUOTE_DEF_NUM = Q.QUOTE_DEF_NUM

ORDER BY FUTURE_EXPIRATION_DT, RISK_PERIOD_CD, QUOTE_DEF_CD

The line in question is AND O.PROCESS_GROUP_CD like {?LOB}. I've treid equals and IN's for the like. I've tried {?@LOB}. This should be so easy. Where am I going wrong?

Any help would be appreciated.

Re: Using a parameter in a command

Former Member — Tue, 09 Aug 2011 16:56:41 GMT

I forgot to tell you that the error message says that there is an invalid character.

The last thing I tried was AND O.PROCESS_GROUP_CD IN ({?LOB})

Re: Using a parameter in a command

abhilash_kumar — Tue, 09 Aug 2011 20:55:02 GMT

Hi,

Could you try placing the parameter within single quotes. Here's what I mean:

AND O.PROCESS_GROUP_CD = '{?LOB}'

Oh and I'm not too sure if IN would work with Crystal Reports XI. I believe multi-value command parameters were not supported until CR 2008.

-Abhilash

Re: Using a parameter in a command

Former Member — Tue, 09 Aug 2011 21:36:59 GMT

That is almost it. I figured out that for commands the parameters have to be entered via Database Expert/Command Edit. You cannot create the parameter the normal way. That is unfortunate as the options for a paramter in command edit are sparse. When you use the parameter in the SQL you do have to use the quotes as you suggested becuase apparently the value is determined before the SQL command is evaluated.

Re: Using a parameter in a command

Former Member — Wed, 10 Aug 2011 01:29:06 GMT

I figured out that for commands the parameters have to be entered via Database Expert/Command Edit.

>> This is true. You do have to add the parameter to the parameter list in order to reference it in the command SQL.

You cannot create the parameter the normal way.

>> Not true. You have 2 options...

-- 1) Create the parameter in the "normal manner" then simply retype the parameter name in parameter list (making sure to use the exact same spelling).

-- 2) Create the parameter in the command's parameter list, then edit it from the design view/field explorer.

That is unfortunate as the options for a paramter in command edit are sparse.

>> The parameter list is only there to create the reference. Use the parameter designer, from the report design view to set the parameter details. (see above)

When you use the parameter in the SQL you do have to use the quotes as you suggested ...

>> Not necessarily true... CR does a VERY cool thing when it comes to passing parameters to commands. It actually does a direct substitution of the parameter value into the command SQL. So it simply depends on the syntax of the database you are using.

... because apparently the value is determined before the SQL command is evaluated.

>> Yes, and this is a good thing.

-- For example (Using SQL Server syntax), all of the following are valid


-- Exact text match

SELECT *
FROM TableName
WHERE TextColumn = '{?TextParameter}'


-- Exact numeric match

SELECT *
FROM TableName
WHERE NumberColumn = {?NumberParameter}


-- Wildcard match

SELECT *
FROM TableName
WHERE TextColumn LIKE '%{?TextParameter}%'

You can even pass entire blocks of SQL through a parameter, giving the effect of dynamic SQL without having to write dynamic SQL

For exmple... Say you want to make a parameter that allows the user to select "FirstName LastName" or "LastName, FirstName"...

Create a parameter like this...


Value 1
t.FirstName + ' ' + t.LastName AS Name
Desc 2
First then Last

Value 2
t.LastName + ', ' + t.FirstName AS Name
Desc 2
Last then First

Now write your SQL like this...


SELECT
{?NameParameter},
t.Column2,
t.Column3
FROM TableName AS t

The only limitation to using parameters in commands is your imagination. I've even passed entire select statements through commands.

HTH,

Jason

Re: Using a parameter in a command

Former Member — Wed, 10 Aug 2011 14:58:55 GMT

WOW! That is an awesome description. Thank you so much for information.

Re: Using a parameter in a command

Former Member — Mon, 19 Sep 2011 06:35:14 GMT

Hi,

Thanks for your posting it helps a lot, but still I am not able to pass parameters to select statement. Please refer the below screen shot and details. Could you please help?

I wrote the below query in Add Command - but getting an error - when I pass hard coded value, it is working fine.

Not working:-

SELECT SUM(XXXXX) from XXXX_YYY where ZZZZZ_TYPE in (1,3,4,6,10,12,13,21,23,30,31,2,11,20,22,32,33) and DATE > ({@Interpreted Start Date})

Working :-

SELECT SUM(XXXXX) from XXXX_YYY where ZZZZZ_TYPE in (1,3,4,6,10,12,13,21,23,30,31,2,11,20,22,32,33) and DATE > '09/17/2011'

FYI... I have already created Interpreted Start Date formula.

Thanks,

Veer Jetta

Re: Using a parameter in a command

Former Member — Mon, 19 Sep 2011 13:54:42 GMT

Veer,

Dates & Times present their own special pains in the back side when working with commands and parameters. The problem is that the formatted value that CR passes isn't necessarily compatible with the acceptable date formats of your RDBMS.

The best way to figure out the solution is to build a quick test report, add a table (in the Database expert, not a command) that has a data or datetime column, create a data or datetime parameter and add that parameter to the selection criteria in the Selection Expert.

Make sure the report executes and that it is in fact being filtered on the date column.

Now that you know it works, open the "Show SQL Query..." under Database in toolbar. This will give you the syntax that CR is using to successfully pass the date parameter.

HTH,

Jason

Re: Using a parameter in a command

Former Member — Mon, 19 Sep 2011 15:07:47 GMT

Jason's right about the format of dates. The DB is picky. I'm running ORacle 10g and it likes 'YYYYMMDD'. The other thing I noticed is that you did not enclose the parameter in quotes. You have to do that for strings and dates. Notice that in your hard-coded example you ahve the date enclosed in quotes. I'd try that first.