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

Crystal Reports

Avatar of undefined
Last Comment
MECR123

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Kurt Reinhardt

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy