I need to output data from one parent record and two different child records on one row.
Parent table - primary key X
Child table - primary key (X, Effective Date) - there will be one to "n" of these per parent
- other column is Rate
Input Start Date
Input End Date
I need to output
- X
- largest effective date that is less than StartDate
(there might not be one)
- matching Rate
- largest effective date that is less than EndDate
(there might not be one)
(might be the same record as found above, might not)
- matching Rate
- (math for amount the Rate changed - this one is easy).
I can find one child or the other, but I'm not sure how to include two different children on one row.
I feel like I've done this sort of thing before, but I haven't worked in Cognos for a while and I'm sure I'm just having a brain cramp. Any pointers are welcome.
Thanks for any help
Paul
looks like you need to create a set
c1 = [child1]
c2 = [child2]
[2childs] = set([child1];[child2])
your crosstab will be:
[column1]
[whatever1]
[2childs]
[other rows]