SSRS Matrix Grand Total

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
Southern_GentlemanAsked:
Who is Participating?
 
Arifhusen AnsariConnect With a Mentor Business Intelligence Developer and AnalystCommented:
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
 
Southern_GentlemanAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.