Skip to Content
avatar image
Former Member

Stored procedures automating multiple values

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • avatar image
    Former Member
    Mar 04, 2017 at 09:14 AM

    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


    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Mar 06, 2017 at 08:07 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 06, 2017 at 02:26 PM

    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?

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 06, 2017 at 02:51 PM

    Yes, an Excel file works too.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Mar 07, 2017 at 04:56 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 07, 2017 at 05:58 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      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.

  • Mar 08, 2017 at 07:05 AM

    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

    Add comment
    10|10000 characters needed characters exceeded