How to display "Compute By" result

  • 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

  • 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

  • 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.

     

  • 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

  •  

    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

  • 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