Inserting date parameters into a Crystal report

[halfer]

Dear all

I am having substantial trouble passing a date parameter to the Crystal Reports 9 component from PHP5 on Windows. It should be easy, of course, but the various commented-out items don't seem to work:

$rptParam = $rptParams->Item(2);
$t = $rptParam->ValueType;  // This comes out to 10, which is correct for a Crystal date type

//None of these work
//$rptParam->SetCurrentValue("25/04/2007");
//$rptParam->SetCurrentValue("25-04-2007");
//$rptParam->SetCurrentValue("25 April 2007");
//$rptParam->SetCurrentValue("#25/04/2007#");
//$rptParam->SetCurrentValue('20070425');
//$rptParam->SetCurrentValue("25/04/2007 00:00");
//$rptParam->SetCurrentValue("00:00 25/04/2007");
//$rptParam->SetCurrentValue("00:00 25/04/2007");
//$rptParam->SetCurrentValue("25/Apr/2007");
//$rptParam->SetCurrentValue(date('c'));
//$rptParam->SetCurrentValue(time());

// These don't work either
//$v = new Variant(time(), VT_DATE);
$v = variant_date_from_timestamp(time());
$rptParam->SetCurrentValue($v);

Any thoughts as to what I might be doing wrong? It results on every occasion in these errors:

PHP Fatal error: Uncaught exception 'com_exception' with message 'Source: Unknown Description: Unknown' in C:\dev-misc\crystal.php:57
Stack trace:
#0 C:\dev-misc\crystal.php(57): variant->SetCurrentValue(Object(variant))
#1 {main}
thrown in C:\dev-misc\crystal.php on line 57

Fatal error: Uncaught exception 'com_exception' with message 'Source: Unknown
Description: Unknown' in C:\dev-misc\crystal.php:57
Stack trace:
#0 C:\dev-misc\crystal.php(57): variant->SetCurrentValue(Object(variant))
#1 {main}
thrown in C:\dev-misc\crystal.php on line 57

Incidentally I am not keen on using RecordSelectionFormula as this does not properly register parameters with the report. Meanwhile I have also seen that another approach (but dot net) is possible, but I wonder whether this might require CR 10+, as the ParameterDiscreteValue is not available AFAICT on v9.

Many thanks in advance for any pointers offered.

[byron3@earthlink]

CDate(“August 28, 2001”)
CDate(“2001/08/28”)
CDate(“2001-08-28”)
CDate(“08/28/01”)
all return August 28, 2001.

Documentation @ http://support.businessobjects.com/c...efunctions.pdf

[halfer]

byron3, thank you for your response. Would I be right in thinking that I would need to set up a string parameter type in the report, and then use these functions inside the report? That wouldn't be ideal for us, sadly, as our reports are also employed by our Java side, which seems to be able to specify date parameters correctly.

However if there is a way I can pass a string parameter such as "CDate(“August 28, 2001”)" where the report demands a date, that would be ideal.

[halfer]

Further info: I discover that this should be possible. Our Java team accesses Crystal via a DLL written in C++, the relevant snippet being this:

For each string parameter {

    	PEParameterFieldInfo PARAM_PVI;
    	PEValueInfo PARAM_VI;

	// ...

    	PEGetNthParameterField(jobid, 0, &PARAM_PVI);
    	PARAM_PVI.ValueType = PE_PF_STRING;
        strcpy(PARAM_PVI.Name, param.c_str());
    	strcpy(PARAM_PVI.CurrentValue, value.c_str());
    	PEConvertPFInfoToVInfo(PARAM_PVI.CurrentValue, PARAM_PVI.ValueType, &PARAM_VI);
	PEAddParameterCurrentValue(jobid, param.c_str(), "", &PARAM_VI);

}

I am no C++ expert, but it looks like PARAM_PVI and PARAM_VI are structs, which I imagine I can't access in PHP. I think it is the function PEConvertPFInfoToVInfo that converts the supplied string parameter to the required date/int/etc type. Has anyone got any ideas on how to do this in PHP? -- thanks

[halfer]

OK, this is no great solution, but I prefer it to searching and replacing strings in report->RecordSelectionFormula. I have discovered that I can use the CDate facility in VBScript to do the conversion and supply the parameter in the correct format for COM. I have no idea whether it is PHP at fault here, for not being able to supply parameters in the correct format, or Crystal, for being awkward about what it will accept. But either way, on Windows, this appears to work:

$oScript = new COM("MSScriptControl.ScriptControl");
$oScript->Language = "VBScript";
$oScript->AllowUI = false;
$oScript->AddObject('rptParam', $rptParam, true);
$oScript->AddCode('Function SetDateParameter(strDate)
rptParam.AddCurrentValue(CDate(strDate))
End Function');
$oScript->Run("SetDateParameter", "25 April 2006");

Hope that helps someone - I've spent some considerable time on it! My question does not end here, of course: if there is a better approach, then please, please do post it here.