Syntax error - How to correct it. 1

[ReportingAnalyst]

I am passing a start and end date from ASP to CR 8.5

session("oRpt").RecordSelectionFormula = "{AMP.DateOfPost}>= Date(strStartDate) and {AMP.DateOfPost}<= Date(strEndDate)"

When I do this, I get an error on the browser:

Error Occurred Reading Records: The ) is missing.

In the above formula,

strStartDate and strEndDate are form variables:

'Request for the startDate
strStartDate = Request.form("StartDate")

'Request for the endDate
strEndDate = Request.form("EndDate")

The format in the above form variables are

mm/dd/yyyy

Please suggest how I can rectify this selection formula syntax.

Thanks.

 

[Madawc]

I know nothing about ASP. In Crystal 8.5, one would choose Report > Selection Formulas > Records and then code

AMP.DateOfPost}>= Date(strStartDate) 
and 
{AMP.DateOfPost}<= Date(strEndDate)

Does that help?

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[ReportingAnalyst]

When I do this within Crystal:

{AMP.EmpID} = {?EmpId} and
{AMP.DateOfPost}>= Date(strStartDate) and
{AMP.DateOfPost}<= Date(strEndDate) and
{AMP.DateOfPost} > Date (2006,1,1)

I get a message that ) is missing....Same as in the web browser.

Is it valid to put ASP variables within Crystal?

Thanks.

 

[Turkbear]

Hi,
No, crystal will not 'see' the VB variables..

Before constructiing your record selection formula try a

Response.Write("{AMP.DateOfPost}>=" & strStartDate & " and {AMP.DateOfPost}<= " & strEndDate)


To check on exactly what is being sent to the Date() function..






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportingAnalyst]

When I do a response.write,

I see this:

{AMP.DateOfPost}>=1/31/2006 and {AMP.DateOfPost}<= 9/30/2006

Now the date is not the same format as the date in Crystal. Could that be the case?

Thanks.

 

[Turkbear]

Hi,
That shoukd work once youi add the Date() to each, it wa sjust a test to see the actual format of the strings..They should be fine as args to the Date function, but I suspect the Variable is not being evaluated ..

Try this for yours:

session("oRpt").RecordSelectionFormula = "{AMP.DateOfPost}>= Date(" & strStartDate & ") and {AMP.DateOfPost}<= Date(" & strEndDate & ")"

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportingAnalyst]

I do not get any error. Response.Write writes the following:

However within the record selection formula editor, the following is the code:

{AMP.EmpID} = {?EmpId} and
{AMP.DateOfPost} in{?Post Date Range} and
{AMP.DateOfPost} > Date (2006,1,1)

It is looking for EmpID as well.

I do pass the EmpID

I double-checked the order of my parameters.

It is the same order as my parameters in the ASP, though date range passed in the form of recordselectionformula while Empid is passed as Parameter 2

Can we do a mix of both or do I need to include even the EmpID in the RecordSelectionFormula?

 

[ReportingAnalyst]

I commented the parameter for EmpID and instead tried this too.

{AMP.EmpID} = 9999 and {AMP.DateOfPost}>=1/31/2006 and {AMP.DateOfPost}<= 9/30/2006

for the RecordSelectionFormula

But that also did not work.

 

[Turkbear]

Hi,
You did not post what Response.Write shows with the amended formula I posted.

Also, what is {?Post Date Range} ? I do not see that in your assigment code..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportingAnalyst]

When I did a response.write for the RecordSelectionFormula:

session("oRpt").RecordSelectionFormula = "{AMP.DateOfPost}>= Date(" & strStartDate & ") and {AMP.DateOfPost}<= Date(" & strEndDate & ")"

It looks like this:

{AMP.DateOfPost}>= Date(1/31/2006) and {AMP.DateOfPost}<= Date(9/30/2006)

My report has 2 parameters:

1. ?Post Date Range
2. EmpID

In ASP, my form has a strStartDate and strEndDate

So the whole issue was to pass these 2 form variables to ?PostDateRange

Is the whole idea about passing paramters to a date range crystal parameter clearer?

Thanks.

 

[Turkbear]

Hi,
You are not usng the ?PostDate parameter in your code so be sure your record selection criteria dos not reference it.


I seem to remember that when passing a SF to Crystal using the Report Object, the SF adds that formula to any existing criteria defined in your report - since you are not providing any values for the Range parameter, be sure you do not use in anywhere in your criteria...

