Hi
We're utilising a system that has Cognos reports heavily integrated into it but we do have a certain amount of scope to add our own custom versions as long as we don't alter any of the stored procedures or tables that sit behind things. We're happy with this arrangement but have come across an issue when we try to add some filters to copies of the reports that are already there (& surprise, surprise the vendor won't support us with what we're doing)
All I need to do is to add a filter to a report that will not show records that are for time periods shorter than a particular time that will be entered as a prompt but despite repeated attempts I can't get it to work
SQL Server 2000 is running as the back end & I'm creating a data item by subtracting one timestamp from another which clearly gives me a time. I'm assuming that I need to cast this value & then do the maths but I can't cast to anything other than a date or an int value & Cognos get's really funny re the the sql syntax that I'm using
cast(([Exception End Time]-[Exception Start Time]) as int)
works for instance (although is useless to me as the resultant numbers ranges from a couple of seconds to a couple of hours) but elsewhere I seem to have to use a comma instead of the "as".
The Cast_float function which I would expect to use validates but the entire report shows the following error when it is validated
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-228'. UDA-SQL-0219 The function "cast_float" is not available as an external, database, or built-in function.
If I try a similar syntax to my working cast but try to go to a float data type, again the formula validates but the entire report returns the following error
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-239'. UDA-EE-0093 The operation "cdouble" is invalid for the data type "interval". UDA-EE-0093 The operation "cdouble" is invalid for the data type "interval". UDA-SQL-0460 A general exception has occurred during local processing.
Can anyone give me a pointer as to what the correct syntax should be & how I can overcome this issue? Can I put a filter on the time value itself?
Also, is there any way to format a time value such as "5 minutes 35 seconds" to a much more civil "5:35" or even 335 (ie the number of seconds but without the word seconds after it)
TIA
Steve
We're utilising a system that has Cognos reports heavily integrated into it but we do have a certain amount of scope to add our own custom versions as long as we don't alter any of the stored procedures or tables that sit behind things. We're happy with this arrangement but have come across an issue when we try to add some filters to copies of the reports that are already there (& surprise, surprise the vendor won't support us with what we're doing)
All I need to do is to add a filter to a report that will not show records that are for time periods shorter than a particular time that will be entered as a prompt but despite repeated attempts I can't get it to work
SQL Server 2000 is running as the back end & I'm creating a data item by subtracting one timestamp from another which clearly gives me a time. I'm assuming that I need to cast this value & then do the maths but I can't cast to anything other than a date or an int value & Cognos get's really funny re the the sql syntax that I'm using
cast(([Exception End Time]-[Exception Start Time]) as int)
works for instance (although is useless to me as the resultant numbers ranges from a couple of seconds to a couple of hours) but elsewhere I seem to have to use a comma instead of the "as".
The Cast_float function which I would expect to use validates but the entire report shows the following error when it is validated
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-228'. UDA-SQL-0219 The function "cast_float" is not available as an external, database, or built-in function.
If I try a similar syntax to my working cast but try to go to a float data type, again the formula validates but the entire report returns the following error
QE-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-239'. UDA-EE-0093 The operation "cdouble" is invalid for the data type "interval". UDA-EE-0093 The operation "cdouble" is invalid for the data type "interval". UDA-SQL-0460 A general exception has occurred during local processing.
Can anyone give me a pointer as to what the correct syntax should be & how I can overcome this issue? Can I put a filter on the time value itself?
Also, is there any way to format a time value such as "5 minutes 35 seconds" to a much more civil "5:35" or even 335 (ie the number of seconds but without the word seconds after it)
TIA
Steve