[Progresso] SSRS date parameter issue with subscription report

[Ditto]

When setting up progresso subscription reports, it suffers in that any report that needs to run with date parameters that are relative to the runtime date, there's no way to specify that - it just keeps runs with the date when the subscribed report was set up. As I result, I'm having to copy and modify each report to have no date parameter . On further thinking, and to keep modifications minimal and standardised, I decide to use "01/01/1901" as a value to trigger overriding the parameter date to a runtime calculated date, effectively allowing me to have one report that works normally on demand, but also as a subscription report. For example, where a report is for attendance with a start and end date, on the appropriate DataSet I've change the DataSet -> Properties -> Parameters, Parameters value to be something like:

 

=IIf(Parameters!EndDate.Value = "01/01/1901", now(), Parameters!EndDate.Value)

 

Generally this has worked fine but I've hit an issue with a modified version of the system report "Absences for a date range". The code above works fine for the end date. However, for the start date I simply want to set to the first of the current month. Various attempts have led to the following error (at runtime). For example

 

"Semantic query compilation failed: e InvalidParameterValueType The data type of the parameter "Date" is DateTime. However, the value provided for the parameter cannot be converted to this type. (SemanticQuery ''). (rsSemanticQueryEngineError)".

 

One thing I noticed in the associated DataSet is the queried date field appears like this:

whereas for another report that works the relevant date field looks like this

 

I suspect I need to do some kind of conversion but I've not cracked it yet, so any help appreciated.

[NotSoFast]

I've never tried to do it from the first of the month, but our behaviour reports are emailed every night using a filter in the dataset that uses a relative date (last day). That's not quite it though, is it?

[Ditto]

I think I am trying a similar approach as the filter on the dataset takes the parameter dates. For end date, if it's "01/01/1901" (i.e from the subscription) I set end date to today() or now() and it's just fine. For the start date I build something along the lines of =(Year(Now()), Month(Now()), "1").

I'm thinking the issue is related to the odd looking date field on the dataset. It's coming from within the 'progressomodel' which has often baffled me!

 

What I find most odd is when the parameter value is used, it's fine, but as soon as I try a calculated date the error "The data type of the parameter "Date" is DateTime. However, the value provided for the parameter cannot be converted to this type" crops up. I'm considering seeing if I can find another date field, but I didn't want to change the standard report more than minimally.

[NotSoFast]

I wonder if your method of making the start date is creating a string rather than a date. I'm on my phone so can't test it all properly, but if you want to pm me the report I'd be happy to see if i can figure it out, not that I'm an expert!

[djrscally]

It's your method of constructing the date that's the issue I would say. Not at a PC so can't check exactly what's happening, but the two date fields ought to be no different. Wrapping your expression in DateSerial() might work

 

I tend to do this kind of operation with dateadd

 

=DateAdd("d", 1 - Day(Today()) , Today())

[Ditto]

Agreed - somewhere along the way the type doesn't match! I'm not sure if it's because of a string date, something to do with RDL datatypes vs underlying SQL server data types or the odd looking structure in the OP for the date filed. I had tried DateSerial but still hit issues. However, the code below worked late last evening which perhaps lacks elegance but is much in line with what you suggest.

 

=IIf(Parameters!StartDate.Value = "01/01/1901", DateAdd("d",1-DatePart(DateInterval.day,now()), now()), Parameters!StartDate.Value)

Edited April 10, 2017 by Ditto