I apologize in advance if I dont use the correct terminology or I'm just lacking some basic understanding (I'm new here).
Lets say I have a query subject "customer" that is defined as:
select id, name
from [datasource1].customer
union all
select id, name
from [datasource2].customer
with the data:
id | name
---------------------------
1 | Peter Venkman
2 | Raymond Stantz
2 | Ray Stantz
3 | Dana Barrett
I also have a query subject "purchase_union":
order_id | customer_id | amount
------------------------------------------
1 1 $125
2 2 $50
3 2 $25
4 3 $39
And finally, I have two query items:
customer_name -> [customer_union].name
total_amount -> [purchase_union].amount
I'm creating a list report by dragging customer_name and total_amount onto my report.
I am hoping to get only 3 rows back since there are only 3 customers (one customer has two records with different names).
However, the default behavior appears to be a group by name, so I get separate totals for customer ID=2.
In SQL, I would group by customer.id, and then just pick any name from one of those columns (e.g. the first one).
How do I do this in Cognos universe?
Thank you for any help,
Justin
Firstly, universe is a Business Objects metadata layer, in Cognos this is called a framework. Did you try to use an aggregate on the name (min or max) to ensure 1 row returned for this Stantz fellow :)?
A well organized datawarehouse should yield 1 and only one name for each Id by the way..