Stored procedures automating multiple values

[ginger_fabricius]

I have a stored procedure that I would like to have it read in and process a table or file full of multiple values for each parameter in the stored procedure. I would like to do this in Crystal reports, if its possible.

[abhilash_kumar]

1. Create a new report that points to the Excel datasource.

2. Drag and drop the field you want the list of values to be sourced from on the details section.

3. Go to the Database Menu > Select 'Show SQL Query'.

4. Copy the SQL that gets generated.

5. Go back to the original report and paste the query in the Add Command Window. Don't forget to add the 'distinct' keyword.

-Abhilash

[abhilash_kumar]

You'd first establish a connection to the Excel Sheet from the Database Expert using the Access/Excel(DAO) driver.

After you add the sheet to the list of tables, go to the links tab and remove any links that get automatically added.

Next, edit the prompt and change its 'List of Values' to 'Dynamic' and choose the field from the Excel sheet under 'Value' as the source for this list.

-Abhilash

[ginger_fabricius]

Thank you for responding so fast. I have gotten that, but that is done in the Crystal. I am at a loss as to how to call the xls in the SQL command? What do I do to accomplish this part:

1. Go to the Database Expert > Expand the current database connection > Double-click 'Add Command' and type in a SQL Query:

Select distinct table.field from table

Replace table.field with the field you'd want the list of values from.

[ginger_fabricius]

Can you tell me how I call the xls instead of a table in this statement:

Select distinct table.field from table

Please, I am stuck and need help.

[abhilash_kumar]

Yes, an Excel file works too.

-Abhilash

[ginger_fabricius]

storedproccode.pnglinkspic.pngparmpic.pngfieldexploer.png

It doesn't seem to be working. I have attached my report. If you can, can you tell me what I did wrong please? There are 38 lines in the excel sheet. I wanted them all to be read in. I put the statname in the format you gave, but it only processed 1.

[ginger_fabricius]

Thank you very much for this answer. If the information I need is in an excel spreadsheet, can this still be done or is it considered an external file?

[abhilash_kumar]

Hi Ginger,

Crystal Reports only supports 'dynamic' prompts from a database table - not an external file.

If the list of values are sourced from a table, here's what you need to do:

1. Go to the Database Expert > Expand the current database connection > Double-click 'Add Command' and type in a SQL Query:

Select distinct table.field from table

Replace table.field with the field you'd want the list of values from.

2. Go to the Links tab and remove any links that CR automatically adds between this command table and the Stored Proc. Ignore the warning it generates.

3. Expand Field Explorer > Right-click the parameter name > Edit > Change the List of Values from 'static' to 'Dynamic'.

4. Under 'Value Options', choose the field from the Command Object and click the text that reads ' click to create parameter'.

-Abhilash

[former_member269596]

Hi ,

Yes you can use store procedure with multiple parameter.

You have to make token.

Please refer:

Working with Selection Criteria Tokens

In case of Optional Parameter you have to make formula.

Thanks.

Kind regards,

Vinit

[ginger_fabricius]

I am not sure if you realize I not only wanted multiple parameters but each of those has multiple values. Does this still work for that? The values I want are in an excel spreadsheet.