Framework Modeling Issues

[paquino11]

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.

[blom0344]

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.

[jabsey]

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