How to do display total by Groupwise in report.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

How to do display total by Groupwise in report.

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-26 : 23:59:57

Hello Experts,

I have made one matrix report.

currently report is coming in this manner as per sql query:-

AccountName	Branch1	Branch2	Branch3	Branch4

100		100	200	300	400
101		20	30	40	50
200		30	40	50	60
201		10	20	30	40

I want the Report output should be like this as below:-

AccountID	Branch1	Branch2	Branch3	Branch4

100		100	200	300	400
101		20	30	40	50

TOTAL		120	230	340	450

200		30	40	50	60
201		10	20	30	40

TOTAL		40	60	80	100

DIFFERENCE	80	170	260	350

There are two total required branchwise based upon the AccountID.
First Total for account staring with 1 and second total starting with 2.

And then i need difference at bottom, for (First Total - Second Total) branchwise.

Since im new to Reporting part, so can any one elaborate me to perform this by step by step, it will be really helpful to me.

Regards,
ABHI

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 00:03:24

you need to add a new column to resultset using RowNumber function and then use it as a top level grouping expression such as
((rownumber -1) /2)

then in its group footer give your expressions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 00:39:49

Im still not clear, can u please give the rownumber logic for better understanding.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 00:47:38

can you post your current query?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 01:00:01

[code]
select AccountID,BRANCHName,

(case when OACT.GROUPMASK =1--(THis is for account staring from 1)
then sum(Debit) - sum(Credit)
when OACT.GROUPMASK =2--((THis is for account staring from 2)
then (sum(Credit)-sum(Debit)
else 0
end) as Amount
from jdt1 inner join ojdt on jdt1.transid=ojdt.transid
inner join oact on JDT1.account = OACT.AcctCode
where jdt1.accountid in (@Account) and jdt1.BranchName in (@Branch)
and (JDT1.RefDate >= (@FromDt)) AND (JDT1.RefDate <= (@ToDt))
group by AccountID,BRANCHName,OACT.GROUPMASK
[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 01:07:21

[code]SELECT (RowNo-1)/2 AS GrpVal,AccountID,BRANCHName,Amount
FROM
(
select ROW_NUMBER() OVER (ORDER BY AccountID) AS RowNo,
AccountID,BRANCHName,

(case when OACT.GROUPMASK =1--(THis is for account staring from 1)
then sum(Debit) - sum(Credit)
when OACT.GROUPMASK =2--((THis is for account staring from 2)
then (sum(Credit)-sum(Debit)
else 0
end) as Amount
from jdt1 inner join ojdt on jdt1.transid=ojdt.transid
inner join oact on JDT1.account = OACT.AcctCode
where jdt1.accountid in (@Account) and jdt1.BranchName in (@Branch)
and (JDT1.RefDate >= (@FromDt)) AND (JDT1.RefDate <= (@ToDt))
group by AccountID,BRANCHName,OACT.GROUPMASK
)t
[/code]

then use GrpVal for first level of grouping and give the reqd expressions in its group footer

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 01:33:29


0	111002	AHD	1479852.190000
0	111002	BLR	127584.140000
1	112002	AHD	407700.000000
1	112002	BLR	682441.840000
2	200101	AHD	11200601.020000
2	200101	BLR	4739386.890000
3	202020	AHD	7623.000000
3	202020	BLR	26619.000000

Grp value whch is returning in above output is 0,1,2,3 when i have used ur query.

For Account starting with 1 i think GrpValue would be 0 and with 2 it should be 1. Is this logic u have tried?

Is it above output query is correct?

and since im new to reporting then can u plz let me know how to use GrpVal for first level of grouping and give the reqd expressions in its group footer?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 01:40:19

quote:
Originally posted by abhit_kumar

0 111002 AHD 1479852.190000
0 111002 BLR 127584.140000
1 112002 AHD 407700.000000
1 112002 BLR 682441.840000
2 200101 AHD 11200601.020000
2 200101 BLR 4739386.890000
3 202020 AHD 7623.000000
3 202020 BLR 26619.000000

Grp value whch is returning in above output is 0,1,2,3 when i have used ur query.

For Account starting with 1 i think GrpValue would be 0 and with 2 it should be 1. Is this logic u have tried?

Is it above output query is correct?

and since im new to reporting then can u plz let me know how to use GrpVal for first level of grouping and give the reqd expressions in its group footer?

see your reqmnt was to group the records two at a time right?
If thats case, wont the above GrpVal be enough for it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 01:49:48

Hey visakh,

I think you are going into wrong direction.

As per my query, its generate data for the AccountNumber which is starting with 1(100,101,102,103,111) and 2 (222,228,229)branchwise. And i want then total of amount of all the accounts starting with 1 and 2 branchwise.

See this output.


I want the Report output should be like this as below:-


AccountID	Branch1	Branch2	Branch3	Branch4

100		100	200	300	400
101		20	30	40	50

TOTAL		120	230	340	450

200		30	40	50	60
201		10	20	30	40

TOTAL		40	60	80	100

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 01:55:21

oh ok. if thats the case you need to tweak query like this


select LEFT(CAST(AccountID AS varchar(15)),1) AS RowNo,
AccountID,BRANCHName,

(case	when OACT.GROUPMASK =1--(THis is for account staring from 1)
		then sum(Debit) - sum(Credit)
		when OACT.GROUPMASK =2--((THis is for account staring from 2)
		then (sum(Credit)-sum(Debit)
		else 0
	end) as Amount
from jdt1 inner join ojdt on jdt1.transid=ojdt.transid
inner join oact on JDT1.account = OACT.AcctCode 
where jdt1.accountid in  (@Account)  and jdt1.BranchName in (@Branch)
and  (JDT1.RefDate >= (@FromDt)) AND (JDT1.RefDate <= (@ToDt)) 
group by AccountID,BRANCHName,OACT.GROUPMASK

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 02:05:15

yeah..now i think u get exact idea.

so now can you please tell me how to use RowNo in reporting part
to display total brancwise.

please explain step by step becoz im new in it. It will be quickly resolve the issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 02:10:30

ok. before that can i ask how many groupings you've currently applied?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 02:15:10

ok,

you can see my report layout at:-

http://s943.photobucket.com/albums/ad273/abhit_kumar/?action=view¤t=1.jpg

Currently i have one matrixgroup. At row level grouping Account is defined and column level grouping BranchName is defined in this matrix group.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 02:27:58

just put another rowgroup before current group and give expression as =Fields!RowNo.value and then in its data section put your aggregated expressions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-02-27 : 03:09:37

Hello Visakh,
Thx for the reply.

I have put another row grp before current grp of Account and give expression
=Fields!RowNo.value, but i dont know from where we will get data section of that row grp what will be correct expression for such type of scenario?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 23:55:15

quote:
Originally posted by abhit_kumar

Hello Visakh,
Thx for the reply.

I have put another row grp before current grp of Account and give expression
=Fields!RowNo.value, but i dont know from where we will get data section of that row grp what will be correct expression for such type of scenario?

put expression in group footer of group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2010-03-01 : 23:42:44

hello visakh,

In the matrix group there is not any type of option of group footer.
I think this type of option available at tabular report,because im unable to get the option of group footer in matrix report.

Currently i have used subtotal in matrix, due to that now subtotal of both type of account is coming correct, now at last row i want the dfference of these two subtotal, which i cant able to get it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources