Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: gats1527 on 02 Oct 2009 12:24:32 PM

Title: Filtering for maximum Date/Time
Post by: gats1527 on 02 Oct 2009 12:24:32 PM
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
Title: Re: Filtering for maximum Date/Time
Post by: kulkarni on 05 Oct 2009 01:53:29 PM
Have you tried adding a filter that says something like below?
date_time_col = maximum(date_time_col)
Title: Re: Filtering for maximum Date/Time
Post by: gats1527 on 05 Oct 2009 02:58:29 PM
I have tried that but when i go and validate that expression, i always receive a parsing error or an inappropriate SQL Request error.
Title: Re: Filtering for maximum Date/Time
Post by: kulkarni on 05 Oct 2009 02:59:49 PM
What is the error you are receiving?  Have you tried this in Summary filter or deail filter?
Title: Re: Filtering for maximum Date/Time
Post by: gats1527 on 05 Oct 2009 03:02:14 PM
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.
Title: Re: Filtering for maximum Date/Time
Post by: kulkarni on 05 Oct 2009 03:31:27 PM
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
Title: Re: Filtering for maximum Date/Time
Post by: gats1527 on 05 Oct 2009 03:42:42 PM
i have tried modfying the data item and still the same result.
Title: Re: Filtering for maximum Date/Time
Post by: TomasF on 06 Oct 2009 01:11:22 AM
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
Title: Re: Filtering for maximum Date/Time
Post by: gats1527 on 06 Oct 2009 07:15:18 AM
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.
Title: Re: Filtering for maximum Date/Time
Post by: TomasF on 06 Oct 2009 08:05:42 AM
The report without the filter works ? Strange.
Try to investigate the generated SQL for the query.

     Tomas
Title: Re: Filtering for maximum Date/Time
Post by: kulkarni on 06 Oct 2009 08:14:30 AM
Quote from: gats1527 on 06 Oct 2009 07:15:18 AM
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.
Text only | Text with Images