Skip to Content
0

Stored procedures automating multiple values

Mar 03, 2017 at 10:54 PM

94

avatar image

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.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

7 Answers

vinit kumar Mar 04, 2017 at 09:14 AM
0

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


Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Abhilash Kumar
Mar 06, 2017 at 08:07 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded
ginger fabricius Mar 06, 2017 at 02:26 PM
0

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?

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 06, 2017 at 02:51 PM
0

Yes, an Excel file works too.

-Abhilash

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

fieldexploer.png (24.9 kB)
linkspic.png (27.4 kB)
parmpic.png (30.4 kB)
storedproccode.png (34.7 kB)
0
ginger fabricius Mar 07, 2017 at 04:56 PM
0

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.

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Mar 07, 2017 at 05:58 PM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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.

0
Abhilash Kumar
Mar 08, 2017 at 07:05 AM
0

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

Share
10 |10000 characters needed characters left characters exceeded