>I have a batch accounting process that moves a payment (check)
> from the original invoices where it was applied to a new set of
> invoices.
>
> I am trying to build a report that will list, for a given batch, the
> original and new invoices.
>
> Approach 1:
> I tried using a single query and 2 report tables with a single group
> in each. The table grouping correctly lists each invoice once but because
> the query is running against 2 sets of invoices it is returning multiple
> rows per invoice so the grouping total is overstated.
> Is there a way to show in the group footer, just the total of the
> displayed
> items in the report table and not of all values returned in the query?
>
> Approach 2:
> I also tried using 2 Datasets for the original/new invoices. This
> allows me to have each query return the correct number of invoices.
> But I cannot find a way to reference both data sets on the same page
> either in separate report tables or within the same table.
>
> I know I can probably do this with a subreport but I would like to
> avoid deploying a second RDL.
>
> Can this be done?
>
> Thanks
>
>

> - I understand from the query results that you are self joining the trans
> table by BatchID and filtering by IsSource. Are amounts always equal
> between Source and Target transactions, so you can include this factor on
> the join expression too?

> - Have you tried using the table name ONLY in the scope of the Sum?

> - When you say the table totals, do you mean that each of both tables show
> 60 instead of 30?
Yes
>If positive, how do you "filter" at each table?

> - Have you tried using a Rectangle to force the page break instead of a
> List?

> Hi Jordi
> Let me try to make this clearer-
>
> I have 3 tables that store the results of a transaction to
> move payments from one set of invoices to another. Assume
> the tables Batch, Trans, Invoice and a single transaction
> with data as follows:
>
> Batch- ID TransCount TotalAmt
> 1 4 30.00
>
> Trans- ID BatchId InvoiceNbr CashAmt IsSource
> 1 1 100 10.00 1
> 2 1 101 20.00 1
> 3 1 201 10.00 0
> 4 1 215 20.00 0
>
> ****
> The report query result looks like this:
> From InvNbr From Amt ToInvNbr ToAmt
> 100 10.00 201 10.00
> 100 10.00 215 20.00
> 101 20.00 201 10.00
> 101 20.00 215 20.00
>
> I have tried but I don't think there is a way to
> eliminate the redundancy from the query (?)
> ****
> The report would look like example below, with each batch on
> a separate page. You would see the header area then a table
> showing the 'from' invoices and one showing the 'To' invoices
> -------------------------------------------------------------------------
> Batch Nbr: 1
> Trans count: 4
>
> Moved from Invoice-
> InvNbr Amt
> 100 10.00
> 101 20.00
> Total: 30.00
>
> Moved to Invoice-
> InvNbr Amt
> 200 10.00
> 215 20.00
> Total: 30.00
> ---------------------------------------------------------------------------
>
> I can make the report tables correctly show a single
> instance of the from/to invoices, but the problem is
> the table totals show 60.00 instead of 30.00
>
> 1) I tried adding this to the footer of table1:
> =sum(Fields!FromAmt.Value,"table1_group1")
> but get this error:
> "The value expression for the textbox textbox21' has a scope
> parameter that is not valid for an aggregate function. The scope
> parameter must be set to a string constant that is equal to either
> the name of a containing group, the name of a containing data region,
> or the name of a data set."
>
> 2) I also tried using 2 datasets, one for the 'FromInvoices'
> and the other for the 'ToInvoices'
> -I tried just using the report tables and adding each to the related
> set; but I could not control the page breaking so that there would be
> one page per batch
> -I tried using the 'List' object but could only reference 1 data
> set. Using the List, I also tried referencing the specific data set in
> the group:
> =sum(Fields!FromAmt.Value,"dsToInvoice")
> but got out of scope errors
>
> I know I am missing something obvious. Many thanks for your help!
>