June 20, 2006 at 7:33 pm
Hi,
How do I display the result from compute in reporting?
I want the report as
Acc No Net Change
7900 $678,908,876
7902 $657,76
And so on
I create a query as below and run in query analyser and works fine
select [G_L Account No_],sum(Amount) AS Amount
from “MyDatabase”
where [G_L Account No_] between 7000 and 9000
group by [G_L Account No_],Amount
order by [G_L Account No_]
compute sum(Amount)by [G_L Account No_]
Thanks,
Susan
June 20, 2006 at 11:00 pm
That COMPUTE BY clause splits your results up into lots of tiny, independant resultsets, each divided by an additional independant resultset with your COMPUTE BY results for the preceeding data resultset. Reporting Services works best on single resultsets.
Also, you won't need the COMPUTE BY clause if you get rid of the Amount column in the GROUP BY clause. That's causing your Sum(Amount) calulation to just return each distinct amount value for the GL Account, multiplied by the number of occurrences of that Amount value for that specific GL Account.
where [G_L Account No_] between 7000 and 9000
group by [G_L Account No_],Amount
order by [G_L Account No_]
compute sum(Amount)by [G_L Account No_]
If you want to have the sum of Amounts for each [G_L Account No_], yet would like to see the detail if necessary on the report, then ditch the SUM() and GROUP BY completely, and use Reporting Services' grouping and subtotaling abilities.
COMPUTE and COMPUTE BY have been hanging around for the last few versions of SQL Server, but they are obsolete, and painful to use. If you need that kind of functionality called from T-SQL and included in the resultsets, take a look at CUBE and ROLLUP.
-Eddie
Eddie Wuerch
MCM: SQL
June 20, 2006 at 11:27 pm
Thanks Eddie.
I try to folow your advise not to use group by, however the error message below is appeared. How do I get around with this ?
Thanks again
An error occurred while executing the query.
Column G_L Entry.G_L Account No_' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
June 21, 2006 at 10:29 am
If you get rid of the GROUP BY, you must also get rid of the SUM() (This is if you plan on using the detail in your report. Otherwise, leave the GROUP BY and SUM() in place):
select [G_L Account No_] AS GLAcct, Amount
from “MyDatabase”
where [G_L Account No_] between 7000 and 9000
order by [G_L Account No_]
-Eddie
Eddie Wuerch
MCM: SQL
June 21, 2006 at 7:54 pm
I used this method in reporting( cos the user only interested in total bal, doesn't care about the detail)
select ge.[G_L Account No_],ga.[Name],SUM(ge.Amount) As ‘Bal’
from “MyDatabase” ge
left join “MyDatabase1”ga on ge.[G_L Account No_] =ga.No_
where ge.[G_L Account No_] between 7000 and 9000
group by ge.[G_L Account No_],ga.[Name]
order by ge.[G_L Account No_]
and the result is
AccNo Name Bal
7020 aa 50
7021 bb 70
7022 cc 15
7023 Dd 25
7024 Ee 15
7025 Ff 70
How do I summarise the sum only for certain accountNo.
So it will appear like below
Name Total
Cash 135 ( from 7020+7021+7022)
Bond 95 (From 7023 + 7025)
I have no idea what I should do?
thanks,
Susan
June 22, 2006 at 5:59 pm
Finally I found the solution.
I have to use CASE and SUM
then in RS I create a report without wizard.
thanks Guys
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply