Hi

This is very easy to do in SSRS. I would post this in the SSRS group (https://social.technet.microsoft.com/Forums/sqlserver/en-US/home?forum=sqlreportingservices).

The easiest way to do this is to use your favorite search tool and type "SSRS Add Grand total".  Amazingly you will find the answer in the first couple entries (if your favorite is google).  https://msdn.microsoft.com/en-us/library/ms170712.aspx.

Basically you right click on a cell and click Add total.

SQL Server 2008 and above

CREATE TABLE dbo.Orders
(
  orderid   INT        NOT NULL,
  orderdate DATETIME   NOT NULL,
  empid     INT        NOT NULL,
  custid    VARCHAR(5) NOT NULL,
  qty       INT        NOT NULL,
  CONSTRAINT PK_Orders PRIMARY KEY(orderid)
);

INSERT INTO dbo.Orders
  (orderid, orderdate, empid, custid, qty)
VALUES
  (30001, '20060802', 3, 'A', 10),
  (10001, '20061224', 1, 'A', 12),
  (10005, '20061224', 1, 'B', 20),
  (40001, '20070109', 4, 'A', 40),
  (10006, '20070118', 1, 'C', 14),
  (20001, '20070212', 2, 'B', 12),
  (40005, '20080212', 4, 'A', 10),
  (20002, '20080216', 2, 'C', 20),
  (30003, '20080418', 3, 'B', 15),
  (30004, '20060418', 3, 'C', 22),
  (30007, '20060907', 3, 'D', 30);
GO



SELECT   CASE WHEN custid IS NULL THEN 'Subtotal' ELSE CAST(empid AS VARCHAR(10)) END emp,  SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS
(
  ( custid, empid ),
  ( empid)
 
)
UNION ALL
SELECT TOP 1 'Grand Totals',SUM(CASE WHEN emp='Subtotal' THEN qty END) OVER () qty
FROM
(
SELECT   CASE WHEN custid IS NULL THEN 'Subtotal' ELSE CAST(empid AS VARCHAR(10)) END emp,  SUM(qty) AS qty
FROM dbo.Orders
GROUP BY GROUPING SETS
(
  ( custid, empid ),
  ( empid)
 
)
) AS Der

Hi,

Is there any way to get the grantotal using ssrs expressions.

In my query iam getting dificult to get the grandtotals

Have you tried this article: -

https://msdn.microsoft.com/en-us/library/ms170712.aspx?f=255&MSPPError=-2147217396

Or this

http://www.sqlandssrssolutions.com/2013/11/adding-total-in-last-row-as-grand-total.html

Honestly all you need to do is google "ssrs grand total" for literally hundreds of exampl

I know to get normal grand total.But here in my query iam having subtotal also so if am summing this all values

i.e sum of values + sub totals also.SO This is wrong.so that iam using see my attached file

If you were to get your subtotals using SSRS grouping, you would not have this problem.

Iam getting subtotals from Query only not SSRS grouping.Now i need to get GrandTotal.Is this possible to get the GrandTotal from SSRS grouping or need to write query only for GrandTotal.Please suggest me

Why did you include subtotals into the Query? Can you remove them? Alternatively, use some column to mark subTotal Rows and use conditional SUM to exclude these subTotal rows.

Hi Naomi N

Can you explain clearly this 'use some column to mark subTotal Rows and use conditional SUM to exclude these subTotal rows'.