We help IT Professionals succeed at work.

crystal reports parameter entry to SQL procedure

MECR123
MECR123 asked
on
694 Views
Last Modified: 2012-06-21
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

Comment
Watch Question

Senior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Kurt ReinhardtSr. Business Intelligence Consultant/Architect
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.