Query Calculation to Assign 13 Different Hourly Values

[Cognos_Jan2017]

... to display as the only Column in a Crosstab.

Running Cognos 10.2.2.  We want to use time intervals to see what time of day Injuries occur.  There is a Query Item that is the time of day an Injury occurs.

We can write a Query calculation using CASE WHEN to assign values such as ...
1 - '7am to 8am'
2 - '8am to 9am'
3 - '9am to 10am'
4 - '10am to 11am'
5 - '11am to Noon'
6 - 'Noon to 1pm'
7 - '1pm to 2pm'
8 - '2pm to 3pm'
9 - '3pm to 4pm'
10 - '4pm to 5pm'
11- '5pm to 6pm'
12 - '6pm to 7pm'
13 - '7pm to 8pm'

I'm new to Cognos.  How can I use the results from the Query Calculation above to display as a Column in a Crosstab?

I will write another Query calculation to "sort" the above values from 1 thru 13 for ascending display.

Questions welcome.

TIA, Bob

[Cognos_Jan2017]

Any ideas on how to utilize CASE WHEN to get needed results ... >??

Time format entry of the App has number formats, IE, as below ...
9-00          21-00
9-05   
9-10   
9-45         21-45
9-50   
10-00       22-00
10-10   
10-15   
10-30   22-30
10-35   
10-45   
10-50   
10-55   
11-00   23-00
....Left most numbers are from 0, 1, ..., 23
then dash
then 00, 05, 10, 15, 20, 25, 30, 35. 40, 45, 50, 55

Ideally we ...
CASE
WHEN (convert to time ... => 0-00 and < 1-00) then '0000 to 0059'
WHEN (convert to time ... => 1-00 and < 2-00) then '0100 to 0159'
WHEN (convert to time ... => 2-00 and < 3-00) then '0200 to 0259'
WHEN (convert to time ... => 3-00 and < 4-00) then '0300 to 0359'
WHEN (convert to time ... => 4-00 and < 5-00) then '0400 to 0459'
WHEN (convert to time ... => 5-00 and < 6-00) then '0500 to 0559'
WHEN (convert to time ... => 6-00 and < 7-00) then '0600 to 0659'
WHEN (convert to time ... => 7-00 and < 8-00) then '0700 to 0759'
WHEN (convert to time ... => 8-00 and < 9-00) then '0800 to 0859'
WHEN (convert to time ... => 9-00 and < 10-00) then '0900 to 0959'
WHEN (convert to time ... => 10-00 and < 11-00) then '1000 to 1059'
WHEN (convert to time ... => 11-00 and < 12-00) then '1100 to 1159'
WHEN (convert to time ... => 12-00 and < 13-00) then '1200 to 1259'
WHEN (convert to time ... => 13-00 and < 14-00) then '1300 to 1359'
WHEN (convert to time ... => 14-00 and < 15-00) then '1400 to 1459'
WHEN (convert to time ... => 15-00 and < 16-00) then '1500 to 1559'
WHEN (convert to time ... => 16-00 and < 17-00) then '1600 to 1659'
WHEN (convert to time ... => 17-00 and < 18-00) then '1700 to 1759'
WHEN (convert to time ... => 18-00 and < 19-00) then '1800 to 1859'
WHEN (convert to time ... => 19-00 and < 20-00) then '1900 to 1959'
WHEN (convert to time ... => 20-00 and < 21-00) then '2000 to 2059'
WHEN (convert to time ... => 21-00 and < 22-00) then '2100 to 2159'
WHEN (convert to time ... => 22-00 and < 23-00) then '2200 to 2259'
WHEN (convert to time ... => 23-00 and < 0-00) then '2300 to 2359'
END

TIA, Bob

[BigChris]

I think you've got most of the function that you need...it just looks like you need the "convert to time" part. Take a look at the _make_timestamp function. You might need to combine it with a substring and a charindex (depending on your database) to get the  part before the dash.

Actually, looking at it again, you might be able to do something simpler. You could try something along the lines of:

Code Select Expand


case
when substring([YourTimeField],1,charindex('-',[YourTimeField]-1))='0' then '0000 to 0059'
when substring([YourTimeField],1,charindex('-',[YourTimeField]-1))='1' then '0100 to 0159'
etc.
else 'Unknown'
END


You might need to tweak that a bit, but hopefully it'll give you a starting point.

[Cognos_Jan2017]

Thank you.  Will try your substring and get back w/ you. 

