How to segment/group data

[awaria]

I have a report that has all the desired data in a one long record set. I need to now subtotal the records by GL account.
Not sure of the best practice for doing so.
Parameters are date range and dept.

Ex: col1 col2 col3 col4
acct 10 $10 $5 $5
acct 11 $10 $5 $5
acct 20 $15 $5 $5
acct 21 $15 $10 $10
acct 30 $25 $25 $25

I want a subtotal for accounts 10 and 11, then
a subtotal for accounts 20 and 21, then
a subtotal for account 30, then
a grandtotal.

How do I accomplish this or what is the best design strategy/tool?

Thanks,

awaria

 

[MisterMo]

just nest the query into another select statement
eg:

select sum(case when col1 in 'acc10','Acc11' then col2+col3+col4 else 0 end) as Gr1,
sum(case when col1 in 'acc11','Acc12' then col2+col3+col4 else 0 end) as Gr2,
sum(case when col1 = 'acc30' then col2+col3+col4 else 0 end) as Gr3 from (select col1 col2 col3 col4
from your table)

-Mo

If you don't stand for something, you'll fall for anything

 

[xlbo]

If teh subtotals depend on the 1st difit o the number then create a new field

=LEFT(fields!acct.value,1)

then use this new field to group by

everything else should just work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question