How to display "Compute By" result

Question

How to display "Compute By" result

Susan S

SSCommitted

Points: 1771
More actions
June 20, 2006 at 7:33 pm

#114557

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

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 1

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

Susan S SSCommitted Points: 1771 More actions · Answer 2

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.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

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

Susan S SSCommitted Points: 1771 More actions · Answer 4

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

Susan S SSCommitted Points: 1771 More actions · Answer 5

Finally I found the solution.

I have to use CASE and SUM

then in RS I create a report without wizard.

thanks Guys