Rather new to Cognos, and have found the Q&A on this site helpful- trying to referencing a different line's value in a new column
Trying to determine how to create a query that reference the value on a separate line
For instance- if a employee signs up for spouse life insurance, I need to see the employee amount next to the spouse amount. If Benefit=Spouse Life, then give Value for Benefit= Vol. Life, else null type of thing:
Employee Benefit Value Query
1234 Vol. Life $10 (null)
1234 Spouse Life $7 $10
you can create 2 data items to achieve your scenario.
assuming every employee has 2 benefits only i.e Vol. life and Spouse life
1. dataitem1 if Benefit=Spouse Life then 0 else value.
2. dataitem2 if Benefit=Spouse Life then maximum(dataitem1 for employee) else null
this solution will slower the performance but it should work.