Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Newbie ReportNet 1.1 Question

Status
Not open for further replies.

C4rtm4N

MIS
Sep 29, 2004
54
GB
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
 
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.

Make sure that the package is published with the proper RDBMS function-set (here SQL server)..

Ties Blom

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top