calculate Percentage - SQL Server Forums

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

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

calculate Percentage

Author

Topic

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 03:08:35

Dear All Experts,

Please see the snap attached here. I have desiged one matrix report.

Now my problem is that i cant able to get Percentage automatically instead of that total is coming.

Please see the yellow color highlighted column which i want to achieve.

There are two row group and tow column group.

Row group are 1) branch 2) Name
Column group 1) Type 2) Amount

In type Sales and Collection is coming
and in Amount Group - Amt and target is coming.

I hope you get my requirement.

Please guide me how to do this.

Regards,
AKM

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 03:40:48

you need to use Inscope operator for this

See
http://visakhm.blogspot.in/2010/01/inscope-operator-in-sql-reporting.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 04:11:26

Dear Visakh,

Thanks,

Can you please let me know how to put condition for inscope for the percantage calculation. It would be really helpful form me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 04:12:55

currently whats the expressions you've put for avg?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 04:40:28

I want to calcualte percentage not Avg. Please ignore the heading of Avg in the attached snap.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 04:48:07

quote:
Originally posted by abhit_kumar

I want to calcualte percentage not Avg. Please ignore the heading of Avg in the attached snap.

Ok in any case post your current expression and also group names used for column and row groups

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 05:34:42

Row Group
=========
matrix1_RowGroup1 -- =Fields!Branch.Value
matrix1_RowGroup2 -- Fields!Name.Value

Column Group
=============
matrix1_Data -- Fields!Data.Value
matrix1_ColumnGroup2 -- Fields!Type.Value

Currently i have not used any expression for percentage calcualtion. Used subtotal option from the columngroup2.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 05:42:02

then you need to tweak the expression for % as


IIF(Inscope("matrix1_ColumnGroup2"),Your Current Expression,SUM(Fields!Amt.value,"matrix1_ColumnGroup2")/SUM(Fields!Target.value,"matrix1_ColumnGroup2"))

Under the Avg column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 06:18:26

Dear Visakh,

As per below expression Amt and target is using the same expression as:- Fields!NetAmt.Value
Hence error is coming for the field name.

IIF(Inscope("matrix1_ColumnGroup2"),Your Current Expression,SUM(Fields!Amt.value,"matrix1_ColumnGroup2")/SUM(Fields!Target.value,"matrix1_ColumnGroup2"))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 06:21:50

Sorry didnt get that
do you mean Amt Target come from same field? then how will you distinguish them?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 06:29:34

Yes its coming from same field.

Its distinguised by Columngroup named "matrix1_ColumnGroup2"

If this columnGroup contain description "Net sales" then its comes under that heading, if the description "Target" then the values comes under that heading.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:10:36

is column Group another field then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 07:12:28

There is only two Amt and target.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:17:01

Sorry your explanation is confusing. You told few posts back that they both come from same field. So my question is how do you distinguish the values to come under two headings (sales vs target) do you've a separate attribute field for that?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 07:31:42

Yes its coming from the same field. I told that heading deccription is different.I dont have the seperate field in that. But its differentiated automatically based on column group. i.e.

Type contain - sales and target

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 07:37:42

so what have you given as expression for data part? just Fields!Data.value? Is it a calculated field brought from dataset?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-27 : 07:44:41

Yes. and its bring data directly from dataset.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-27 : 13:47:45

then you need to do this at query behind. As you already bring as % you need to calculate it for subtotal row also and bring. otherwise it will just summate it which is wrong

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

abhit_kumar
Posting Yak Master

147 Posts

Posted - 2013-05-29 : 03:06:59

OK thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-29 : 03:53:46

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources