Solved

SSRS Matrix Grand Total

Posted on 2016-11-28
2
45 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Have you ever had to extract data from a Microsoft SQL Server database and export it to an Excel file, but did not want to use a DTS package? The concept in this article is not new, but it is the answer and will also work on 64-bit SQL boxes.   …
Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

777 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