SSRS 100% Stacked Bar - Group on Field Names to show totals

Question

I am trying to create a 100% stacked bar chart to display totals I have of various counts I'm getting through a query. I cannot figure out how to use the grouping the allow the y axis of the chart to display the different column names of the totals I have. I have created what I want in separate charts but because the way html formats everything there is a bunch of unwanted spacing and I'm trying to create this in one chart. Example below

In this I am just getting the sum of the column I have which is returning a 1 or 0 for each item based on the status. Then for the remaining 100% I'm using an expression to subtract them from a total. =Sum(Count(Fields!Computer_Name.Value)) - Sum(Fields!ClientHealthEvaluation.Value)

I can modify my query to group all this in the results and just have a query of the totals but I still haven't found a way to make that work in one bar chart either. I would like to know if there is a way to have all this displayed in one bar chart, y axis groups are the separate columns, bars showing the totals so its cleaner or at least to know of a better way to structure all these separate charts in the image so they look more uniform. Is this possible?

--Edit - Adding sample of dataset

resourceID  IsActiveDDR IsActivePolicyRequest   IsActiveStatusMessages  IsActiveHW  IsActiveSW  ClientHealthEvaluation
16784171    0   0   0   0   0   1
16784668    1   1   1   1   0   1
16784901    0   0   0   0   0   1
16785366    1   0   1   0   0   1
16786781    0   0   0   0   0   1
16786855    0   0   0   0   0   1
16787070    1   1   1   0   0   1
16787571    0   0   0   0   0   1
16787996    1   1   1   1   0   0
16788182    1   1   1   1   0   1

This is the data i currently have and i use sum function on each column to get the totals. I can group this like below if its easier.

Total   IsActiveDDR IsActivePolicyRequest   IsActiveStatusMessages  IsActiveHW  IsActiveSW  ClientHealthEvaluation
10  5   4   5   3   0   9

---Edit: Updated dataset as suggested to now format the table in a way that will work better with a chart.

select 
'Client Health Evaluation' as 'PolicyType',
'Success' as 'Status',
SUM(a.ClientHealthEvaluation) as 'Amount'
from(
Select 
summ.Resourceid,
case summ.LastEvaluationHealthy when 1 then 1 else 0 end as 'ClientHealthEvaluation'
from v_CH_ClientSummary summ) a
UNION
select 
'Client Health Evaluation' as 'PolicyType',
'Failure' as 'Status',
(Count(a.resourceid) - SUM(a.ClientHealthEvaluation)) as 'Amount'
from(
Select 
summ.Resourceid,
case summ.LastEvaluationHealthy when 1 then 1 else 0 end as 'ClientHealthEvaluation'
from v_CH_ClientSummary summ) a
-------------
UNION
select 
'Policy Request' as 'PolicyType',
'Success' as 'Status',
SUM(a.IsActivePolicyRequest) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActivePolicyRequest
from v_CH_ClientSummary summ) a
UNION
select 
'Policy Request' as 'PolicyType',
'Failure' as 'Status',
(Count(a.resourceid) - SUM(a.IsActivePolicyRequest)) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActivePolicyRequest
from v_CH_ClientSummary summ) a
-------------
UNION
select 
'Data Discovery' as 'PolicyType',
'Success' as 'Status',
SUM(a.IsActiveDDR) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveDDR
from v_CH_ClientSummary summ) a
UNION
select 
'Data Discovery' as 'PolicyType',
'Failure' as 'Status',
(Count(a.resourceid) - SUM(a.IsActiveDDR)) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveDDR
from v_CH_ClientSummary summ) a
-------------
UNION
select 
'Hardware Inventory' as 'PolicyType',
'Success' as 'Status',
SUM(a.IsActiveHW) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveHW
from v_CH_ClientSummary summ) a
UNION
select 
'Hardware Inventory' as 'PolicyType',
'Failure' as 'Status',
(Count(a.resourceid) - SUM(a.IsActiveHW)) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveHW
from v_CH_ClientSummary summ) a
-------------
UNION
select 
'Software Inventory' as 'PolicyType',
'Success' as 'Status',
SUM(a.IsActiveSW) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveSW
from v_CH_ClientSummary summ) a
UNION
select 
'Software Inventory' as 'PolicyType',
'Failure' as 'Status',
(Count(a.resourceid) - SUM(a.IsActiveSW)) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveSW
from v_CH_ClientSummary summ) a
-------------
UNION
select 
'Status Messages' as 'PolicyType',
'Success' as 'Status',
SUM(a.IsActiveStatusMessages) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveStatusMessages
from v_CH_ClientSummary summ) a
UNION
select 
'Status Messages' as 'PolicyType',
'Failure' as 'Status',
(Count(a.resourceid) - SUM(a.IsActiveStatusMessages)) as 'Amount'
from(
Select 
summ.Resourceid,
summ.IsActiveStatusMessages
from v_CH_ClientSummary summ) a
Order by PolicyType, Status desc