Please post the actual Record Selection formula in your report.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
Just another note..Check the docs about using the parameters collection of your report object to specify values for a parameter defined in that report - it is not done by adding a selection formula but by assigning values directly and specifying what type parameter it is( Range, Single, Multiple Discreet, etc.)
Here is some code from the asp samples on the Business objects web site:

====================================================================================
                                                                     
set session("ParamCollection") = Session("oRpt").Parameterfields              
'This line creates an object to represent the collection of parameter
'fields that are contained in the report.                                                                      
																	  
set Param1 =  session("ParamCollection").Item(1)
'This line creates an object to reference the first parameter in the 
'report.  

Param1.EnableMultipleValues = 1
ParamValue = "USA"
ParamValue1 = "Canada"
'This line creates a temporary variable to store the value to pass to
'the paraemter field.

Call Param1.AddCurrentValue (CStr(ParamValue))
Call Param1.AddCurrentValue (CStr(ParamValue1))

'This line calls the AddCurrentValue method of the parameter field
'object to add the value to the parameter field the  of the ParamValue variable.
'
'The parameter does not need be cast with a value type as it will be picked from the report.  
'Do not try casting a value type, as the AddCurrentValue method does not support that 
'many arguments as there is with the SetCurrentValue Method.

'    Do the next parameter field.  In the report being used in 
'    this example, the second parameter is a number parameter
'    field.

set Param2 = session("ParamCollection").Item(2)
NewParamValue = "This is the second new parameter value (the report title)"
Call Param2.SetCurrentValue(CStr(NewParamValue),12)

'In the second parameter, we are only passing a single valued parameter so we can use 
'the SetCurrentValue method. 
'It is also possible to use the AddcurrentValue method for single valued parameters.
'but not vice versa. 
'Since we are passing a VBScript variable as the value for the parameter
'we must cast the variable as the proper datatype before passing it to
'the report.  This is because VBScript variables are not datatyped unless
'cast.

'Below is a list of the VBScript cast functions used to convert the variant 
'to the correct data type.
     
'    Param Field Type    VBScript Call      Type Number
'
'    NumberField             CDbl                7
'    CurrencyField           CDbl                8
'    Boolean                 CBool               9
'    StringField             CStr                12
                                                                      

'====================================================================================

Altho' these do not deal with Range types, the technique is similar..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Turkbear]

Hi,
One more example from XI's asp code for Ranged Parameters:

'This section shows enabling a parameter to accept a ranged parameter.
'The first line sets the DiscreetOrRangeKind to 1. For Ranged parameters the constant is 1.
'The second line uses the AddCurrentRange method which takes three arguments.
' AddCurrentRange LowerBoundValue, UpperBoundValue, CRRangeInfoConstant
'The CRRangeInfoConstant of 3 indicates that the range should include values greater than or equal to
'the lower bound and less than or equal to the upper bound.

session("oRpt").ParameterFields.GetItemByName("ExampleRangedNumberParameter").DiscreteOrRangeKind = 1
session("oRpt").ParameterFields.GetItemByName("ExampleRangedNumberParameter").AddCurrentRange CDbl("5"),CDbl("10"),CDbl("3")


'The sample report shows records from the following table. The record selection formula of the report
'is set to show records based on the parameter values passed into the report. The records are selected 
'on the criteria that 
'a) the employee id number falls in the range specified in the ExampleRangedNumberParameter  
'b) the employee first name matches one of the values passed in the ExampleMultiValuedStringParameter.
'This table is provided to allow you to check the records shown versus the data that is in the table
'based on the parameter values that are set here in the code.
'       ===========================================
'                Data in Employee Table
'       ===========================================
'	Employee ID	First Name	Last Name
'       -----------     ----------      ---------
'	1		Nancy		Davolio
'	2		Andrew		Fuller
'	3		Janet		Leverling
'	4		Margaret	Peacock
'	5		Steven		Buchanan
'	6		Michael		Suyama
'	7		Robert		King
'	8		Laura		Callahan
'	9		Anne		Dodsworth
'	10		Albert		Hellstern
'	11		Tim		Smith
'	12		Caroline	Patterson
'	13		Justin		Brid
'	14		Xavier		Martin
'	15		Laurent		Pereira
%>

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportingAnalyst]

Hi,

The above example is for ASP with Crystal 8.0

I have the following example from ASP 8.5 as you gaveme. I did try that, but to no result.

'Get the EmpID from the form
strEmpID = Request.form("empid")

'Request for the startDate
strStartDate = Request.form("StartDate")

'Request for the endDate
strEndDate = Request.form("EndDate")

'This line sets the DiscreetOrRangeKind to 1. For Ranged parameters the constant is 1.
'session("oRpt").ParameterFields.GetItemByName("RangedDateParameter").DiscreteOrRangeKind = 1

