Hello All,

Quick question about a customized data item in my Cognos report that uses Case When function, the logic is as below:

CASE WHEN (Field A = 'Value 1')
THEN 'Pass'
END

For this data item, I wanted to look for field A which could have multiple values entered, Value 1, Value 2, Value 3, etc... and only those records that have their field A with an entry of Value 1, would display a "Pass" in the output. However, I realized that without specifically defining an ELSE condition in the Case When statement, it defaults as "ELSE NULL" so for those records with Value 1 in field A, it generates an extra blank row in the output, like this.

Record Number   Field A Yes/No
Record 1       
Record 2
Record 3        Pass
Record 3
Record 4
Record 5        Pass
Record 5

Because for Record 3, and Record 5, they both had multiple data entries into field A and only one entry was Value 1, so the Case When function checks Value 1 and outputs a Pass as a row, but then because there's also Value 2 so it outputs an extra blank row. My question is, how do I modify the logic to avoid generating the extra rows for Record 3 and Record 5?

First post here and thank you for your help in advance!
 

Since you have not documented the nature of the data I can't provide much guidance other from my surmises based on the usual patterns.

I really don't think I have encountered a situation where an expression would return multiple rows due to an expression when there are only one record for an entity.

I think it possible that there are multiple records for entities in your db.  You would need to know this. Knowing the nature of the data and the metadata would determine how to approach the problem, which would require you to step back and not seek some method to tweak your expression but to seek to define the problem more clearly.  One thing you would need to do to accomplish this would be to understand the nature of your data.

I think it possible that, given the likelihood of there being multiple records for any particular entity, you are seeking to have your report return the latest record for an entity, where the value of a column is some condition.

It is possible that the time state of a particular record is captured in a column. 

If so, there is the possibility that the latest record could have a value which is not (as in your case) 'passed'. It is not clear what you want to do in that case.




----

I think your situation deals with two common patterns of behaviour.  The first being a report author trying to model.  This sort of calculation should be better dealt with in the model or, even better, during ETL.  The second being an over-abstraction of the problem as data has semantic meaning which would partially define the bounds of the problem.

Quote from: bus_pass_man on Yesterday at 06:22:31 AMSince you have not documented the nature of the data I can't provide much guidance other from my surmises based on the usual patterns.

I really don't think I have encountered a situation where an expression would return multiple rows due to an expression when there are only one record for an entity.

I think it possible that there are multiple records for entities in your db.  You would need to know this. Knowing the nature of the data and the metadata would determine how to approach the problem, which would require you to step back and not seek some method to tweak your expression but to seek to define the problem more clearly.  One thing you would need to do to accomplish this would be to understand the nature of your data.

I think it possible that, given the likelihood of there being multiple records for any particular entity, you are seeking to have your report return the latest record for an entity, where the value of a column is some condition.

It is possible that the time state of a particular record is captured in a column. 

If so, there is the possibility that the latest record could have a value which is not (as in your case) 'passed'. It is not clear what you want to do in that case.




----

I think your situation deals with two common patterns of behaviour.  The first being a report author trying to model.  This sort of calculation should be better dealt with in the model or, even better, during ETL.  The second being an over-abstraction of the problem as data has semantic meaning which would partially define the bounds of the problem.

Thanks for the response! Sorry I wasn't being very clear about the description. So for the source data, in the DB, field A for every single record (case), could have 0 entry, 1 entry, 2 entries, etc. Take field A as "Physical Test Date", and if a person did a test there would a date entered as value 1, and then another test on a different date as value 2. My function is looking for all the people that did a test on a certain date (value 1) and output their records (rows) as "Pass". But my Case When function, it seems like because person A has other test dates than value 1, so it outputs 1 row as Pass but another row as blank.

Hi,

It appears there is a row for each record, regardless? Your case statement in the data item expression (ie a calculation) is generating a 'Pass' value for those rows that have 'Value 1' in Field A, and a null value for those rows whose Field A is something other than 'Value 1'. It sounds like you only want to see rows with Pass values (ie rows that have 'Value 1' in Field A)? If this is the case, you would generally use a filter in your query to allow the rows you require and to omit the rows you don't need. It is possibly as simple as coding a filter expression [Field A] = 'Value 1'
Of course, it's quite possible I'm missing something, but if so, maybe you could provide more detail and explain exactly what you need to do?

Cheers!

MF.

or...

If you want a row for every [Record Number], maybe aggregate the data item.  MAX() should work to get you:

Record Number  |  Field A Yes/No
Record 1       | 
Record 2       | 
Record 3       |  Pass
Record 4       | 
Record 5       |  Pass

Quote from: MFGF on Yesterday at 02:29:43 PMHi,

It appears there is a row for each record, regardless? Your case statement in the data item expression (ie a calculation) is generating a 'Pass' value for those rows that have 'Value 1' in Field A, and a null value for those rows whose Field A is something other than 'Value 1'. It sounds like you only want to see rows with Pass values (ie rows that have 'Value 1' in Field A)? If this is the case, you would generally use a filter in your query to allow the rows you require and to omit the rows you don't need. It is possibly as simple as coding a filter expression [Field A] = 'Value 1'
Of course, it's quite possible I'm missing something, but if so, maybe you could provide more detail and explain exactly what you need to do?

Cheers!

MF.

Thanks! I do still want to see every row. And only those with a certain date display a pass in the Yes/No column but those other records that do not have a certain date would just display blank. So instead of the current:

Record Number   Field A Yes/No
Record 1       
Record 2
Record 3        Pass
Record 3
Record 4
Record 5        Pass
Record 5

I wanted it to be:

Record Number   Field A Yes/No
Record 1       
Record 2
Record 3        Pass
Record 4
Record 5        Pass

Because in the database, record 3 have multiple entries in field A (test date), like 01-Jan-2025, 02-Jan-2025, 03-Jan-2025, etc, indicating this person has done multiple tests on different dates. My current function is:

CASE WHEN (Field A (test date) = '02-Jan-2025')
THEN 'Pass'
END

And I think the reason why record 3 has two rows in the current output, is because for the 02-Jan-2025 entry in record 3 in DB, the case when function outputs a "Pass". However because record 3 also has 01-Jan-2025 and 03-Jan-2025, this is the "ELSE NULL" default statement part of Case When function that outputs an extra row of blank. So I would want record 3 to only have one row, which is Pass. Basically as long as it has that date 02-Jan-2025 I want the function to stop looking for other entries within the record. Does that help clarify?

Thank you!