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
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
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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