'This line uses the AddCurrentRange method which takes three arguments.
' AddCurrentRange LowerBoundValue, UpperBoundValue, CRRangeInfoConstant
'The CRRangeInfoConstant of 3 indicates that the range should include values greater than or equal to
'the lower bound and less than or equal to the upper bound.

'Reformat the start date to that of Crystal
'splitStartDate = split(strStartDate, "/")
'MyStartDate = splitStartDate(2) & "/" & splitStartDate(0) & "/" & splitStartDate(1) & " 12:00:00 AM"

'Reformat the end date to that of Crystal
'splitEndDate = split(strEndDate, "/")
'MyEndDate = splitEndDate(2) & "/" & splitEndDate(0) & "/" & splitEndDate(1) & " 12:00:00 AM"

'session("oRpt").ParameterFields.GetItemByName("RangedDateParameter").AddCurrentRange CDate(MyStartDate),CDate(MyEndDate),CDbl("3")

See anything missing...

 

[Turkbear]

Hi,
Using a Time in the Date() function can introduce issues , so either use the DateTime() function , being careful to format the string used, or ignore the time component.

CDateTime(2000,01,01,13,04,00) = 1:04 PM on 1/1/2000



Can you specify what you mean by:
no result





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportingAnalyst]

I ignored the time content as you mentioned.

This is what I did:

Set ParamDefCollection = Session("oRpt").Parameterfields
Set session("Param") = ParamDefCollection

'This line uses the AddCurrentRange method which takes three arguments.
' AddCurrentRange LowerBoundValue, UpperBoundValue, CRRangeInfoConstant
'The CRRangeInfoConstant of 3 indicates that the range should include values greater than or equal to
'the lower bound and less than or equal to the upper bound.

'Reformat the start date to that of Crystal
splitStartDate = split(strStartDate, "/")
MyStartDate = splitStartDate(2) & "/" & splitStartDate(0) & "/" & splitStartDate(1)

'Reformat the end date to that of Crystal
splitEndDate = split(strEndDate, "/")
MyEndDate = splitEndDate(2) & "/" & splitEndDate(0) & "/" & splitEndDate(1)

session("oRpt").ParameterFields.GetItemByName("RangedDateParameter").AddCurrentRange CDate(MyStartDate),CDate(MyEndDate),CDbl("3")

'This line creates an object to reference the 2nd parameter in the report.
'EmpID which is numeric should be equal to ?EmpID. Hence cast as double of type number 7. (See the chart below)

Set MyParam3 = session("Param").Item(3)
MyNewParamValue3 = strEmpID
Call MyParam3.SetCurrentValue(CDbl(MyNewParamValue3), 7)

Results:

It takes a long time to open the report.
Report opens up but with no data.

Just wanted to add that within the reports, when I rightclick on the ?Post Date Range parameter to edit,

I can see that when I click on the Set Default Values Button, this paramter has a list of default values which are the last day of the month for the current year of 2006.

I am able to pass ASP parameters to other reports where values were hardcoded in the crystal report, but they are single parameters and not range parameters.

Thanks.

 

[Turkbear]

Hi,
Just as a test, use 2 single-value parameters (StartDate and EndDate)..

Then use those:

{AMP.DateOfPost} In {?StartDate} To {?EndDate}


another note:
In your Range version, you can use the Date() function, instead of CDate(), then you can use strStartDate and strEndDate without processing them first( saving lines of code and potential errors), since
Date(1/31/2006), for instance, will be a valid value..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[ReportingAnalyst]

Hi,

I am trying to remove the references to the ?PostDateRange.

I have created ?StartDate and ?EndDate - added it to the report.

Now I want to make sure that the Crystal script refers to the new date instead of the old ones.

How can I change this to using ?StartDate and ?EndDate...

Totext(Minimum({?Post Date Range}),"MMMM yyyy")+" to "+totext(Maximum({?Post Date Range}),"MMMM yyyy")

I am going into every formula to check if the old paramater was used. But is there a way to see where the parameter is used from any menu ?

Thanks

 

[ReportingAnalyst]

Hi Turkbear....

My reports finally work with the approach you suggested. Thanks for the help and guidelines.

 

[Turkbear]

Hi,
In the Report Explorer view, expand the Parameters list and, if a green check is on a parameter name, it is in use somewhere..If not, you can delete it.

If it shows as being used ( and cannot be deleted) export your report in Report Definition Format and seach that document for all its uses..





To Paraphrase:"The Help you get is proportional to the Help you give.."