SSRS 2012 display only group totals in tablix (?)

Question

I would like to display only totals on my tablix, is it possible, when I tried to delete last detail column, SSRS doesn't allow me (Need at least one column in body)..

This is my sample input, year is fixed, limited to 2 years (let say 2012,2013), no need dynamics here. This is demographics type data, person with dimensions (Region, Language, Age, Sex). I will need to build similar report for all of dimensions, data will have like million rows, I'm thinking to make all aggregation inside ssrs (?). This sample data only for one dim = Region.

with cte AS 
(
    SELECT  'Asia' Region, 2012 YEAR, 'Wo' Cust  

    UNION ALL
    SELECT 
        'Asia' Region, 2012 YEAR, 'Ma' Cust  

    UNION ALL 
    SELECT  'Asia' Region, 2013 YEAR, 'Li' Cust  

    UNION ALL 
    SELECT  'Asia' Region, 2013 YEAR,'Xo' Cust  

    UNION ALL 
    SELECT  'Africa' Region, 2012 YEAR, 'Samy' Cust 

    UNION ALL 
    SELECT  'Africa' Region, 2012 YEAR, 'Cora' Cust  

    UNION ALL 
    SELECT  'Africa' Region, 2012 YEAR, 'Lamy' Cust  

    UNION ALL 
    SELECT  'Africa' Region, 2013 YEAR, 'Kary' Cust  

    UNION ALL 
    SELECT  'US' Region, 2013 YEAR, 'Michael' Cust 

    UNION ALL 
    SELECT  'US' Region,2013 YEAR, 'Rachelle' Cust 

    UNION ALL 
    SELECT 'US' Region, 2012 YEAR, 'Landley' Cust
)  

SELECT * FROM cte

My goal to have tablix which looks like below, just aggregated data grouped by dim. No any details rows.

Sample of output desired:

R. Richards R. Richards · Accepted Answer · 2016-06-01T18:36:09

Use a matrix for this.

Add a matrix to the report body.

Drag the YEAR column from the dataset on to the header of the second column of the matrix.

Drag the Region column from the dataset on to the first detail column of the matrix.

Drag the Cust column from the dataset on to the second detail column of the matrix.

Change the expression of the Cust cell to be =Count(Fields!Cust.Value)

Should look something like this after:

Run the report

Pic with totals

To add a Total, right-click where you see [Region] in the matrix, and you will see an Add Total option in the context menu. Just choose where you want to put it, Before or After.

To do the percentage, you add another column after YEAR (Right-click the YEAR column choose Insert Column -> Inside Group - Right). The detail expression for this one is going to be something like this:

=Count(Fields!Cust.Value)/Count(Fields!Cust.Value, "YEAR")

The denominator part of that gets the total for the whole scope, which is named YEAR. Right-click on that cell, and choose Textbox Properties to format the number and a percentage.

SSRS 2012 display only group totals in tablix (?)

1 Answers