How can I use nested aggregation on both row and column groups?

Question

I have a row group for Tenants (which has a page break on) and a column group for Floors.

The data output is in the following format, as you can see because of other data items the TotalArea is duplicated, I need to take the first/max TotalArea and add this together for each Floor and Tenant:

I am trying to calculate the total area occupied (outside of the column grouping) as seen below:

Total Occupied Area expression: =SUM(MAX(Fields!TotalArea.Value))

And this is the outcome I get, it should be Level 1 + Level 2 = 8156 m2:

I have tried adding the Floor grouping in as a parameter =SUM(MAX(Fields!TotalArea.Value,"FloorReference")), but I get the following error:

Is this even possible, do I need to use some custom code?

Could you please provide the complete query, expected results and actual results for better help? It would be even better when you the same using sqlfiddle — Govind, Jul 07 '20 at 17:07
@Govind I have updated the expected outcome and the query results I have already provided however I am unable to provide you with the query as it comes from a CLR Stored Procedure. — AMouat, Jul 08 '20 at 07:45

score 1 · Accepted Answer · answered Jul 07 '20 at 20:21

Although this may be possible directly in SSRS I would usually calculated this directly in the dataset query and then the report is very simple.

If you are using a stored proc and cannot change it for whatever reason, you can still do this, I'll show the stored proc version below

The pure query version

I mocked up some data for two tenants, each with two floors, the data is similar to your example

I then just used a subquery to return the tenants overall floor area so you can just use this field directlyin SSRS with using FIRST() or MAX() etc.

declare @t TABLE(Tenant varchar(20), FloorReference varchar(10), TotalArea float, Value float)

INSERT INTO @t VALUES
('Customer A', 'Lv 1', 4081, 100),
('Customer A', 'Lv 1', 4081, 110),
('Customer A', 'Lv 1', 4081, 120),
('Customer A', 'Lv 1', 4081, 130),
('Customer A', 'Lv 1', 4081, 140),
('Customer A', 'Lv 1', 4081, 150),
('Customer A', 'Lv 2', 4075, 160),
('Customer A', 'Lv 2', 4075, 170),
('Customer A', 'Lv 2', 4075, 180),
('Customer B', 'Lv 1', 1000, 190),
('Customer B', 'Lv 2', 1500, 200)

SELECT 
    t.*,  fa.TenantFloorArea
    FROM @t t
    JOIN (SELECT Tenant, TenantFloorArea = SUM(TotalArea) FROM (SELECT DISTINCT Tenant, FloorReference, TotalArea FROM @t) x GROUP BY Tenant) fa 
        ON t.Tenant = fa.Tenant

This gives you the following results

The Stored proc version

Even if you cannot change the stored proc you can still get the results then extend them like this...

CREATE TABLE #t(Tenant varchar(20), FloorReference varchar(10), TotalArea float, Value float)

INSERT INTO #t
    EXEC myStoredProc

SELECT 
    t.*
    ,  fa.TenantFloorArea
    FROM #t t
    JOIN (SELECT Tenant, TenantFloorArea = SUM(TotalArea) FROM (SELECT DISTINCT Tenant, FloorReference, TotalArea FROM #t) x GROUP BY Tenant) fa 
        ON t.Tenant = fa.Tenant

This will give exactly the same results.

The best way for me to resolve this was to go back to the CLR Stored Procedure and do the calculation there, thanks for the help! — AMouat, Jul 09 '20 at 16:13

How can I use nested aggregation on both row and column groups?

1 Answers1