CRAXDRT: How do I set Date Range Parameters? 1

[SMerrill]

I have the following code snippet that sets a date range parameter incorrectly.
(The CR examples do not go this deep. I've looked.)
I get no errors, but if I use a Selection Formula of {TimeSheet.Work_Date} in {?Date Range} then it [red]filters out [/red]all the records. If I remove the selection formula, the report [red]shows[/red] all the records. So I must be doing something wrong in my ASP code.

Here is a VBScript snippet from my ASP page:

'Using CRAXDRT.DLL:Crystal Reports 8.5 ActiveX 
'                  Designer Run Time Library

WITH oRpt.Parameterfields.Item(2)
' This gets a ParameterFieldDefinition object
  SELECT CASE .DiscreteOrRangeKind 
  CASE crRangeValue
    .EnableRangeLimit = TRUE			    
    .MaximumValue = CDate("10/30/2003")
    .MinimumValue = CDate("10/31/2003")
  END SELECT
  Response.Write "Parameter [" _
    & .ParameterFieldName _
    & "] = [" _
    & .MinimumValue & "-" _
    & .MaximumValue _
    & "], ValueType=" _
    & .ValueType 
END WITH

'LOG IN TO EACH TABLE IN THE REPORT:
for each T in oRpt.Database.Tables
  T.SetLogOnInfo _
    sServer, sDatabase, sUserID, sPassword
  If NOT T.TestConnectivity then
    Response.Write "Connectivity error to table " _
      & T.Name & &quot;<br/>&quot;
    Set oRpt = nothing
    Set oApp = nothing
    Session.Abandon
    Response.End
  end if
next 
set T = Nothing		                   
		                            
'READ THE RECORDS:
call oRpt.ReadRecords()	
Response.Write _
  oRpt.PrintingStatus.NumberOfRecordRead _
  & &quot; records read.<br/>&quot;
Response.Write _
  oRpt.PrintingStatus.NumberOfRecordSelected _
  & &quot; records selected.<br/>&quot;

This results in the following:

Parameter [Date Range] = [10/30/2003-10/31/2003] ValueType=10 which is crDateField
525 Records read.
0 records selected.


Take care,
--Shaun Merrill
Seattle, WA

 

[FVTrainer]

In your sample code you have:

.MaximumValue = CDate("10/30/2003&quot
.MinimumValue = CDate("10/31/2003&quot

Perhaps this is just an error in your code snippet (as opposed to the actual code you are using), but how can your minimum date be greater than your maximum date?

 

[SMerrill]

Yeah, it was a typo in the snippet that I made when I was trying to pare it down to size.

I have also tried to add this code both before and after setting the Maximum and Minimum values, and it still doesn't do what I want. If I put it in Before setting them, I get all records. If I put it in after setting them, I get an error.

  .AddCurrentRange CDate("10/30/2003"), _
    CDate("10/31/2003"), _
    crRangeIncludeLowerBound + crRangeIncludeUpperBound

Please help when you can.

Take care,
--Shaun Merrill
Seattle, WA

 

[FVTrainer]

Ok, here's code that I used to set a datetime parameter value that allows a range. Your parms are dates so you wouldn't include the time portion:

Dim crParm As CRAXDDRT.ParameterFieldDefinition
Dim dtBeginDate As Variant
Dim dtEndDate As Variant

dtBeginDate = CDate("09/01/2002 12:00:00 AM&quot
dtEndDate = CDate("09/30/2002 12:59:59 PM&quot

Set crParm = Report.ParameterFields.GetItemByName("ImportDate&quot

crParm.ClearCurrentValueAndRange

crParm.AddCurrentRange dtBeginDate, dtEndDate, crRangeIncludeLowerBound + crRangeIncludeUpperBound

The Developer's Guide is not helpful in this matter, but I used the RDC 9 Parameter sample referenced in the following pdf to create the code that passes the range value:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/apps_rdc.pdf.asp

 

[SMerrill]

When I put in your code, an error occured just after the call to oRpt.ReadRecords: "The parameter value is not in the range limit."


Take care,
--Shaun Merrill
Seattle, WA

 

[FVTrainer]

I didn't mention this in my previous post, but the code I posted should replace the following existing code:

CASE crRangeValue
.EnableRangeLimit = TRUE
.MaximumValue = CDate("10/30/2003&quot
.MinimumValue = CDate("10/31/2003&quot
END SELECT

Did you remove this code or leave it in?

 

[SMerrill]

Thanks. I found and removed the .EnableRangeLimit=True line, which got rid of the error. I am now only using the ClearCurrentRangeAndValue and the AddCurrentRange methods.

The main difference is that I am using VBScript and you seem to be using an early-binding version, perhaps Visual Basic. That is why I must use the CDate() conversions literally within the AddCurrentRange method.

The remaining problem is that the report comes up fine, but it does not filter down to the date range at all. If I run the RPT file directly, it filters properly.



Take care,
--Shaun Merrill
Seattle, WA

 

[FVTrainer]

Ok, so I modeled this in ASP. First, my report uses a Date Parameter that allows for Ranges. The Record Selection formula looks like:

{Orders.Order Date} in {?DateParameter}

In my asp code, I have:

With Session("oRpt&quot.ParameterFields.GetItemByName("DateParameter&quot
.ClearCurrentRange
.AddCurrentRange CDate("2001/Nov/01&quot, CDate("2001/Nov/30&quot, 2 + 1
End With

The last argument, 2 + 1, is to include lower and upper bounds.

The filter works on my report. I've tested it with several different date ranges. The only difference I can see is the method I'm using for addressing the parameter (using GetItemByName), but I've tested it using your syntax (.Item(x)) and it works that way, too.

 

[SMerrill]

Thanks for trying to help me solve this.

There must be something wrong with the date parameter settings still, since I can run them directly from the RPT design view (CR version 8.5.) I am tempted to send you the CR file, but you couldn't tie in to my ADO database.

Using the very helpful VB tool you pointed me to above (Report Designer Component's Automation Server), I am able to enumerate the properties of that date range parameter for you, but I am not able to use the program to load anything into the date range.
Name={?Date Range}
Kind=6
Number Of Bytes=4
Parameter Field Name="Date Range"
Report Name=""
Parameter Type = 0 (crReportParameter)
Value Type = 10 (crDateField)
Is Default Value Set=False
Is Current value set=False
Needs Current Value=True
Previous Value=SectionFormat() Only
Next Value=SectionFormat() Only
Value=SectionFormat() Only

I also noticed that the NumberOfCurrentRanges=0, so the thing needs a current value.
I hope this is helpful. Please give me your e-mail address if you want to share desktop with me and see this.



Take care,
--Shaun Merrill
Seattle, WA

 

[FVTrainer]

OK, now I'm a little confused. Are you attempting to use an ADO recordset? You said you have an ADO Database. What connectivity is your report using?

 

[SMerrill]

Specifically, I am using PDSOLEDB.DLL connecting to SQL 7.

Take care,
--Shaun Merrill
Seattle, WA

 

[SMerrill]

So the program you pointed me to is set up to choose only from the default values. I cannot ad-hoc enter a date-range parameter using the form provided.

I wish when Crystal Decisions made a VB example, they would make the user interface as close as possible to the one that comes up when you launch the report!

 

[FVTrainer]

Ah...a though occurs to me as I review your code one more time...

Sometimes ASP pages have a hard time using the named crystal value types (like crRangeValue). So, assuming your code looks like:

SELECT CASE .DiscreteOrRangeKind
CASE crRangeValue
With Session("oRpt&quot.ParameterFields.GetItemByName("DateParameter&quot
.ClearCurrentRange
.AddCurrentRange CDate("2001/Nov/01&quot, CDate("2001/Nov/30&quot, 2 + 1
End With

Try changing it to:


SELECT CASE .DiscreteOrRangeKind
CASE 1 ' which the return value of crRangeValue
With Session("oRpt&quot.ParameterFields.GetItemByName("DateParameter&quot
.ClearCurrentRange
.AddCurrentRange CDate("2001/Nov/01&quot, CDate("2001/Nov/30&quot, 2 + 1
End With

END SELECT

 

[SMerrill]

Nice thought. I explicitly defined those constants myself with the CONST command.

But just on a whim, I tried the literal string dates exactly as you had typed, and IT WORKED!
What I had been hiding from the forum was the fact that I was trying to do this in very generic ASP code, passing the dates in with Session.QueryString parameters.
Then I found a typo in the querystring which had been typed in to the Address bar days ago, and due to Intellisense, the error persisted.

You have been wonderfully persistent in getting this solved for me. CASE CLOSED.