Solved

SSRS Matrix Grand Total

Posted on 2016-11-28
2
57 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
2 Comments
 
LVL 12

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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