Removal of joint individuals in Cognos report

[stevedave]

Hello all and Good Morning/Afternoon,

I am working in Cognos and have a question for all you bright folk.

I am working with data that has primary account holders as well as joint account holders. My intention is to gather only accounts where the primary individual does not have a joint account holder. I have recently been in a few Cognos trainings and am getting better within my function at my place of employment but for the last day my brain is just not connecting the dots...

Can anyone assist me in this? It has to be something simple that I am just blanking on. I had an advanced course for Cognos in the last week and it feels like I am over complicating EVERYTHING. The instructor told me to just reach out for assistance and then reflect and study notes to stop removing easy answers so here I am.

Any assistance would be great.

An example of my data are as follows

Account number which is [data].[Account].[Account #]
Relationship which is [data].[Account relationship].[Relationship]

The relationship is simple and has 'P' for primary and 'J' for joint. Is it as simple as a filter to rule out accounts with a joint? I have a filter now to include only primary relationships. That helps but does not only show accounts with no joint just shows all primaries on the account regardless of others if that makes sense.

Thanks again everyone.

[MFGF]

Hello all and Good Morning/Afternoon,

I am working in Cognos and have a question for all you bright folk.

I am working with data that has primary account holders as well as joint account holders. My intention is to gather only accounts where the primary individual does not have a joint account holder. I have recently been in a few Cognos trainings and am getting better within my function at my place of employment but for the last day my brain is just not connecting the dots...

Can anyone assist me in this? It has to be something simple that I am just blanking on. I had an advanced course for Cognos in the last week and it feels like I am over complicating EVERYTHING. The instructor told me to just reach out for assistance and then reflect and study notes to stop removing easy answers so here I am.

Any assistance would be great.

An example of my data are as follows

Account number which is [data].[Account].[Account #]
Relationship which is [data].[Account relationship].[Relationship]

The relationship is simple and has 'P' for primary and 'J' for joint. Is it as simple as a filter to rule out accounts with a joint? I have a filter now to include only primary relationships. That helps but does not only show accounts with no joint just shows all primaries on the account regardless of others if that makes sense.

Thanks again everyone.

Hi,

One thing we need to understand is whether there is just one row for each Account (and that row has either a P or a J in the Relationship column) of if there can be multiple rows for each Account - eg one row for the Account with P in the Relationship column and a second (or multiple other) row(s) for the Account with J in the Relationship column? I'm assuming it's the latter?

Moving forward on that basis, the challenge is then to identify which Accounts have only a P relationship and no J relationship(s)?

To do this, we need to do some aggregation to figure out the minimum value of Relationship for each Account. For Accounts that have only a Primary, this will obviously come back as P, since P will be the minimum value for all rows with the same Account number. For Accounts that have both a Primary and a Joint, this will come back as J, since J comes before P in the alphabet so is the minimum value of Relationship for each Account. You can achieve this with a minimum() function, and with the scope set to be Account (you use a 'for' operator for this) - so in your case minimum( [Relationship] for [Account] )
So now you have account numbers with either P or J, it's an easy job to filter for the P accounts. You can do this all in one expression in your detail filter:

minimum( [Relationship] for [Account] ) = 'P'

Cheers!

MF.

PS I spent ten years as a Cognos instructor. This was roughly a hundred billion years ago in the days before Cognos was part of IBM, but I loved that job! :-)

[dougp]

It looks like [Account Relationship] should really be named [Account Access] and [Relationship] should be named [Access Type].  So, something like this:

Code Select Expand


┌─────────────┐    ┌─────────┐
│Account      │    │Person   │
├─────────────┤    ├─────────┤
│AccountNumber│    │PersonId │
└─────────────┘    │LastName │
        *          │FirstName│
        │          └─────────┘
        │               *
        └───────┬───────┘
                1
         ┌─────────────┐
         │AccountAccess│
         ├─────────────┤
         │AccountId    │
         │PersonId     │
         │AccessType   │
         └─────────────┘


...and what you want is to identify all accounts with no joint access, but what you are getting is all of the primary account holders -- even on accounts that have joint access.

I think what you need is:

Code Select Expand


WITH A AS (
  SELECT AccountNumber
  , MIN(AccessType) AS AccessType
  FROM Account a
    INNER JOIN AccountAccess aa ON aa.AccountId = a.AccountId
)

SELECT AccountNumber
FROM A
WHERE AccessType = 'P'


I think the way to do this in Cognos (and using the names you're dealing with) is to change the Detail aggregation for [Relationship] to Minimum then create a filter [Relationship] = 'P' and set the Application property of the filter to After Auto Aggregation.

[stevedave]

Thank you all and sorry for my slow reply. Things picked up and vacation came faster than I would have hoped for.

I have not got the report functioning. Thank you for the input as Cognos is still my weakest skillset at work.

[bus_pass_man]

Describing in detail the nature of the errors or variations from expected results can help in troubleshooting problems.  Sometimes the error message actually has text which identifies the problem in a clear manner. More often, less so but at least knowing what the message is can help make it clearer what the problem is or at least narrow down the problem space.

The situation suggests that the nature of the problem is suitable to a bridge table.

What is the structure of the relevant tables and their relationships in the data base?   Has a bridge table been already modelled in the db?  If not, why not?  If it really is impossible to model it in the db, can you model it in your model?  Both FM and data modules have properties which, when switched on, identifies a bridge table as such and the Cognos query engine can then generate an appropriate query, without you going through a lot of the rigmarole.