Hiding totals in matrix when there is only one row in the group

[brookseitz]

How can I hide the entire column totals row when there is only one row in the group and the totals are redundant?

[brookseitz]

@Greg_Deckler   Any luck hiding unnecessary subtotal rows?

[brookseitz]

@Greg_Deckler 

I fixed the sort issues using the "Sort By Column" functionality. One down, 2 to go.

Here's the updated code:

DECLARE @Summary table

(

[GroupSort] tinyint,

[Group] varchar(50),

[TypeSort] tinyint,

 [Type] varchar(20),

[SubType] varchar(50),

[Value] decimal(16,4),

[DisplayValue] decimal(16,4)

)

INSERT INTO @Summary([GroupSort],[Group], [TypeSort], [Type], [SubType], [Value], [DisplayValue])

VALUES (1,'Capital', 1, 'FUNDS', 'Capital', 21060552, 21060552),

(2,'Capital to Investments', 1, 'FUNDS', 'Commitments', -3000000, 3000000),

(2,'Capital to Investments', 1, 'FUNDS', 'Fees Paid>Commitments', -202000, 202000),

(3,'Assumed Fees*', 1, 'FUNDS', 'Assumed Fees', -2757569, 2757569),

(1,'Capital', 2, 'DIRECTS', 'Capital', 21060552, 21060552),

(2,'Capital to Investments', 2, 'DIRECTS', 'Commitments', 0, 0),

(2,'Capital to Investments', 2, 'DIRECTS', 'Fees Paid>Commitments', 0, 0),

(3,'Assumed Fees*', 2, 'DIRECTS', 'Assumed Fees', -2757569, 2757569)

SELECT * FROM @Summary

[Greg_Deckler]

@brookseitz I don't think that's really a thing but if you can provide sample data could maybe give it a shot.

[brookseitz]

It's absolutely a thing that I've done for a long time in other reporting tools. Actually there are several things I'm trying to figure out how to do that are fairly easy to do elsewhere.

1. Hide repetitive total rows (i.e. there's only one detail row in the group

2. Sort columns, groups and rows

3. Display absolute values (in accounting, some values are assumed to be negative)

I kinda figured out the sorting issues but haven't resolved how to hide the sort values/groupings.

Here's an example of where I am:

Here's were we're trying to get to and also would like to show absolute value for all numbers:

Thanks for your help,

Brook

[Greg_Deckler]

@brookseitz Yeah, but can you save me having to type a bunch of fictious data and just post some sample data as text that can be used to recreate the scenario?