Search code examples
cognoscognos-bi

Cognos BI - Join Results of Multiple Queries into Single Table

Cognos BI question here - I have two data tables – one contains the Last Name and SS # of customers, and another table has “Extended Info” about those customers. Element ID is the data element being stored, Ext Cis Value has the data value, and SS Number ties it back to a customer.

I want to build a single list which lists all customers, as well as the corresponding values for each of the three data elements in the ExtendedInfo table. In this case it’s #13 (Email Address), #15 (Prospect Type) and #16 (Prospect Source)

Here is the data I have today:

ProspectData table:

| Last Name             | SS #      |
|-----------------------|-----------|
| ABC Construction, LLC | S10000104 |
| XYZ Construction, LLC | S10000106 |

ExtendedInfo table:

| Element Id | Ext Cis Value | SS Number |
|------------|---------------|-----------|
| 13         | HAS@EMAIL.COM | S10000104 |
| 13         | NO@EMAIL.COM  | S10000106 |
| 15         | HOT PROSPECT  | S10000104 |
| 15         | WARM PROSPECT | S10000106 |
| 16         | External      | S10000106 |
| 16         | Internal      | S10000104 |

I've been able to JOIN these two tables together to create a result like this, but only by applying a filter to ExtendedInfo to return a single field. Example as shown:

| SS #      | Last Name             | Email Address |
|-----------|-----------------------|---------------|
| S10000104 | ABC Construction, LLC | HAS@EMAIL.COM |
| S10000106 | XYZ Construction, LLC | NO@EMAIL.COM  |

I am trying to set up a single query which will contain five columns: SS Number, Last Name, Email Address (#13 on Element ID), Prospect Type (#15) and Prospect Source (#16). I envision it looking like this:

| SS #      | Last Name             | Email Address | Prospect Type | Prospect Source |
|-----------|-----------------------|---------------|---------------|-----------------|
| S10000104 | ABC Construction, LLC | HAS@EMAIL.COM | HOT PROSPECT  | Internal        |
| S10000106 | XYZ Construction, LLC | NO@EMAIL.COM  | WARM PROSPECT | External        |

So far, the closest I’ve come to this is adding a new query on the ExtendedInfo table which has a filter applied for Element ID, then using JOIN to join the result of that query and the ProspectData table. However, I don’t know how (or if it’s practical) to create 3 individual queries on ExtendedInfo (Email, Prospect Type, Prospect Source) and join them all to ProspectData. This seems like a simple task, but I’m not sure how to do it. Any suggestions? Thanks in advance for your help.

Solution

  • You don't have to join the tables three times. In fact, you only have to join once. You can construct your custom columns at the model/report layer.

    • Join ProspectData and ExtendedInfo on SS Number with a standard inner join

    The result will look like this:

    | Element Id | Ext Cis Value | SS Number | SS Number | Last Name             |
|------------|---------------|-----------|-----------|-----------------------|
| 13         | HAS@EMAIL.COM | S10000104 | S10000104 | ABC Construction, LLC |
| 13         | NO@EMAIL.COM  | S10000106 | S10000106 | XYZ Construction, LLC |
| 15         | HOT PROSPECT  | S10000104 | S10000104 | ABC Construction, LLC |
| 15         | WARM PROSPECT | S10000106 | S10000106 | XYZ Construction, LLC |
| 16         | External      | S10000106 | S10000106 | XYZ Construction, LLC |
| 16         | Internal      | S10000104 | S10000104 | ABC Construction, LLC |
    • Now, at the model layer (if doing this in Framework manager) or in the resultant result query (if doing this in a report) add three new data items, Email Address, Prospect Type, Prospect Source with the following expressions:

    Email Address

    CASE 
WHEN position('@',[Ext Cis Value]) > 0 THEN [Ext Cis Value] 
ELSE null 
END

    Prospect Type

    CASE 
WHEN position('PROSPECT',[Ext Cis Value]) > 0 THEN [Ext Cis Value] 
ELSE null 
END

    Prospect Source

    CASE 
WHEN position('ternal',[Ext Cis Value]) > 0 THEN [Ext Cis Value] 
ELSE null 
END

    Set the Aggregate Function attribute for the three new data items to 'Maximum'. This should cause your result to roll up to a single row, with values in each of the three new data items.