Solved

SSRS Matrix Grand Total

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

14 Experts available now in Live!

Get 1:1 Help Now