Unable to summarize results without inflated values or duplicated detail rows

Question

I'm attempting to summarize data in a Report Header but keep running into incorrect summation or duplicated Detail rows.

There are two SQL views that I'm pulling data from, and can be simplified as the following:

View_Invoice_Header:

Invoice ID        Department        Total Amount
================================================
101               A                 30
102               B                 20
103               C                 5
104               C                 5

View_Invoice_Detail:

Invoice ID        Line        Department        Total Amount
============================================================
101               1           A                 30
101               2           A                 30
102               1           B                 20
103               1           C                 5
104               1           C                 5
104               2           C                 5
104               3           C                 5

In the report header, I want to display the totals for each Department like so:

Department        Total Amount
==============================
A                 30
B                 20
C                 10

I thought this would be easier to accomplish than it turns out.

1. If the tables are linked together (on Invoice ID), then Summarizing the totals from View_Invoice_Header by Department results in largely inflated values due to the Total Amount for each being multiplied by the number of linked Detail rows.
2. If the tables are not linked, Detail rows are duplicated by every Header row.

Are there ways around those problems or should I be attempting a different approach? The tables do not need to be linked, the Header view is only needed for summarizing the results in the Report Header.

Answer 1

Create a new formula field like so:

If {Line} = 1 Then {Total Amount} Else 0

Summing this one up will give the required result. (Assumption here is that every invoice has exactly one line number 1.)