## 3 replies

Last post Oct 19, 2015 02:07 AM by Krunal Parekh

• it journalis...

Member

5 Points

12 Posts

### SSRS subtotal of exact number of values

Oct 13, 2015 02:28 PM|it journalist|LINK

Hi all,

I am troubling with a problem that I have been working on a project. The scenerio is like that:

I have a set of data which has 70000 rows. Each row has 10 numerical cells. This data consisting of departure cities and the destination cities of a plane. After some sorting I need to get, based on the origin city, the first 20rows and the 21st row must have the subtotal of all remaining rows.

For example by using 3-letter abbreviations of cities I have the following data:

Origin    Destination    FleetBodyType   Chargeableweight   Postweight

FRA        NYC                WIDE-BODY      3.000                       200

LAX        ACC                CARGO              65.000                     2.500

.

.

and so on

The final data should be like this

Origin    Destination    FleetBodyType   Chargeableweight   Postweight

FRA        NYC                WIDE-BODY      3.000                       200

FRA        CHI                 CARGO              60.000                     500

FRA        SAO                WIDE-BODY      35.000                     400

.

.

FRA       OTHER             -                        S.T                            S.T

This last row must consist of the subtotal of the data of the remaining rows.

Is it possible to do this?

SSRS subtotal

• Krunal Parek...

All-Star

15372 Points

2074 Posts

### Re: SSRS subtotal of exact number of values

Oct 15, 2015 02:43 AM|Krunal Parekh|LINK

Hello it journalists,

You can actually do this behind the scenes in the sql and apply that dataset directly in the tablix.

You can make use of Grouping sets to make calculate subtotal.

https://technet.microsoft.com/en-us/library/bb522495%28v=sql.105%29.aspx?f=255&MSPPError=-2147217396

```/*
IF EXISTS(SELECT 1 FROM sys.tables WHERE object_id = OBJECT_ID('myTable'))
BEGIN;
DROP TABLE [myTable];
END;
GO

CREATE TABLE [myTable] (
[myTableID] INTEGER NOT NULL IDENTITY(1, 1),
[Weight] DECIMAL(6,3) NULL,
PRIMARY KEY ([myTableID])
);
GO

INSERT INTO myTable([Weight]) VALUES('0.60'),('1.27'),('1.34'),('1.28'),('1.83'),('1.38'),('1.63'),('1.75'),('1.63'),('1.48');
INSERT INTO myTable([Weight]) VALUES('1.64'),('0.10'),('1.91'),('1.72'),('0.35'),('0.04'),('1.14'),('0.35'),('0.46'),('0.44');
INSERT INTO myTable([Weight]) VALUES('1.80'),('0.56'),('0.32'),('0.47'),('0.84'),('0.17'),('0.51'),('0.99'),('1.11'),('0.79');
INSERT INTO myTable([Weight]) VALUES('1.13'),('0.51'),('1.77'),('0.36'),('0.97'),('0.62'),('0.58'),('1.61'),('1.70'),('1.63');
INSERT INTO myTable([Weight]) VALUES('1.91'),('0.68'),('0.20'),('1.43'),('0.66'),('1.59'),('1.88'),('0.38'),('1.33'),('1.22');
INSERT INTO myTable([Weight]) VALUES('1.43'),('0.20'),('1.47'),('1.15'),('1.01'),('0.31'),('0.08'),('1.58'),('1.43'),('1.63');
INSERT INTO myTable([Weight]) VALUES('0.82'),('1.74'),('1.64'),('1.69'),('1.76'),('1.37'),('0.83'),('0.92'),('0.36'),('1.77');
INSERT INTO myTable([Weight]) VALUES('0.12'),('0.10'),('1.81'),('1.49'),('1.19'),('1.69'),('0.28'),('1.76'),('1.34'),('0.04');
INSERT INTO myTable([Weight]) VALUES('0.84'),('1.84'),('1.36'),('0.76'),('1.72'),('1.37'),('1.06'),('1.65'),('1.63'),('0.52');
INSERT INTO myTable([Weight]) VALUES('1.13'),('1.67'),('1.65'),('1.77'),('1.28'),('0.46'),('1.05'),('0.30'),('0.42'),('0.77');
*/

;With CTE1 AS
(
select
*,
(mytableid - 1) / 20 GroupID
from dbo.myTable
), CTE2 AS
(
SELECT
CASE WHEN GROUPING(myTableID) = 1 THEN 'Sub Total' ELSE CAST(myTableID AS VARCHAR)  END myTableID,
CASE WHEN GROUPING(Weight) = 1 THEN SUM(Weight) ELSE Weight END [Weight],
GROUPID
FROM
CTE1
GROUP BY GROUPING SETS(GroupID,(myTableID,Weight,GroupID))
)

SELECT * FROM CTE2

```

Result

```myTableID	Weight	GROUPID
1	0.600	0
2	1.270	0
3	1.340	0
4	1.280	0
5	1.830	0
6	1.380	0
7	1.630	0
8	1.750	0
9	1.630	0
10	1.480	0
11	1.640	0
12	0.100	0
13	1.910	0
14	1.720	0
15	0.350	0
16	0.040	0
17	1.140	0
18	0.350	0
19	0.460	0
20	0.440	0
Sub Total	22.340	0
21	1.800	1
22	0.560	1
23	0.320	1
24	0.470	1
25	0.840	1
26	0.170	1
27	0.510	1
28	0.990	1
29	1.110	1
30	0.790	1
31	1.130	1
32	0.510	1
33	1.770	1
34	0.360	1
35	0.970	1
36	0.620	1
37	0.580	1
38	1.610	1
39	1.700	1
40	1.630	1
Sub Total	18.440	1
41	1.910	2
42	0.680	2
43	0.200	2
44	1.430	2
45	0.660	2
46	1.590	2
47	1.880	2
48	0.380	2
49	1.330	2
50	1.220	2
51	1.430	2
52	0.200	2
53	1.470	2
54	1.150	2
55	1.010	2
56	0.310	2
57	0.080	2
58	1.580	2
59	1.430	2
60	1.630	2
Sub Total	21.570	2
61	0.820	3
62	1.740	3
63	1.640	3
64	1.690	3
65	1.760	3
66	1.370	3
67	0.830	3
68	0.920	3
69	0.360	3
70	1.770	3
71	0.120	3
72	0.100	3
73	1.810	3
74	1.490	3
75	1.190	3
76	1.690	3
77	0.280	3
78	1.760	3
79	1.340	3
80	0.040	3
Sub Total	22.720	3
81	0.840	4
82	1.840	4
83	1.360	4
84	0.760	4
85	1.720	4
86	1.370	4
87	1.060	4
88	1.650	4
89	1.630	4
90	0.520	4
91	1.130	4
92	1.670	4
93	1.650	4
94	1.770	4
95	1.280	4
96	0.460	4
97	1.050	4
98	0.300	4
99	0.420	4
100	0.770	4
Sub Total	23.250	4```

Hope this helps.

With Regards,

Krunal Parekh

SSRS subtotal

Krunal

MSDN Community Support
• it journalis...

Member

5 Points

12 Posts

### Re: SSRS subtotal of exact number of values

Oct 19, 2015 01:52 AM|it journalist|LINK

Dear Krunal Parekh,

Actually, I wonder whether or not this is possible in SSRS. If I return to SSIS layer of the project there surely will be some solutions. Neverthless, your answer will be useful. I will check it immediately.

Regards,

SSRS subtotal

• Krunal Parek...

All-Star

15372 Points

2074 Posts

### Re: SSRS subtotal of exact number of values

Oct 19, 2015 02:07 AM|Krunal Parekh|LINK

Hi,

#### it journalist

Actually, I wonder whether or not this is possible in SSRS. If I return to SSIS layer of the project there surely will be some solutions. Neverthless, your answer will be useful. I will check it immediately.

I am not familiar SSIS but I suggested to do this in SQL Server side to make things less complicated to this on reports. and you would only need to change the query if you want to change the outcome. Otherwise with SSRS you would have to add page breaks and probably need to write custom codes for this which is I am not sure how to go about right now.

With Regards,

Krunal Parekh

SSRS subtotal

Krunal

MSDN Community Support