Multiple Databases on SQL

I'm a SQL newbie, so please be kind. I have just recently installed SQL as a
part of a new accounting/project management/CRM app that is Very Important to
the company. It's performance is vital. I want to install an email archiving
solution which uses SQL as the backend, but I'm wondering what sort of
performance hit SQL takes when you start loading databases on it.
It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1 Xeon
3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
processors wouldn't improve performance. Is that true? How much load can SQL
stand before showing performance degradation? What should I look for on the
server? Processor cycles, disk reads? IS this an adequate machine for running
the two apps I describe? We are a 125 person company with our own Exchange
server.
Thanks for any info that's forthcoming.
> even a single query can be paralized over several processors.
Paralized (paralyzed) means the opposite of what you're trying to say, I
think.
;-)
How about parallelized?
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com...
> No, that is not true. SQL Server has many threads, and even a single query
> can be paralized over several processors.
> It is impossible to say how much machine you need for "two applications",
> since each application is different. You can look at resource usage for
> your current app (memory, disk, network, processor) and try to determine
> whether you have resources left for the other app. You would of course
> need to know about resource usage for that other app in order to know
> whether it will fit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
> news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
>
|||> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
> Xeon
> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> processors wouldn't improve performance. Is
To add on the other responses, SQL Server can also use multiple processors
to process multiple queries concurrently. This is especially important in
an environment with many concurrent users and/or long- running queries.
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
> I'm a SQL newbie, so please be kind. I have just recently installed SQL as
> a
> part of a new accounting/project management/CRM app that is Very Important
> to
> the company. It's performance is vital. I want to install an email
> archiving
> solution which uses SQL as the backend, but I'm wondering what sort of
> performance hit SQL takes when you start loading databases on it.
> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
> Xeon
> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> processors wouldn't improve performance. Is that true? How much load can
> SQL
> stand before showing performance degradation? What should I look for on
> the
> server? Processor cycles, disk reads? IS this an adequate machine for
> running
> the two apps I describe? We are a 125 person company with our own Exchange
> server.
> Thanks for any info that's forthcoming.
|||I even wrote a blog post about getting this word wrong, whether is
misspelling or misspeaking...
http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/29/geek-city-total-paralysis.aspx
:-)
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6702E3AB-59D3-46A0-9A31-A0C509659B66@.microsoft.com...
> Yep, that was the one I was thinking of. Thanks.
> This is one of those I never seem to get the right spelling for. My spell
> check found this type, but my finger pressed "Ignore" and it got sent
> before it reached my brain-cells. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u0hQEVHcIHA.5712@.TK2MSFTNGP04.phx.gbl...
>
|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com:

> No, that is not true. SQL Server has many threads, and even a single
> query can be paralized over several processors.
I agree with Tibor. My servers are often paralized.
Thanks for all your input over the years Tibor, but I couldn't resist.
|||Thanks to all of you who responded. Obviously, I got some bad info. Thanks
for clearing that up.
"Dan Guzman" wrote:

> To add on the other responses, SQL Server can also use multiple processors
> to process multiple queries concurrently. This is especially important in
> an environment with many concurrent users and/or long- running queries.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
> news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
>

Multiple Databases on SQL

