Solved

SSRS Matrix Grand Total

Posted on 2016-11-28
2
103 Views
Last Modified: 2016-11-29
I built a simple Matrix in SSRS. I'm just trying to add a Grand Total to the right of December. In the month header It should say Total
and for each year it should have the total for each month from the corresponding year. The row parameter is an account manager.

For Accountmanager it should have a total for all the 2014, 2015, 2016, and 2017

ssrssample
The final result should look like a layout like this minus the colors.

ssrssample2
0
Comment
Question by:Southern_Gentleman
[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 Comments
 
LVL 13

Accepted Solution

by:
Arifhusen Ansari earned 500 total points
ID: 41905206
Hi ,

The way you want the data in quite difficult. You can not do this in ssrs directly. You have to build some logic in query.

I have created sample for the same. Follow the below step.

1) You have to evaluate your month and year in the query it self rather than in ssrs report. Because we want to categorized the grand total as different group.

Refer the below query you will get an idea.

CREATE TABLE #TestData

( ID INT,
ValueDate DATETIME,
Data INT
)

INSERT INTO #TestData

VALUES
 (1,'20160101',2000)
,(2,'20160201',200)
,(3,'20160301',20)
,(4,'20150101',2000)
,(5,'20150201',10)
,(6,'20150301',100)
,(7,'20140101',1000)
,(8,'20140201',3000)
,(9,'20140301',300)


SELECT id,year(ValueDate) as Year, FORMAT(ValueDate,'MMMM') Month,Data FROM #TestData

UNION ALl

SELECT ID, year(ValueDate) as Year,'-1' Month,Data FROM #TestData

DROP TABLE #TestData

Open in new window


2) in your case what you can do is. Whatever data you get from the final query just insert into the temp table.

Now in final select you have to use the Year and month function on the date filed to get month and year for data.

Again do the union all on the same temp table but this time you only need to fetch the year and make month as -1. So this -1 will in other group.

3) On report side add the group on Month column and year column. In header files you can write an expression that

=IIF(Fields!Month.Value="-1","Grand Total",Fields!Month.Value)

Open in new window


I have attached the rdl file and script file.

Have a look. Hope it will help you.
Grand-Total-in-Matrix.rdl
SQLQuery1.sql
0
 

Author Closing Comment

by:Southern_Gentleman
ID: 41906548
Thanks, i thought i would have to add it in the query but didn't know if there was a way around it. make sense, and the addition of the .rdl helped. thanks again
0

Featured Post

Raise the IQ of Your IT Alerts

From IT major incidents to manufacturing line slowdowns, every business process generates insights that need to reach the people required to take action. You need a platform that integrates with your business tools to create fully enabled DevOps toolchains.

You need xMatters.

Question has a verified solution.

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

Written by Valentino Vranken. Introduction: In a previous article (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Reporting-On-Data-From-Stored-Procedures-part-1.html) I announced that I would writ…
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…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

696 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