Link to home
Create AccountLog in
Avatar of MECR123
MECR123

asked on

crystal reports parameter entry to SQL procedure

Hello

I wish to create a crystal report that will allow me to select a number of customers and then pass these customers as a string into a SQL procedure to retrieve the appropriate data.  My customer number is 10 long and I know I can pass multiple customers like ‘     12345,     12346’  - However I would like to use the standard crystal functionality for parameter entry – allow multiple selection etc. This is a lot more user friendly.

Crystalreport 1

Parameter
I need to set up the parameter within crystal – I will select allow multiple values   - This will allow me to select 1 or more customers

This will create a listing like
12345      Customer1
12346      Customer3
12347      Customer3

This listing can be suppressed so as not to show the list

I then wish to join these customers numbers together and pass to crystalreport2 like ‘     12345,     12345,    12347’ -  Crystalreport2 is a a subreport.

Crystalreport2 will be the report to call the SQL procedure with the parameter list of customers and then display the data retrieved
 

My questions are
Will this work ?
How do I create the joined list so as to join all the customers ? – It may be one customer or many customers
Is this the best solution to what I am trying to do ?

thanks
Mike

ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
SOLUTION
Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
Avatar of MECR123
MECR123

ASKER

hi mimcc

I need the list as ‘     12345,     12345,    12347’
leading single quote, then customer number, followed by comma and then next number and so on and ending with a single quote

your code only returns last number in list

I have created a shared variable - put in the detail lines of the report so that it builds up for each record
then also show the variable in the footer for the complete list
Shared stringvar Custlist :=  (custlist + ',' + {TABLE.CustomerAccount} )
This returns
,      1007,      1077,      1175,      1198,      2982,      3018,      3018
I then need turn this in
'      1007,      1077,      1175,      1198,      2982,      3018'
regards
mike