Incompatible data types in case statement.

[zemkos]

Hi guys,

I have a bit problem with set up of my Report.

I have data with Year number. And I need to arrange this in three year circles e.g. 2008-2009-2010, 2009-2010-2011, 2010-2011-2012....

I tried to get average when the Year Number is x or x+1 or x+2
CASE
WHEN ([Query1].[Year Nr] = (cast ([Year Nr],varchar( 8 ))) or [Query1].[Year Nr]= (cast ([Year Nr]+1,varchar( 8 ))) or [Query1].[Year Nr]=(cast ([Year Nr]+2,varchar( 8 ))))
THEN ( average (aggregate([Hours])  for [Year Nr]))
ELSE ('')
END

And this results in Incompatible data types in case statement.

Connection is Query2 <- Query1

Is there any possibility to use <dataitem> =/in <dataitem> instead of filter?

The other option might be to create queries with filters for the years, but this exclude possible prompt on  [Year Nr] which will be in place.

Thank you.

zemkos

[BigChris]

If I understand your requirement, I think you want something a little more like:

Code Select Expand

CASE
WHEN ([Query1].[Year Nr] = (cast (?Year Nr?,varchar( 8 )))
   or [Query1].[Year Nr] = (cast (?Year Nr?+1,varchar( 8 )))
   or [Query1].[Year Nr] = (cast (?Year Nr?+2,varchar( 8 ))))
THEN ( average (aggregate([Hours])  for [Year Nr]))
ELSE (NULL)
END

[zemkos]

BigChris,

not exactly. I have masterdata Query (Query1) with items like (Year,Name,startdate,hours,nsr,...) and I need to put 3-year cycles from the masterdata query to query2 (Query2 is now Year, Year+1, Year+2).

In other words, if Year in Query1=Year in Query2 OR Year in Query1=Year+1 in Query2 OR Year in Query1=Year+2 in Query2; then average all hours for these three years.

Yours advise would work if the prompt was one time use, but we have 12 years from 2005 to 2017 and the cycles would be:
2008-2009-2010
2009-2010-2011
2010-2011-2012
.
.

And in your formula I would have to have data items for every cycle and with every new year, I would need to add new cycle manually.

So I tried:

Code Select Expand

CASE
WHEN ([Query1].[Year Nr] = (cast ([Year Nr],varchar( 8 )))
   or [Query1].[Year Nr] = (cast ([Year Nr]+1,varchar( 8 )))
   or [Query1].[Year Nr] = (cast ([Year Nr]+2,varchar( 8 ))))
THEN ( average (aggregate([Hours])  for [Year Nr]))
ELSE (NULL)
END


and also

Code Select Expand

CASE
WHEN ([Query1].[Year Nr] = [Year Nr]
   or [Query1].[Year Nr] = [Year Nr1]
   or [Query1].[Year Nr] = [Year Nr2])
THEN ( average (aggregate([Hours])  for [Year Nr]))
ELSE (NULL)
END

But I am not able to get it work. And I am not sure it is possible to use it like this.

zemkos

[AnalyticsWithJay]

First, to comment on your "incompatible types" error:

Code Select Expand


THEN ( average (aggregate([Hours])  for [Year Nr]))
ELSE ('')


In the THEN statement, you're returning a numeric value, and in your ELSE statement, you're returning a string. Cognos requires one data type only.

Also, I don't know your data, but if Query1.Year is numeric you should not be converting the other data item to a varchar for comparison.

Try to simplify your code:

Year

Code Select Expand

[Query1].[Year Nr]

Three Years

Code Select Expand

[Query2].[Year] + 2

Calculation

Code Select Expand


average(
CASE WHEN [Year] <= [Three Years]
  THEN [Hours]
  ELSE 0
END
  for [Year]
)


Set the aggregate properties to Calculated for the calculation.