Using parameter Multiple values in SQL query (command)

[Former Member]

Crystal Reports 2008

I created a crystal report with complicated query with about 6-8 parameters in it. I have 2 parameters which can be optional and also have multiple values. Report works fine if I just enter one value or leave it blank. But if I check multiple values option for those parameters and try to add more than one value, it gives 'missing right parenthesis' oracle error after accepting all the parameters.

Can someone help me to resolve this issue?

Thanks in advance

Raji

[Former Member · Accepted solution]

Hi,

1 Check your query after entering all your parameter by clicking on Show SQL Query option in Report Menu.

2. Values you are passing belongs to which data type?

3. Are you using this parameter in record selection or Sql Command

Regards,

Sathish

[Former Member · Accepted solution]

1 Check your query after entering all your parameter by clicking on Show SQL Query option in Report Menu.

In CR 2008, I dont see this option under Reports menu.

2. Values you are passing belongs to which data type?

String

3. Are you using this parameter in record selection or Sql Command

In SQL Command. I created the query using Database-Database expert-add command option

Here is my query:

select total_calls, unknown_calls,repeat_calls

from

(select sum(count_num) total_calls, sum(unknown_acct_num) unknown_calls

from odi_app_sdr_summary s, odi_entry_point_dim d,

odi_app_dim app

where

s.entry_point_key = d.entry_point_key

and s.sum_time >= {?p_start_date}

and s.sum_time < {?p_end_date}

and app.provider_id = '{?p_provider_id}'

and app.app_id IN nvl('{?p_app_id}',app.app_id)

and d.entry_point in nvl('{?p_entry_point}',d.entry_point)

),

(select sum( case when '{?p_interval}' = '10m' then repeat_call_cnt_10m

when '{?p_interval}' = '30m' then repeat_call_cnt_30m

when '{?p_interval}' = '24h' then repeat_call_cnt_24h

else repeat_call_cnt_24h

end) repeat_calls

from odi_repeat_call_summary r, odi_entry_point_dim d,

odi_app_dim app

where

r.entry_point_key = d.entry_point_key

and r.sum_time >= {?p_start_date}

and r.sum_time < {?p_end_date}

and app.provider_id = '{?p_provider_id}'

and app.app_id IN nvl('{?p_app_id}',app.app_id)

and d.entry_point in nvl('{?p_entry_point}',d.entry_point)

)

[Former Member · Accepted solution]

Hi Rajeshwari,

Try to insert the command report as a sub report and in the main report just insert the field which you wanted to make it has multiple values and thn create a formula like

"[" & Join(?Parameter,",") & "]"

And link this parameter to the sub report command objects parameter by going into > Right click on sub report and change sub report link.

Regards,

Vinay

[Former Member · Accepted solution]

"[" & Join({?Parameter},",") & "]"

[Former Member · Accepted solution]

Thanks for your response.

I have more than 5 paramaters related to the query. I do not show the field which requires this multi selection parameter in the report, it is just used in the filtering criteria.

Can i use the 'Allow multiple values' check box in the parameter inside the command? I have tried this but it is not working. It would be great if I can use this option.

[Former Member · Accepted solution]

Hi Rajeshwari,

The command level parameters are always a discrete values it can accept only one value. If you want the command level parameter to accept multiple values then unless you join it with a comma separator it will not accept it as a multiple values.

The other way around is you can create a reprot level parameter and specify the condition in the Selection formula but it could hamper the report performance.

Regards,

Vinay