Hi all,
I have the following 2 tables Table A and Table B. Table B is a table that updates with user input from front end, in this case user changed the Cflg to Y and the Hflg to null for the same ID, it updates as a new row in the table with new SID.
Table A Table B
ID SID ID Hflg Cflg
1 01 1 Y null
2 02 1 null Y
At report level i need to join these 2 tables for report output
After join on ID the result would be
ID Hflg Cflg
1 Y null
1 null Y
Users however prefer seeing data in a single row like this
ID Hflg Cflg
1 Y Y
Can anyone please point me in the right direction. How can this be done in Cognos 10?
Very simple. Use an aggregate (min / max) against the Hflg / Cflg dataitems so that data is rolled up against the Id
thank you blom0344, your solution worked. :)
While i was waiting for a reply i tried out another more complex logic. Please share your thoughts on this.
Lets call the result set of the join between Table A and Table B as Query A.
I took a copy of Query A and named it Query B.
Now the main report list report pointed to Query A and the data items Hflg,Cflg i put them inside repeaters. The repeater points to Query B and master detail join on ID. the Hflg,Cflg basically concatnate over each other and give the desired output.
I don't know how safe this soln is so am definitely going with blom0344s version. Thanks again :)
master-detail designs are suspect to performance issues. Everything that can be handled in the query will be superior in terms of performance!!