@WhereClause datetime range parameter

[former_member651359]

How do I code a @WhereClause

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

[former_member651359]

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?

[abhilash_kumar]

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

[former_member651359]

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?

[abhilash_kumar]

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

[DellSC]

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