Tabular Report - Grouping before writing to report

[cg001]

I have two tables - accounts and invoices.  I want to create a tabular report where each record contains the sum 
of AccountBalance by AccountType and uses calculated fields to return data from the invoice table.  How do I create a tabular report that is first grouped by AccountType so that I can eliminate multiple rows and provide a higher level report?  Can this be accomplished with a view of Accounts?

ACCOUNTS
Account#    AccountType    AccountBalance
1                  1                          50
2                  1                          50
3                  2                          75

INVOICES
Invoice#    Account#    InvoiceTotal
1                1                    10
2                2                    20
3                3                    10

Desired Report
------------
AccountType    AccountBalance    Invoice        RemainingBalance
1                        100                           30                70
2                        75                             10                65

Instead of
---------
AccountType    AccountBalance    Invoice        RemainingBalance
1                         50                            30                20
1                         50                            30                20
2                         75                            10                65

[Mathilda]

On 8/11/2017 at 8:15 PM, cg001 said:

I have two tables - accounts and invoices.  I want to create a tabular report where each record contains the sum 
of AccountBalance by AccountType and uses calculated fields to return data from the invoice table.  How do I create a tabular report that is first grouped by AccountType so that I can eliminate multiple rows and provide a higher level report?  Can this be accomplished with a view of Accounts?

ACCOUNTS
Account#    AccountType    AccountBalance
1                  1                          50
2                  1                          50
3                  2                          75

INVOICES
Invoice#    Account#    InvoiceTotal
1                1                    10
2                2                    20
3                3                    10

Desired Report
------------
AccountType    AccountBalance    Invoice        RemainingBalance
1                        100                           30                70
2                        75                             10                65

Instead of
---------
AccountType    AccountBalance    Invoice        RemainingBalance
1                         50                            30                20
1                         50                            30                20
2                         75                            10                65

I have a few ideas. The first one -  you may try using pivot table (if it's available for your plan)

another Idea: create a report datapage using table where account type is a unique value, then get other values (account balance, invoice) using sql queries in calculated fields.

Hope that helps