Multiple Data Regions on Same Page

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?
ThanksHi Mike,
I'm not sure if I've fully understood your question, but just in the case.
All aggregate functions have an scope associated with them. If none is
specified, the default is used.
So, if you drop the Amount field, i.e., in the TABLE footer, RS is assuming
you want to sum all table items.
Thus
Sum(Fields!Amount) is equivalent to Sum(Fields!Amount, "table1")
If you do the same in the GROUP footer
Sum(Fields!Amount) is equivalent to Sum(Fields!Amount, "table1_group1")
In consequence, what I've understood you're trying to do is the default
behavior.
I do not understand your statement about "reference both datasets in the
same page" because this is quite straight, too.
Could you elaborate this a bit more, please?
BTW, are you using Filters on the table data regions?
Best regards,
Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning
"Mike Harbinger" <MikeH@.Cybervillage.net> escribió en el mensaje
news:uzYTwof$FHA.3864@.TK2MSFTNGP12.phx.gbl...
>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
>|||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!|||Hi Mike,
Your message is long so let me ask a question before I get hands on:
- 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? If positive, how do you "filter" at each table?
- Have you tried using a Rectangle to force the page break instead of a
List?
Best regards,
Jordi Rambla
SQL Server MVP (Reporting Services)
SolidQualityLearning
"Mike Harbinger" <MikeH@.Cybervillage.net> escribió en el mensaje
news:%23ulxrGr$FHA.504@.TK2MSFTNGP12.phx.gbl...
> 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!
>|||Hi Jordi
> - 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?
While the total 'From invocies' will always equal the totlal 'To invoices' ,
the
individual amounts may vary because there could be one 'From' invoice
and n 'To' invocies.
> - Have you tried using the table name ONLY in the scope of the Sum?
Like this? =Sum(Fields!FromAmt.Value,"tbFromInv")
Yes and it stills returns the DataSet total
> - 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?
I am not filtering and was not sure if/how I could do that. How could I
filter
in the 'From' group to omit the 'To' items without omitting the 'From'
items?
> - Have you tried using a Rectangle to force the page break instead of a
> List?
I did try this, but it lists all the batches at once in each of the tables,
then
does a page-break; insteadof listing one batch in both tables, page-break,
next
batch etc
Thanks again
Chris