BETWEEN parameter

[Shoji Kaburagi]

Hello,

We are developing a web service that loads TRDX files that users created into XmlReportSource and renders PDF files and send back to web clients using RenderReport of a ReportProcessor object. In TRDX files, our users want to set two parameters in SQL to create a SQL statement that contains BETWEEN with a begin date and a end date and use just one parameter to pass in, separated by commas.

The SQL statement is something like:

SELECT * FROM APPLICANT
WHERE DATE_CREATED BETWEEN @BeginDate AND @EndDate

Since our users have accustomed to pass just one parameter to accomplish this, they are unwilling to create two report parameters: one for begin date and one for end date. They want to create just ONE report parameter to basically parse the report parameter value into two SQL parameter values.

I have created a report parameter type string that receives the begin date and the end date separated by a comma ex. 1/1/2013,12/31/2013.

In the data source side, I would like to do something like:
Parameters:
Name: @BeginDate
Type: DateTime
Value:=CDate(Split(",",  Parameters.ReportDate.Value[0]))

Name: @EndDate
Type: DateTime
Value:=CDate(Split(",",  Parameters.ReportDate.Value[1]))

Of course this give me errors at [ characters.

Is there anyway to define the parameter?

Thanks

[Nasko]

Hello Shoji,

You can create a User Function and pass to it the string containing the begin and end dates together with a control parameter. Split the string inside the user function and return the begin or end date depending on the passed control parameter. Then you can use the user function inside an expression.

Regards,
Nasko
Telerik

[Shoji Kaburagi]

Got it!

Thanks!