String OR Date in ONE COLUMN

[alarina]

Hello guys,

I am sorry if I am asking a stupid question but I would like to check with somebody else other than Google whether what my client wants is possible.

PROBLEM DESCRIPTION: in Cognos reports we have some columns with dates. It happens that date columns sometimes contain null values for some lines. The thing is that our client does not want to have empty cells. That is why he asked us to implement this scenario which shows a text if no date is available:

IF (Date is not null)
THEN (Date)
ELSE ('No date available')

I understand that Cognos wants one type per column (that is why I am getting "Datatype Mismatch in THEN/ELSE expression.") But I wanted your opinion: is there no way to force Cognos accept different data types in one column? Like ticking off somewhere "Yeah, I know it is bad and I will take the consequences of it but please allow String and Date [Integer/Double/etc] co-exist in one column?"

One more remark: making a string out of a date is not an option - our client wants to filter his date columns in Excel, and he would not like to bother with reconverting string to date again...

THANKS in advance for your advice!

[Lynn]

Hello guys,

I am sorry if I am asking a stupid question but I would like to check with somebody else other than Google whether what my client wants is possible.

PROBLEM DESCRIPTION: in Cognos reports we have some columns with dates. It happens that date columns sometimes contain null values for some lines. The thing is that our client does not want to have empty cells. That is why he asked us to implement this scenario which shows a text if no date is available:

IF (Date is not null)
THEN (Date)
ELSE ('No date available')

I understand that Cognos wants one type per column (that is why I am getting "Datatype Mismatch in THEN/ELSE expression.") But I wanted your opinion: is there no way to force Cognos accept different data types in one column? Like ticking off somewhere "Yeah, I know it is bad and I will take the consequences of it but please allow String and Date [Integer/Double/etc] co-exist in one column?"

One more remark: making a string out of a date is not an option - our client wants to filter his date columns in Excel, and he would not like to bother with reconverting string to date again...

THANKS in advance for your advice!

The restriction you are describing is imposed by the database, not Cognos per se. I would suggest that you unlock the report layout and add a text item alongside the date data item. Then you can apply a render variable to both such that the date displays only when the date is not null and the text displays only when the date is null. This is done in the layout, not in the query, so be aware of rendering time if you are producing a large data dump.

Speaking of large data dumps, if your client wants to implement filtering in Excel then you may be missing a trick. If you can identify what problem is meant to be solved in Excel using the output from Cognos you may be able to produce a report that facilitates the analysis without the repetitive manual process that would be required in the Excel world.

[alarina]

The restriction you are describing is imposed by the database, not Cognos per se. I would suggest that you unlock the report layout and add a text item alongside the date data item. Then you can apply a render variable to both such that the date displays only when the date is not null and the text displays only when the date is null. This is done in the layout, not in the query, so be aware of rendering time if you are producing a large data dump.

Speaking of large data dumps, if your client wants to implement filtering in Excel then you may be missing a trick. If you can identify what problem is meant to be solved in Excel using the output from Cognos you may be able to produce a report that facilitates the analysis without the repetitive manual process that would be required in the Excel world.

Thank you, Lynn, it was easier than I thought! All thumbs up for the proposed solution! :D

[Lynn]

Hooray! Glad it helped.