Cognos 8 - idenfity descrepancies between two tables

[CrystalLion]

The Package contains View_A and View_B. View_A contains Table_A and View_B contains Table_B. the tables are properly linked.

The tables have common data objects like:

View_A,Table_A View_B, Table_B
Loan_ID Loan_Number
Principal_Amt Prin_Amt

I want to compare Principal_Amt to Prin_Amt for each Loan_ID and total and count the Principal_Amt if the amounts don't match.

The summary report in Report Studio should show:

1025 loans - No Variance Total[Principal_Amt] = $500,000
25 loans - Variance Total[Principal_Amt] = $ 67,000
1050 lonas - TOTAL Total[Principal_Amt] = $567,000

I cannot get the count of Loans to aggregate properly unless I include the Loan_ID.

Any help is GREATLY appreciated.

 

[blom0344]

How do you define the count of loans? Are Loan_ID and Loan_Number matching keys? I feel this is very much solvable with a little more detail..

Ties Blom

 

[CrystalLion]

yes - they are

 

[blom0344]

I would write separate queries (singletons) for the individual loan counts. These queries would be based on auto-aggregation disabled and using filters like :

Principal_Amt <> Prin_Amt

for counting the variance loans

and

Principal_Amt = Prin_Amt

for counting the non-variance loans

The actual measure would then be:

count(distinct [loan_id])



Ties Blom