Filtering for maximum Date/Time

[gats1527]

I have developed a report based on a workflow we are using to tracking actions by certain users.  When i first generate this report, it shows all the various pahses that instance has gone through.  I would like to only have the latest phase to show on the report.  This report has a receive date fields which i have tried to set to maximum as the expression, but still does not work.  Anyone have any suggestions.  Thanks

[kulkarni]

Have you tried adding a filter that says something like below?
date_time_col = maximum(date_time_col)

[gats1527]

I have tried that but when i go and validate that expression, i always receive a parsing error or an inappropriate SQL Request error.

[kulkarni]

What is the error you are receiving?  Have you tried this in Summary filter or deail filter?

[gats1527]

I have tried in both the summary and detail filter.  The error message states"Deferred Compare could not be completed. The provider could not support a required property.

[kulkarni]

Can you try this?

1. Remove the filter we have been discussing about
2. Modify your query item that is date to maximum(date_col)

See attached spec in 8.2

[gats1527]

i have tried modfying the data item and still the same result.

[TomasF]

Hi,
I have tried this solution and it works:

I have data item [Date] in my query, I created data item [maxDate] = maximum ([Date] for report) and added detail filter [Date] = [maxDate].

       Tomas

[gats1527]

I was able to add the MaxDate data item with no issues but when i added the filter, i received the below error message when trying to validate it.

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements.

[TomasF]

The report without the filter works ? Strange.
Try to investigate the generated SQL for the query.

     Tomas

[kulkarni]

I was able to add the MaxDate data item with no issues but when i added the filter, i received the below error message when trying to validate it.

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft OLE DB Provider for SQL Server]Could not execute query against OLE DB provider 'SQLOLEDB'. The provider could not support a required property. The provider indicates that conflicts occurred with other properties or requirements.

All these solutions do work, not sure why you are seeing this error.  I suspect it may be due to the way your query is.  May be you should create a fresh new report, with just this date item and try to retrieve the maximum of it and then work forward.