How would you send a date-range parameter to a SQL sproc?

[Former Member]

Team,

MY ENVIRONMENT

SQL 2005, Crystal Reports for Visual Studio 2005

MY PROBLEM

I am authoring both a sproc and a report, so I have full control over the design. I am a SQL expert and also a Crystal 8.5 expert.

I have done the Sproc-Report connection dozens of times.

Please consider along with me the sequence of creating a report based on a parameterized stored procedure.

My sproc header is shown here:

CREATE Procedure dbo.usp_DocumentActivityReport(
     @Department NVARCHAR(50) 	
   , @DateRange  NVARCHAR(50) 
) AS 
 ...
SELECT Col1, Col2, Col3 FROM #TEMP

MY THOUGHT PROCESS

@DEPARTMENT is a string. That's easy.

@DATERANGE is a DATE RANGE and I don't know how to get Crystal Reports to prompt for a date range, so I used a String parameter knowing I can parse a specially formatted string, and knowing that I can use a formula to compute the string.

Step 1. Create the blank report, the {?Department} parameter, the {?CreationDateRange} report parameter, and the {@DateRangeText} conversion formula that converts {?CreationDateRange} to the specially formatted string.

Step 2. Test the stored procedure.

Tests pass; It returns data when I run it with values, with zero-length string values, and with NULL values.

Step 3. Tie the report to the stored procedure.

Adding the sproc directly creates two hard-wired, undeletable parameters, and returns data columns. That's no good because the user must supply the specially formatted string for the date range. So, I try using Add Command instead, with this syntax:

{call "EXP_TEST"."dbo"."usp_CorroDocumentActivityReport" (N'{?Department}', N'{@DateRangeText}')}

This code is accepted, but Add Command did not create any undeletable parameters at all. I guess that's OK.

But the worst part is that it does not show any output columns with data either! AAARGH!

Please assist with showing me the proper order to do these steps.

BTW, here's the VB Syntax formula for {@DateRangeText}:

Dim min As String
dim max as String
if HasLowerBound ({?CreationDateRange}) then
  min = ToText(Minimum({?CreationDateRange}),"MM/dd/yyyy")
else
  max = ""
end if
 
if HasUpperBound ({?CreationDateRange}) then
  max = ToText(Maximum({?CreationDateRange}),"MM/dd/yyyy")
else
  max = ""
end if
 
if IncludesLowerBound ({?CreationDateRange}) then
  min = "[" & min
else
   if HasLowerBound ({?CreationDateRange}) then min = "(" & min
end if
 
if IncludesUpperBound ({?CreationDateRange}) then
    max = max & "]" 
else
   if HasUpperBound ({?CreationDateRange}) then max = max & ")"
end if
 
'formula = min & "..." & max 
formula = "(1/1/2009...3/1/2009)"

sorry ... cross-posted per Amit

[Former Member · Accepted solution]

Ludek,

It sounds like you and think along the same lines! I have cross-posted for both of you now!

Please see

~ Shaun

[Former Member · Accepted solution]

Sounds good. It's always a good idea to get it working in the designer first. Hopefully the design guys can figure it out faster than we could here. Once it work there, if you have issues at runtime, let us know and we'll help you from there.

Ludek

[Former Member]

Closing post as no response for over 2 months.

[Former Member]

Looks to me like this does not even work in the CR designer(?). I'd suggest posting to the [Crystal Reports Design forum|; before running the report with any SDK.

Ludek