I just tried the below code, unsuccessfully ...
CASE
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 0-00:0-59 } then '0000 to 0059'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 1-00:1-59 } then '0100 to 0159'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 2-00:2-59 } then '0200 to 0259'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 3-00:3-59 } then '0300 to 0359'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 4-00:4-59 } then '0400 to 0459'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 5-00:5-59 } then '0500 to 0559'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 6-00:6-59 } then '0600 to 0659'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 7-00:7-59 } then '0700 to 0759'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 8-00:8-59 } then '0800 to 0859'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 9-00:9-59 } then '0900 to 0959'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 10-00:10-59 } then '1000 to 1059'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 11-00:11-59 } then '1100 to 1159'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 12-00:12-59 } then '1200 to 1259'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 13-00:13-59 } then '1300 to 1359'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 14-00:14-59 } then '1400 to 1459'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 15-00:15-59 } then '1500 to 1559'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 16-00:16-59 } then '1600 to 1659'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 17-00:17-59 } then '1700 to 1759'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 18-00:18-59 } then '1800 to 1859'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 19-00:19-59 } then '1900 to 1959'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 20-00:20-59 } then '2000 to 2059'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 21-00:21-59 } then '2100 to 2159'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 22-00:22-59 } then '2200 to 2259'
WHEN ([IIMS - Business Layer].[General Incident Details].[Time Of Incident]) IN_RANGE { 23-00:23-59 } then '2300 to 2359'
END

[Cognos_Jan2017]

BigChris - your code for the 2 WHENs validated.  All I have to do to add the other 22 WHENs.

Should work fine, and will confirm here.

Thanks again.

[Cognos_Jan2017]

All the substring code validated, but adding that as the only column in a crosstab tried to run but resulted in ...
   An error occurred while performing operation 'sqlOpenResult' status='-9'.

Your thoughts?  Thank you, Bob

[Cognos_Jan2017]

Trying to add the Query Calculation in a row of the crosstab (after cutting that calculation from the crosstab column)
results in the same ...
An error occurred while performing operation 'sqlOpenResult' status='-9'.

[Cognos_Jan2017]

Same error when adding that code in a Query Calculation
in a new List w/ only [Time of Incident] and the Query Calculation.

An error occurred while performing operation 'sqlOpenResult' status='-9'.
Should mean, even though the code Validated, something wronq w/ the code?

TIA - Bob

[Cognos_Jan2017]

BigChris - You have helped me a tot.  THANK you.

I have used VBA's InStr a lot, so I think I understand how Cognos" substring works including charindex.

If you would like to check the ode, which validates, maybe you will see something sausing the Cognos error ...

case
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='0' then '0000 to 0059'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='1' then '0100 to 0159'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='2' then '0200 to 0259'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='3' then '0300 to 0359'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='4' then '0400 to 0459'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='5' then '0500 to 0559'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='6' then '0600 to 0659'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='7' then '0700 to 0759'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='8' then '0800 to 0859'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='9' then '0900 to 0959'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='10' then '1000 to 1059'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='11' then '1100 to 1159'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='12' then '1200 to 1259'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='13' then '1300 to 1359'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='14' then '1400 to 1459'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='15' then '1500 to 1559'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='16' then '1600 to 1659'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='17' then '1700 to 1759'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='18' then '1800 to 1859'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='19' then '1900 to 1959'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='20' then '2000 to 2059'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='21' then '2100 to 2159'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='22' then '2200 to 2259'
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident]-1))='23' then '2300 to 2359'
END

[Cognos_Jan2017]

The 3rd party App integrates Cognos for Reporting.  They do not permit Users to see Table design.

The datatype for their 'Time of Incident' is probably a DateTime.  Therefore, substring shouldn't work when looking at a value like 17-05.

I will be able to reach the 3rd party developer on Monday to confirm this, and see if he will recommend code to convert the premised DateTime to a string ... then BigChris' code should work.

Anyone else w/ suggestions?

[BigChris]

Could you give us the full error message Cognos_Jan2017? That might give us a little more to go on.

[Cognos_Jan2017]

The Cognos error, when running to Excel 2007, is ...
An error occurred while performing operation 'sqlOpenResult' status='-9'.

I read an article that said Cognos has been known to use
... An error occurred while performing operation 'sqlOpenResult' status='-9' ...
as a "catch-all" to an undetermined error.

BigChris, as you said earlier, I suspect the 'Time of Incident' field is
a datatype DateTime.  I just asked the App's developer if that is true
as Users are not permitted to see the schema.

I WILL post the App developer's reply when I receive that.

If so, Cognos code to convert a DateTime such as 1-00, 18-45, ...
to a string should make the substring code work.  That code does
validate.

It could help, as everyone here might not read this Topic.

I will see if someone else can assist in converting a DataTime to a string to
have substring work.

TIA, Bob

[Cognos_Jan2017]

App developer replied ...
Robert, not sure, the field is not shown as a date-time field. Not sure why you are receiving the error related to Excel 2007. Does it fail if you run in HTML?

I tried running in HTML, PDF, Excel 2007, and Excel 2002.  All result in the "-9" error.

[Cognos_Jan2017]

Relational model.  The App developer said ...
... the field is not shown as a date-time field. Not sure why you are receiving the error related to Excel 2007. Does it fail if you run in HTML?

From VB, I thought the "values" such as 2-00, 13-45, etc were a datatype DateTime field, thereby causing reading that thru substring to fail.

When running to HTML, PDF, Excel 2007, and Excel 2002, it results in ...
An error occurred while performing operation 'sqlOpenResult' status='-9'.

I read an article that said Cognos has been known to use
... An error occurred while performing operation 'sqlOpenResult' status='-9' ...
as a "catch-all" to an undetermined error.

I have simplified the code below ...

case
when substring([Time Of Incident],1,charindex('-',[Time Of Incident]-1))='0' then '0000 to 0059'
when substring([Time Of Incident],1,charindex('-',[Time Of Incident]-1))='1' then '0100 to 0159'
END

Anyone w/ thoughts on what is causing the error "-9"?

TIA - Bob

[BigChris]

What do you get if you try

Code Select Expand

hour([IIMS - Business Layer].[General Incident Details].[Time Of Incident])

[Lynn]

Relational model.  The App developer said ...
... the field is not shown as a date-time field. Not sure why you are receiving the error related to Excel 2007. Does it fail if you run in HTML?

From VB, I thought the "values" such as 2-00, 13-45, etc were a datatype DateTime field, thereby causing reading that thru substring to fail.

When running to HTML, PDF, Excel 2007, and Excel 2002, it results in ...
An error occurred while performing operation 'sqlOpenResult' status='-9'.

I read an article that said Cognos has been known to use
... An error occurred while performing operation 'sqlOpenResult' status='-9' ...
as a "catch-all" to an undetermined error.

I have simplified the code below ...

case
when substring([Time Of Incident],1,charindex('-',[Time Of Incident]-1))='0' then '0000 to 0059'
when substring([Time Of Incident],1,charindex('-',[Time Of Incident]-1))='1' then '0100 to 0159'
END

Anyone w/ thoughts on what is causing the error "-9"?

TIA - Bob

Error messages generally have a Details link that you can click to find out more information about the error. It isn't always helpful but there are times when it gives more clues. Can you see if there is anything further in the error details?

What is the datatype of the field you are trying to substring? If you right-click on the query item in your package you should see the datatype.

You are operating on the same field in two different ways. One is with a substring which means it ought to be a string. The other is subtracting 1 as part of your charindex function which means it ought to be a number. Sometimes the database will perform implicit conversions for you but it is best to understand what data type you have and what datatype the functions you are using expect as parameters and what datatype is returned.

Basic troubleshooting techniques will help further. Try to isolate which of those two different aspects is causing the problem (substring or subtraction).

You could try to just substring with hard coded start and length parameters to see if that complains or not. If it is actually a string (and doesn't therefore complain about a simple substring) then you'd want to get the length of in order to subtract 1 from that. If it is not a string perhaps you need to cast it as varchar. I think you want to subtract one outside the charindex function but more specific syntax help only makes sense after you provide answers to the other questions.

[Lynn]

Did you create a new thread that is a duplicate of this problem? http://www.cognoise.com/index.php/topic,32658.0.html

Forum etiquette requests that you do not do this since people may spend time answering something that is already answered. Perhaps it is appropriate to request that a moderator join these posts.

[BigChris]

You are operating on the same field in two different ways. One is with a substring which means it ought to be a string. The other is subtracting 1 as part of your charindex function which means it ought to be a number

Good spot Lynn

Code Select Expand

case
when substring([Time Of Incident],1,charindex('-',[Time Of Incident]-1))='0' then '0000 to 0059'
when substring([Time Of Incident],1,charindex('-',[Time Of Incident]-1))='1' then '0100 to 0159'
END

probably should be

Code Select Expand

case
when substring([Time Of Incident],1,charindex('-',[Time Of Incident])-1)='0' then '0000 to 0059'
when substring([Time Of Incident],1,charindex('-',[Time Of Incident])-1)='1' then '0100 to 0159'
END

[Cognos_Jan2017]

BigChris ... I tried a new List w/ only 'Time of Incident' and the Query Calculation of ...
hour([IIMS - Business Layer].[General Incident Details].[Time Of Incident])

It did not validate, and gave error ...
An error occurred while performing operation 'sqlPrepareWithOptions' status='-56

Also, a long list of reasons no validation.

Lynn .., I didn' know that was Forum protocol.

Mea culpa.  My apology, Bob

The App developer doesn't know what is causing the problem.  He will
try to correct it.

TIA, Bob

[Lynn]

BigChris ... I tried a new List w/ only 'Time of Incident' and the Query Calculation of ...
hour([IIMS - Business Layer].[General Incident Details].[Time Of Incident])

It did not validate, and gave error ...
An error occurred while performing operation 'sqlPrepareWithOptions' status='-56

Also, a long list of reasons no validation.

Are there any details given with the error? Click the details link and post what is displayed. Often there will be some information in the first few lines that is helpful.

Lynn .., I didn' know that was Forum protocol.

Mea culpa.  My apology, Bob

I responded to you other thread including a number of questions that might yield more help for you if you can answer them.

[Cognos_Jan2017]

BigChris ...
Tried the paren chgs ...
case
when substring([Time Of Incident],1,charindex('-',[Time Of Incident])-1)='0' then '0000 to 0059'
when substring([Time Of Incident],1,charindex('-',[Time Of Incident])-1)='1' then '0100 to 0159'
END
.. and got error ...
An error occurred while performing operation 'sqlOpenResult' status='-56'.

Lynn ...
The Linkage to the Field in the Package says ...
Property Value
Name Time Of Incident

Value is what the App developer confirmed yesterday.
He said ...
"Value is a ubiquitous term to define the data item."

Not sure his answer helps.  He is preparing for a
WebEx Users Group Conference tomorrow at 0900.

He (he has a full staff) will have to correct this .. I will
let all know the resolution.

Thanks all.

[Cognos_Jan2017]

I can conclude that 'Time of Incident', while called a "Value", cannot be a DateTime Field.

I should have realized that when submitting this Topic as 'Time of Incident' includes, in its results ...
Time Of Incident
9-00
Time Undetermined
11-55
3-00

It appears, because "Time Undetermined" is allowed as an entry in their design,
it has to be a string ... unless there is something I'm missing.  Maybe too much
Visual Basic programming?  In Cognos, can "Time Undetermined" be something
other than a string?

IF it is a string, then the substring example BigChris and Lynn provided (thank you
both again), which DOES validate, in theory, will work ... since there is no 'T" in the
CASE WHEN, it would ignore that?  I will add a WHEN looking for 'T" just in case that
is causing the error, and let you know.

TIA - Bob

[Cognos_Jan2017]

Trying, to trap for 'T' when the entry for 'Time of Incident'
is "Time Undetermined", results in ...
... An error occurred while performing operation 'sqlOpenResult' status='-56'.

... when trying ...
when substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)='T' then 'Unknown'

Looking more like the onus should be placed on the App developer.

[Cognos_Jan2017]

Response to Lynn #16.

Error is ...
RQP-DEF-0177
An error occurred while performing operation 'sqlOpenResult' status='-56'.

Lynn, from your suggestion ... first few lines ...
includes
Warning: Null value is eliminated by an aggregate or other SET operation.
That's NOT one, as there are NO Nulls entered as 'Time of Incident'

Possibly interesting ...
Invalid length parameter passed to the LEFT or SUBSTRING function ... but what possible invalid length parameter?
Details for the error are ...
UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Warning: Null value is eliminated by an aggregate or other SET operation. (SQLSTATE=01003, SQLERRORCODE=8153)UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Invalid length parameter passed to the LEFT or SUBSTRING function. (SQLSTATE=42000, SQLERRORCODE=537)RSV-SRV-0042 Trace back:RSReportService.cpp(733): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_RequestRSASyncExecutionThread.cpp(864): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(319): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): promptPagingForward_RequestRSASyncExecutionThread.cpp(909): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_RequestRSRequest.cpp(1683): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(636): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(744): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(290): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(279): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(174): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(165): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1160): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1158): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1115): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1091): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(888): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(311): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4522): QFException: CCL_THROW: CoordinationPlanner

[Cognos_Jan2017]

Reply to Lynn #1

Trying ...
substring([IIMS - Business Layer].[General Incident Details].[Time Of Incident],1,charindex('-',[IIMS - Business Layer].[General Incident Details].[Time Of Incident])-1)

An error occurred while performing operation 'sqlOpenResult' status='-56'

Details ...
UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Invalid length parameter passed to the LEFT or SUBSTRING function. (SQLSTATE=42000, SQLERRORCODE=537)RSV-SRV-0042 Trace back:RSReportService.cpp(733): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(864): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(319): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(909): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(600): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(340): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(185): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(314): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(177): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(151): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(446): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSDocAssemblyDispatch.cpp(384): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyRSResultSetIterator.cpp(1506): QFException: CCL_RETHROW: RSResultSetIterator::retrieveDataQFSPartialDataset.cpp(80): QFException: CCL_RETHROW: QFSPartialDataset::GetEdgeIteratorSource/QEDmResultSet.cpp(75): QFException: CCL_THROW: QE