I have four tables linked DW_Reference_ID->Practitioners-> Vouchers->QB_OH_Table. The report I have is listed and grouped by Practitioners.Last_Name. The part I am having trouble with is the Vouchers to QB_OH_Table linking.
Basically the QB_OH_Table contains a single record for each month and year and an amount associated with that month and year. I need to be able to create a running total of the QB_OH_Table.OH_Amount of say months 3, 4, and 5 for 2003. However, because of the way these tables are linked it is returning a QB_OH_Table record for each voucher record pulled in from the database creating duplicate records pulled in from the QB_OH_Table. Here’s an example:
Voucher_ID Service_Month Service_Year QB_OH_Table.Amount
1 3 2003 $10,000
2 3 2003 $10,000
3 4 2003 $15.000
4 3 2003 $10,000
5 5 2003 $20,000
6 4 2003 $15,000
Ok so I would like to get the total overhead amount for months 3, 4, 5 which should be 10000 + 15000 + 20000 = $45,000. However, doing a running total on this field with the formula ({QB_OH_Table.Month} = 3 or ({QB_OH_Table.Month} = 4 or ({QB_OH_Table.Month} = 5) will give me $80,000 because of the record duplication. I have tried using a maximum running total with this same formula however that of course will only return $20,000 which is not what I need either. I have also tried various things with variables but with no success. Is there any way that I can resolve this using variables? I thought about using a variable to get the maximum Amount where month = ?, but I am not sure if this can be done. Thanks in advance!
Basically the QB_OH_Table contains a single record for each month and year and an amount associated with that month and year. I need to be able to create a running total of the QB_OH_Table.OH_Amount of say months 3, 4, and 5 for 2003. However, because of the way these tables are linked it is returning a QB_OH_Table record for each voucher record pulled in from the database creating duplicate records pulled in from the QB_OH_Table. Here’s an example:
Voucher_ID Service_Month Service_Year QB_OH_Table.Amount
1 3 2003 $10,000
2 3 2003 $10,000
3 4 2003 $15.000
4 3 2003 $10,000
5 5 2003 $20,000
6 4 2003 $15,000
Ok so I would like to get the total overhead amount for months 3, 4, 5 which should be 10000 + 15000 + 20000 = $45,000. However, doing a running total on this field with the formula ({QB_OH_Table.Month} = 3 or ({QB_OH_Table.Month} = 4 or ({QB_OH_Table.Month} = 5) will give me $80,000 because of the record duplication. I have tried using a maximum running total with this same formula however that of course will only return $20,000 which is not what I need either. I have also tried various things with variables but with no success. Is there any way that I can resolve this using variables? I thought about using a variable to get the maximum Amount where month = ?, but I am not sure if this can be done. Thanks in advance!