Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2087
  • Last Modified:

SSRS Matrix Report - Create Columns to show Row Groups % of the Subtotal and Total

Column Group SampleI have a matrix report that I want to be able to reflect two percentage columns as:
1) Each players % of their team,
2) Each teams % of the total

Attaching a sample picture, where the last two columns I am having trouble constructing in SSRS 2008.  The Team and player are row groups, and the point type is a column group that includes values of Goals Assists and Points.
0
JoeChampagne
Asked:
JoeChampagne
  • 2
1 Solution
 
Nico BontenbalCommented:
This is what the scope parameter in the aggregation functions is for. Give me half an hour to create an example.
0
 
Nico BontenbalCommented:
See the attached file for an example. You need to change the datasource of dataset DataSet1 for the report to work. See the documentation of the scope parameter here:  http://msdn.microsoft.com/en-us/library/ms159673(SQL.90).aspx (read the 'Scope' paragraph).
The forumula for the %Team column is:
=(Fields!Goals.Value+Fields!Assists.Value) 
/ 
sum(Fields!Goals.Value+Fields!Assists.Value,"table1_Team")

Open in new window

Where "table1_Team" is the name of the Team group. So sum(Fields!Goals.Value+Fields!Assists.Value,"table1_Team") is the sum of the group.
The formula for the %League column is:
=(Fields!Goals.Value+Fields!Assists.Value) 
/ 
sum(Fields!Goals.Value+Fields!Assists.Value,"DataSet1")

Open in new window

Where "DataSet1" is the name of the dataset. So sum(Fields!Goals.Value+Fields!Assists.Value,"DataSet1") is the sum of the entire dataset.
GroupPercentage.rdl
0
 
JoeChampagneAuthor Commented:
Nicobo,
Outstanding!  Thank you for the direction, and the detailed example.  I appreciate it.

Joe
0
 
Jitendra KumarDeveloperCommented:
Please tell me the detailed solution to fix problem same as your attachment
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now