Solved

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

Posted on 2012-04-12
4
1,981 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

695 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