Solved

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

Posted on 2012-04-12
4
1,921 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Dimension table indexes 8 10
SYNTAX PROBLEM -- adding another column into the stored procedure output 30 32
TSQL - IF ELSE? 3 28
Sql Join Problem 2 27
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

911 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

22 Experts available now in Live!

Get 1:1 Help Now