So I recently fell upward into an analyst position at work, and while there's a big push towards Power BI, apparently a significant chunk of our data lives on Cognos Analytics. From what I've been able to gather, someone set up some reports pre-covid and people have just been using those reports ever since. The guy who set them up left the company a while ago, and nobody knows how to do anything with this EXCEPT run those reports. I'd like to get a handle on it.

Unfortunately, I am a very small cog in a very large machine so I don't have access to stuff like setting up my own data modules (I am not trained in data science or whatever, I'm a techy guy with google), and the data I DO have feels like it's all over the place and not labeled particularly well.

Do you folks have any links that might be helpful to me as a newbie, trying to wrap my head around how this is supposed to work? Tips and/or tricks for massaging the data into legible reports? I'll take anything at this point.

I have a report view that was working until a specific date when there was error as per the attached.

The report view sends to 3 DLs (distribution lists) and 1 individual. Ever since the error occurred, only 2 DLs and 1 individual get the report. Also, ever since the error occurred, no matter which report I add the DL that is the issue to, it won't send to that specific DL.

Any insights would be appreciated. Our IT team both on Exchange and Cognos side cannot figure it out. I'm thinking there is some sort of failsafe that catches problematic DLs and then blocks them for all future sends. All other DLs and emails are working perfectly fine, it's just the one DL (which worked before Oct 27).

Quote from: MFGF on 10 Nov 2025 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!

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

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.

Quote from: bus_pass_man on 10 Nov 2025 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.

Thanks all!  The previous posts were very helpful to create this query for our Content Store on SQL Server to search the report XML (spec) for the occurrence of "sqlText" which indicates a manual SQL object in the report and return the Report Name and Path.  Set Content = 'report' (Dashboards and Analysis do not have Manual SQL) and set the Path to a target folder or all of Team Content by adjusting path like 'Team Content\Consumer Products\%' in the query below to customize for your search criteria.

/****** Script for Reports with Manual SQL Queries command from SSMS  ******/
With CognosContent AS (

Select a.CMID, a.PCMID, b.NAME, b.Name as Parent, c.name as Content

,Cast(b.NAme  as varchar(max)) as path

, 0 as level

from cmObjects a

inner join CMOBJNAMES b on a.CMID = b.CMID

inner join CMCLASSES c on a.CLASSID = c.CLASSID



Where b.ISDEFAULT = 1

and a.PCMID =0

and a.CMID = 2



UNION ALL

Select a.CMID, a.PCMID, b.NAME as parent, d.NAME, c.NAME

, d.path + '\' +  CAST (b.Name as varchar(Max))  as path

, d.level + 1 as level

from cmObjects a

inner join CMOBJNAMES b on a.CMID = b.CMID

inner join CMCLASSES c on a.CLASSID = c.CLASSID

inner join CognosContent d on a.PCMID = d.CMID

Where b.ISDEFAULT = 1

)

Select CMID, NAME as ReportName, path, level, content from CognosContent

Where

Content = 'report' --or Content = 'dashboard' or content = 'analysis'
and path like 'Team Content\Consumer Products\%'
AND CMID IN ( SELECT DISTINCT CMID FROM ( select S.CMID, try_convert(xml,s.SPEC).value('count(//*[local-name()="sqlText"])', 'int') CustomSQL

from CMOBJPROPS7 s where s.spec is not null ) A WHERE A.CustomSQL>0 )
order by path

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.

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!
 

This topic has been merged into Cognos Installation on Azure VM.