Output:

PolicyType                Status    Amount
ClientHealth Evaluation   Success   13862
Client Health Evaluation  Failure   210
Data Discovery            Success   13967
Data Discovery            Failure   105
Hardware Inventory        Success   13854
Hardware Inventory        Failure   218
Policy Request            Success   14025
Policy Request            Failure   47
Software Inventory        Success   13713
Software Inventory        Failure   359
Status Messages           Success   14018
Status Messages           Failure   54

Chart:

Answer 1

If this does not help, please edit your question and include a sample of your base data and dataset output.

I created some sample data as follows - I think this is where yo might need to make a change, by calculating this in your dataset query but until I see your data its hard to tell.

Anyway, here i sthe sample data

DECLARE @t TABLE(Caption varchar(30), Colour varchar(10), Amount int)

INSERT INTO @t VALUES 
('Client Health Evaluation', 'Green', 10),
('Client Health Evaluation', 'Red', 1),
('Policy Request', 'Green', 12),
('Policy Request', 'Red', 3),
('Data Discovery', 'Green', 15),
('Data Discovery', 'Red', 2),
('Hardware Inventory', 'Green', 20),
('Hardware Inventory', 'Red', 2),
('Software Inventory', 'Green', 30),
('Software Inventory', 'Red', 5),
('Status Messages', 'Green', 10),
('Status Messages', 'Red', 2)

SELECT * FROM @t

Then I simply added a 100% bar chart and configured it like this...

All I did was drag the fields into the respective bins 'Values'/'Category Groups'/'Series Groups' and added a bit of colour formatting.

Which gives the following output.

---

EDIT using example data

---

I've taken you table (again just as a table variable so just swap out @t for the real table name.

I then unpivot the data inside a CTE so I can reference it more than once, then UNION two copies of the data, one is the actual values and one is the values subtracted from the record count to give you the "red" values.

DECLARE @t TABLE (resourceID bigint, IsActiveDDR int, IsActivePolicyRequest int,  IsActiveStatusMessages int, IsActiveHW int , IsActiveSW int,  ClientHealthEvaluation int)
INSERT INTO @t VALUES
(16784171,    0,   0,   0,   0,   0,   1),
(16784668,    1,   1,   1,   1,   0,   1),
(16784901,    0,   0,   0,   0,   0,   1),
(16785366,    1,   0,   1,   0,   0,   1),
(16786781,    0,   0,   0,   0,   0,   1),
(16786855,    0,   0,   0,   0,   0,   1),
(16787070,    1,   1,   1,   0,   0,   1),
(16787571,    0,   0,   0,   0,   0,   1),
(16787996,    1,   1,   1,   1,   0,   0),
(16788182,    1,   1,   1,   1,   0,   1);

-- SELECT * from @t

-- UNPIVOT THE DATA
WITH nd (caption, FlagSum, RecordCount) AS
(
    SELECT caption, SUM(flag) AS flagCount, (SELECT COUNT(*) FROM @t) AS TCount
    FROM    
        (SELECT IsActiveDDR, IsActivePolicyRequest, IsActiveStatusMessages, IsActiveHW, IsActiveSW, ClientHealthEvaluation FROM @t) p 
    UNPIVOT 
        (flag FOR caption 
            IN (IsActiveDDR, IsActivePolicyRequest, IsActiveStatusMessages, IsActiveHW, IsActiveSW, ClientHealthEvaluation)
        ) unpiv
    GROUP BY caption
    )

SELECT caption, 'Green' as SeriesGroup, FlagSum FROM nd
UNION ALL
SELECT caption, 'Red', RecordCount - FlagSum FROM nd

This give us the following results

You should be able to drop this straight onto your chart as per this answer above.