We are having issues with Framework and modeling our data.
Here are some details about the data
1) Header information (Total Billed)
2) Transaction information (Transaction Amounts)
3) Line Item Transactions (Line Item Debits/Credits)
We need to somehow model this data so that it can be seen on one report.
Our issue is that when we bring in all 3, 1 and 2 are summed up due to 3.
Example
Total Billed = 75
# of Transactions = 3
# of Line Itmes = 3
So our report looks like the following
Invoice # Total Billed Transaction Transaction Amt Line Item Line Item Payment Amt
12345 75 1 110 1 12
12345 75 1 110 1 58
12345 75 2 52 2 16
12345 75 3 52 3 16
12345 75 4 88 4 35
12345 75 5 88 5 35
12345 75 6 527 6 149
Summary 528 1027 321
For the summary line we should see 75,927 and 321 respectively
Any help or suggestions would be greatly appreciated.
1. Use aggregate max for the Header (total billed) instead of total
2. Store number of lines for each transaction as a seperate measure and divide the transaction amount by this figure. Rolling up these wil then compensate for the overcounting.
The second part means creating a (slightly) more complex query subject which could be build using a database view or a Cognos SQL object in FM.
See my recent reply in the subject "Join 2 Fact tables where no conformed dimension is present" and make sure your aggreagate calculations have scope specified correctly (for clause; you may also need to include the "distinct" keyword into the expression)
-jabsey