How to search for % in a string in Cognos Report Studio

If you need to search in a report for % it will not work. The % character is reserved in SQL.

The solution is to use \% (Backslash Percentage)

How to concat in Cognos report studio, blank / null problem Oracle

In certain reports, either data contains null values or the results of a complex query can return null values.
When performing a calculation, null values will cause the calculation to return a null value.
When concatenating names consisting of  surname, prefix and initials if one of the 3 is missing the result is a complete blank.

In most cases this may not be the desired result.

Only Oracle databases have this problem, other databases work fine.
IBM states its Oracles fault because Oracle does not do standard SQL.

One of the solutions is to change the data-items you want to concatenate so that a value is returned if the field is a blank. Here is an example:

[DATABASE].[EMPLOYEE].[PREFIX]

Change the above data-item into:
if
( [DATABASE].[EMPLOYEE].[PREFIX] is  NULL)
then
(  '  ' )
else
( [DATABASE].[EMPLOYEE].[PREFIX] )

Or change the above data-item into:
coalesce (  [DATABASE].[EMPLOYEE].[PREFIX] ; '  ' )



Do this with all the fields that may contain any blanks or null values in the whole Oracle database.
After this is done it is finally possible to concatenate the several database-fields in Oracle even if one field is NULL or blank.

How to count only certain data in Cognos Report Studio?

Sometimes a count needs to be made on data that needs to meet certain requirements.
There are several ways to accomplish this. Here are two examples.


Example 1:

COUNT ( IF ( [FrameworkField] <= 2700 ) THEN (1) ELSE (0) )



Example 2:

CASE WHEN ( [FrameworkField] <= 2700 ) THEN (1) ELSE (0) END


Put either of these in a 'data-item'.
You can now use it in a list and do a Total on it.

How to strip the time from a date_time in Cognos Report Studio

Edit the definition of the date/time field in Framework Manager.

cast( [field in framework] as DATE )

Cognos Report Studio: How to replace '1' and '0' with 'Yes' and 'No'

If you need to replace the values '1' and '0' with 'Yes' and 'No' in Cognos Report Studio you will need to add a new Data Item with an Expression Definition.

- Open the report in Cognos Report Studio and open the query in the query explorer.

- Add a new Data Item and use as Expression Definition:
if([Databasefield] <> '1')
then ('No')
else
('Yes')

- Now open the page explorer and add the Data Item as a column. You will now see a list containing Yes and No values.