Skip to Content

@WhereClause datetime range parameter

How do I code a @WhereClause

in a stored procedure to use as a datetime range parameter?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Posted on May 06 at 08:27 PM

    You can't use a Crystal "range" parameter to pass to a stored procedure. Instead, you need to have separate Start datetime and End datetime parameters that get passed.

    -Dell

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 06 at 08:48 PM

    Hi Christine,

    Here's a workaround that requires the use of a Subreport. This won't work if the original report already contains a subreport(s).

    1. Start by creating a blank report

    2. Add the date range prompt in this report

    3. Insert the original report as a subreport and place it on the Report header. Make sure that the SP that is used as the source has a Start and End date parameter.

    4. Suppress all sections on the Main Report except Report Header

    5. Create a @startdate formula with this code:

    Minimum({?DateRangeParameter})

    6. Create a @enddate formula with this code:

    Maximum({?DateRangeParameter})

    7. Right-click the subreport > change subreport links > move the @startdate formula to the pane on the right > uncheck 'Select data in subreport based on field'; from the dropdown for 'select parameter field to use' choose the Start Date parameter.

    Note: If you don't see the Start date parameter, it indicates that the datatype of the Date Range parameter and SP parameter don't match.

    8. Repeat for End date parameter

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 15 at 07:13 PM

    I don't understand your directions.

    When I create a blank report:

    1. I use the stored procedure that I created. It has a @startdate and @enddate. and the Where statement looks like: Where dEventTime >= @startdate And dEventTime <= @enddate.

    2. I create new parameter: @Whereclause

    3. I insert the original report as subreport in the blank report and place in Header

    4. Suppress all sections except the Header section

    5. I tried to create a startdate formula and get an error message: The field cannot be summarized.

    What am I missing?

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 15 at 09:12 PM

    Hi Christine,

    The "blank" report does not need to be connected to any datasource.

    In this blank report, go to the Field Explorer and create a parameter of type "date" and set the "allow range values" option to True.

    Then follow steps 3 through 8 from my earlier post.

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 21 at 06:03 PM

    Thank you Abhilash. The crystal report now works fine. I am getting the parameter datetime and the parameters startdate and enddate when i run the report.

    I have additional issues. I added the new report to Interaction Adminstrator. Under the Parameters tab> SQL Table Columns I entered DateTime. In teh Tables tab I entered the store procedure from the orgianal report and when I try to run the report in IC Business Manager I receive an error that the 'Message=The table 'sprpt_NWEIVRAuditTest;1' could not be found. Error in File a45aa806-d2e8-4e8f-b6e9-a7aa4cce97f3 {5F7FBBD7-2BD0-47EF-8C55-E703C97EFCEB}.rpt: The table could not be found.

    What am I doing wrong?

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.