I'm a SQL newbie, so please be kind. I have just recently installed SQL as a
part of a new accounting/project management/CRM app that is Very Important to
the company. It's performance is vital. I want to install an email archiving
solution which uses SQL as the backend, but I'm wondering what sort of
performance hit SQL takes when you start loading databases on it.
It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1 Xeon
3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
processors wouldn't improve performance. Is that true? How much load can SQL
stand before showing performance degradation? What should I look for on the
server? Processor cycles, disk reads? IS this an adequate machine for running
the two apps I describe? We are a 125 person company with our own Exchange
server.
Thanks for any info that's forthcoming.> I was told that SQL is not multi threaded, so adding
> processors wouldn't improve performance. Is that true?
No, that is not true. SQL Server has many threads, and even a single query can be paralized over
several processors.
It is impossible to say how much machine you need for "two applications", since each application is
different. You can look at resource usage for your current app (memory, disk, network, processor)
and try to determine whether you have resources left for the other app. You would of course need to
know about resource usage for that other app in order to know whether it will fit.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
> I'm a SQL newbie, so please be kind. I have just recently installed SQL as a
> part of a new accounting/project management/CRM app that is Very Important to
> the company. It's performance is vital. I want to install an email archiving
> solution which uses SQL as the backend, but I'm wondering what sort of
> performance hit SQL takes when you start loading databases on it.
> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1 Xeon
> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> processors wouldn't improve performance. Is that true? How much load can SQL
> stand before showing performance degradation? What should I look for on the
> server? Processor cycles, disk reads? IS this an adequate machine for running
> the two apps I describe? We are a 125 person company with our own Exchange
> server.
> Thanks for any info that's forthcoming.|||> even a single query can be paralized over several processors.
Paralized (paralyzed) means the opposite of what you're trying to say, I
think.
;-)
How about parallelized?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com...
>> I was told that SQL is not multi threaded, so adding
>> processors wouldn't improve performance. Is that true?
> No, that is not true. SQL Server has many threads, and even a single query
> can be paralized over several processors.
> It is impossible to say how much machine you need for "two applications",
> since each application is different. You can look at resource usage for
> your current app (memory, disk, network, processor) and try to determine
> whether you have resources left for the other app. You would of course
> need to know about resource usage for that other app in order to know
> whether it will fit.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
> news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
>> I'm a SQL newbie, so please be kind. I have just recently installed SQL
>> as a
>> part of a new accounting/project management/CRM app that is Very
>> Important to
>> the company. It's performance is vital. I want to install an email
>> archiving
>> solution which uses SQL as the backend, but I'm wondering what sort of
>> performance hit SQL takes when you start loading databases on it.
>> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
>> Xeon
>> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
>> processors wouldn't improve performance. Is that true? How much load can
>> SQL
>> stand before showing performance degradation? What should I look for on
>> the
>> server? Processor cycles, disk reads? IS this an adequate machine for
>> running
>> the two apps I describe? We are a 125 person company with our own
>> Exchange
>> server.
>> Thanks for any info that's forthcoming.
>|||> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
> Xeon
> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> processors wouldn't improve performance. Is
To add on the other responses, SQL Server can also use multiple processors
to process multiple queries concurrently. This is especially important in
an environment with many concurrent users and/or long- running queries.
--
Hope this helps.
Dan Guzman
SQL Server MVP
http://weblogs.sqlteam.com/dang/
"maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
> I'm a SQL newbie, so please be kind. I have just recently installed SQL as
> a
> part of a new accounting/project management/CRM app that is Very Important
> to
> the company. It's performance is vital. I want to install an email
> archiving
> solution which uses SQL as the backend, but I'm wondering what sort of
> performance hit SQL takes when you start loading databases on it.
> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
> Xeon
> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> processors wouldn't improve performance. Is that true? How much load can
> SQL
> stand before showing performance degradation? What should I look for on
> the
> server? Processor cycles, disk reads? IS this an adequate machine for
> running
> the two apps I describe? We are a 125 person company with our own Exchange
> server.
> Thanks for any info that's forthcoming.|||> How about parallelized?
Yep, that was the one I was thinking of. Thanks.
This is one of those I never seem to get the right spelling for. My spell check found this type, but
my finger pressed "Ignore" and it got sent before it reached my brain-cells. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:u0hQEVHcIHA.5712@.TK2MSFTNGP04.phx.gbl...
>> even a single query can be paralized over several processors.
> Paralized (paralyzed) means the opposite of what you're trying to say, I think.
> ;-)
> How about parallelized?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.InsideSQLServer.com
> http://blog.kalendelaney.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com...
>> I was told that SQL is not multi threaded, so adding
>> processors wouldn't improve performance. Is that true?
>> No, that is not true. SQL Server has many threads, and even a single query can be paralized over
>> several processors.
>> It is impossible to say how much machine you need for "two applications", since each application
>> is different. You can look at resource usage for your current app (memory, disk, network,
>> processor) and try to determine whether you have resources left for the other app. You would of
>> course need to know about resource usage for that other app in order to know whether it will fit.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
>> news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
>> I'm a SQL newbie, so please be kind. I have just recently installed SQL as a
>> part of a new accounting/project management/CRM app that is Very Important to
>> the company. It's performance is vital. I want to install an email archiving
>> solution which uses SQL as the backend, but I'm wondering what sort of
>> performance hit SQL takes when you start loading databases on it.
>> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1 Xeon
>> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
>> processors wouldn't improve performance. Is that true? How much load can SQL
>> stand before showing performance degradation? What should I look for on the
>> server? Processor cycles, disk reads? IS this an adequate machine for running
>> the two apps I describe? We are a 125 person company with our own Exchange
>> server.
>> Thanks for any info that's forthcoming.
>|||I even wrote a blog post about getting this word wrong, whether is
misspelling or misspeaking...
http://sqlblog.com/blogs/kalen_delaney/archive/2007/07/29/geek-city-total-paralysis.aspx
:-)
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://blog.kalendelaney.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:6702E3AB-59D3-46A0-9A31-A0C509659B66@.microsoft.com...
>> How about parallelized?
> Yep, that was the one I was thinking of. Thanks.
> This is one of those I never seem to get the right spelling for. My spell
> check found this type, but my finger pressed "Ignore" and it got sent
> before it reached my brain-cells. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:u0hQEVHcIHA.5712@.TK2MSFTNGP04.phx.gbl...
>> even a single query can be paralized over several processors.
>> Paralized (paralyzed) means the opposite of what you're trying to say, I
>> think.
>> ;-)
>> How about parallelized?
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.InsideSQLServer.com
>> http://blog.kalendelaney.com
>>
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com...
>> I was told that SQL is not multi threaded, so adding
>> processors wouldn't improve performance. Is that true?
>> No, that is not true. SQL Server has many threads, and even a single
>> query can be paralized over several processors.
>> It is impossible to say how much machine you need for "two
>> applications", since each application is different. You can look at
>> resource usage for your current app (memory, disk, network, processor)
>> and try to determine whether you have resources left for the other app.
>> You would of course need to know about resource usage for that other app
>> in order to know whether it will fit.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
>> news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
>> I'm a SQL newbie, so please be kind. I have just recently installed SQL
>> as a
>> part of a new accounting/project management/CRM app that is Very
>> Important to
>> the company. It's performance is vital. I want to install an email
>> archiving
>> solution which uses SQL as the backend, but I'm wondering what sort of
>> performance hit SQL takes when you start loading databases on it.
>> It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
>> Xeon
>> 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so
>> adding
>> processors wouldn't improve performance. Is that true? How much load
>> can SQL
>> stand before showing performance degradation? What should I look for on
>> the
>> server? Processor cycles, disk reads? IS this an adequate machine for
>> running
>> the two apps I describe? We are a 125 person company with our own
>> Exchange
>> server.
>> Thanks for any info that's forthcoming.
>>
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com:
> No, that is not true. SQL Server has many threads, and even a single
> query can be paralized over several processors.
I agree with Tibor. My servers are often paralized.
Thanks for all your input over the years Tibor, but I couldn't resist.|||> Thanks for all your input over the years Tibor, but I couldn't resist.
I don't mind. Glad I've been of some help.
:-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Henson" <dhenson@.certifiednetworks.com> wrote in message
news:Xns9A465E020FBBCdhensoncertifiednetw@.64.74.227.2...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in news:5E7AC07B-52B5-4354-9066-1AF25AB6023E@.microsoft.com:
>> No, that is not true. SQL Server has many threads, and even a single
>> query can be paralized over several processors.
> I agree with Tibor. My servers are often paralized.
>
> Thanks for all your input over the years Tibor, but I couldn't resist.|||Thanks to all of you who responded. Obviously, I got some bad info. Thanks
for clearing that up.
"Dan Guzman" wrote:
> > It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
> > Xeon
> > 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> > processors wouldn't improve performance. Is
> To add on the other responses, SQL Server can also use multiple processors
> to process multiple queries concurrently. This is especially important in
> an environment with many concurrent users and/or long- running queries.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> http://weblogs.sqlteam.com/dang/
> "maitakeboy" <maitakeboy@.discussions.microsoft.com> wrote in message
> news:229F4810-47CD-4155-8C84-E45A3579C7BC@.microsoft.com...
> > I'm a SQL newbie, so please be kind. I have just recently installed SQL as
> > a
> > part of a new accounting/project management/CRM app that is Very Important
> > to
> > the company. It's performance is vital. I want to install an email
> > archiving
> > solution which uses SQL as the backend, but I'm wondering what sort of
> > performance hit SQL takes when you start loading databases on it.
> > It is SQL 2005 on a Server 2003 (32-bit) machine (Proliant DL380 G4, 1
> > Xeon
> > 3Ghz proc, 4 GB RAM. I was told that SQL is not multi threaded, so adding
> > processors wouldn't improve performance. Is that true? How much load can
> > SQL
> > stand before showing performance degradation? What should I look for on
> > the
> > server? Processor cycles, disk reads? IS this an adequate machine for
> > running
> > the two apps I describe? We are a 125 person company with our own Exchange
> > server.
> > Thanks for any info that's forthcoming.
>

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