I am working in Cognos Analytics 11.1.7.
I have a data module with three tables. Table 1 and 2 contain all the transactions we do, where table 1 contains the remitter's part of the transaction and table 2 contains the beneficiary's part of the transaction. I.e. one transaction is divided into two tables. Table 3 holds account numbers.
I want to create a report that shows the remitter's customer ID and account number. However, in some cases, the the remitter's account is missing. These customer ID's have unique customer ID's (Y instead of X). In those cases, I want the beneficiary's customer ID and account number. Consider the following three tables
Table 1: REMITTER
| CUSTOMER_ID | ORDER_NR |
|---|---|
| 1X | 123 |
| 2Y | 456 |
| 1X | 789 |
Table 2: BENEFICIARY
| CUSTOMER_ID | ORDER_NR |
|---|---|
| 4X | 123 |
| 6X | 456 |
| 6X | 789 |
Table 3: ACCOUNTS
| CUSTOMER_ID | ACCOUNT_NR |
|---|---|
| 1X | 1111 |
| 2Y | |
| 3X | 3333 |
| 4X | 4444 |
| 5X | 5555 |
| 6X | 6666 |
What I want is basically the following report:
REPORT OF ALL TRANSACTIONS TODAY
| CUSTOMER_ID | ACCOUNT_NR | ORDER_NR |
|---|---|---|
| 1 | 1111 | 123 |
| 6 | 6666 | 456 |
| 1 | 1111 | 789 |
I have solved the CUSTOMER_ID column with a switch case:
CASE
WHEN REMITTER.CUSTOMER_ID CONTAINS 'Y'
THEN BENEFICIARY.CUSTOMER_ID
ELSE REMITTER.CUSTOMER_ID
END
Now here's the problem, I can't create a join (relationship) between the column created above and the ACCOUNTS table since my own column lies directly "under" the data module (on the same level as the tables in the index list to the left). However, if I create a column "under" REMITTER table, I can't use the case calculation from above. Cognos gives med the following error:
The expression is not valid.
XQE-MSR-0008 In module "STACKOVERFLOW", the following query subjects are not joined: "REMITTER", "BENEFICIARY".
I have tried to circumvent the error by creating all kinds of joins between REMITTER and BENEFICIARY on ORDER_NR but Cognos keeps giving me this error.
I have also tried to make a "triangle" of joins, where REMITTER and BENEFICIARY are joined on ORDER_NR, REMITTER and ACCOUNTS are joined on CUSTOMER_ID and BENEFICIARY and ACCOUNTS are joined on CUSTOMER_ID. This doesn't work. However, when I delete either the REMITTER/ACCOUNT or BENEFICIARY/ACCOUNTS join, it works with the table I keep joined.
I am slowly losing my sanity here. Thanks!
What is the nature of the relationships between these entities?
That is a question which you should ask for everything in your model.
The pattern of that relationship drives the relationship between the objects in the model, which in turn drives what decisions you need to make in your modelling.
For example, is this a Bridge table situation? If so, you need to be aware of it so you can model appropriately.
In the end it falls back on Kimball:
I am assuming that the cardinality is beneficiary to remitter to account or remitter to beneficiary to account.
Put beneficiary and remitter into a view in the module, create a relationship between it and account, and delete the relationship between the middle table and account (so that the SQL will use the relationship which you created ).
I think putting the calculation into the table which is in the middle would also do the trick.
I can not say that I can map between your described 'triangle' of joined tables and a business purpose so I could not use that information to understand the entity relationship. Such a pattern of relationship is specifically identified as one to be identified and, as part of the Cognos proven practices, corrected. Because I can not identify if there truly is a business purpose to have such a triangle or not, I can not, and will not, describe the appropriate modelling actions as they are dependent on the business purpose of the relationships between the entities, which takes us back to St. Ralph.