SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 calculate Percentage
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  03:08:35  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 05/27/2013 :  03:40:48  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  04:11:26  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  04:12:55  Show Profile  Reply with Quote
currently whats the expressions you've put for avg?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  04:40:28  Show Profile  Reply with Quote
I want to calcualte percentage not Avg. Please ignore the heading of Avg in the attached snap.


Edited by - abhit_kumar on 05/27/2013 04:42:17
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  04:48:07  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  05:34:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  05:42:02  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  06:18:26  Show Profile  Reply with Quote
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"))


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  06:21:50  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  06:29:34  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  07:10:36  Show Profile  Reply with Quote
is column Group another field then?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  07:12:28  Show Profile  Reply with Quote
There is only two Amt and target.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  07:17:01  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  07:31:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  07:37:42  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/27/2013 :  07:44:41  Show Profile  Reply with Quote
Yes. and its bring data directly from dataset.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/27/2013 :  13:47:45  Show Profile  Reply with Quote
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
Go to Top of Page

abhit_kumar
Posting Yak Master

147 Posts

Posted - 05/29/2013 :  03:06:59  Show Profile  Reply with Quote
OK thanks
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/29/2013 :  03:53:46  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000