CODEWITHSUNDEEP
reportcognos
Amsakanna
Amsakanna

Reputation: 12934

Cognos: Using DataItem value in conditional variable expression

I've a list which has a column 'YEAR'. I want to make the recent year(2010) appear bold in that 'YEAR' column. I tried this expression

IF ( [myQuery].[YEAR] = [myQuery].[RECENT_YEAR]) THEN ( "RecentYearBold" )

where [RECENT_YEAR] is a DataItem with the expression maximum([YEAR])

This throws me an error and it doesn't allow me to use the DataItem in conditional variable expression. Can anybody help?

Error I get:

RSV-VAL-0002 Invalid expression IF ( [myQuery].[YEAR] = [myQuery].[RECENT_YEAR] ) THEN ( "RecentYearBold" ). CRX-API-0005 An error on or around the position '34'. The variable named '[myQuery].[RECENT_YEAR]' is invalid.

Upvotes: 1

Views: 22094

Answers (2)

Francisco Colmenares
Francisco Colmenares

Reputation: 31

The problem is that Cognos 8 strips away variables from a query that are not being used by the report object (in your case the List object). IF you want them available you need to add the query item to the data properties of the Report Object. Note that you CAN add the item to your list (but then you have to hide it, this is ugly). It's much better to instruct Cognos to include the query item in the report object to avoid unnecessary gimmicks.

This link explains the problem and solution in greater detail: http://www-01.ibm.com/support/docview.wss?uid=swg21339433

Upvotes: 3

jamz
jamz

Reputation: 5311

I was able to accomplish what you are trying (after getting the same error you did) by taking the following approach:

  1. I created the [recent_year] column to be the following: maximum ([cognos_test].[year] for report). (You could use a different scope, if desired.)
  2. Created a boolean variable in Conditon Explorer called recent year with the following expression: [Query1].[year] = [Query1].[recent_year]
  3. I added the year column to the list, set the style variable = recent year and applied the conditional bold formatting to the year column when recent year = true
  4. At this point, when I executed the report, I received the same error you did. Validating the report gave a little more information, which indicated that the [Query1].[recent_year] field needed to be in the list as well. When I added it to the list, the conditional formatting worked. (Really not sure why this was the case, but I went with it.)
  5. To hide the [recent_year] column (while still leaving it in the list) you can set the "Box Type" property for the "List Column Body" and "List Column Title" objects for the recent_year column to "None".

Upvotes: 1

Related Questions