Solved

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

Posted on 2012-04-12
4
1,901 Views
Last Modified: 2015-02-09
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
Comment
Question by:JoeChampagne
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 37841283
This is what the scope parameter in the aggregation functions is for. Give me half an hour to create an example.
0
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 37841362
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
 

Author Closing Comment

by:JoeChampagne
ID: 37842510
Nicobo,
Outstanding!  Thank you for the direction, and the detailed example.  I appreciate it.

Joe
0
 

Expert Comment

by:Jitendra Kumar
ID: 40598321
Please tell me the detailed solution to fix problem same as your attachment
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now