SSRS calculate % of desired colums in column group in matrix

Question

SELECT        PatchPhase.PhaseName, MAX(PatchPhase.Sequence) AS seq, 
PatchState.Application, PatchState.Objectname, PatchState.Jobname, 
PatchState.Streamname, COUNT(*) AS Total,
PatchState.Jobdescription, 
PatchState.Status, PatchState.Timestamp      
FROM  PatchState 
INNER JOIN PatchPhase ON PatchState.Phase = PatchPhase.PhaseTech
WHERE        (PatchPhase.PhaseName IN (@Phase)) 
AND (PatchState.Application IN (@Application)) AND (PatchState.Timestamp >= @StartDate) 
AND (PatchState.Timestamp <= @EndDate)
GROUP BY PatchPhase.PhaseName, PatchState.Application, PatchState.Objectname, 
PatchState.Jobname, PatchState.Streamname, PatchState.Jobdescription, PatchState.Status, 
PatchState.Timestamp
ORDER BY PatchState.Application

I am working on matrix and I have column group of status which contains 3 columns (planned, running,completed). I want to sum planned+completed and divide by total column.

Total column is outside the column group.

I do find some answers but I did not get how should I use in my code can someone please help?

Answer 1

Try using this to aggregate, and then use a table instead of a matrix. You will not need a column group now. As I cannot run the sql I can't promise it is perfect but perhaps you could have a little play with it if not. It should offer a guide if nothing else.

SELECT       
PatchPhase.PhaseName, 
MAX(PatchPhase.Sequence) AS seq, 
PatchState.Application, 
PatchState.Objectname, 
PatchState.Jobname, 
PatchState.Streamname, 
PatchState.Jobdescription, 
SUM(case when PatchState.Status = 'Planned' then 1 else 0 end) as 'Planned', 
SUM(case when PatchState.Status = 'Running' then 1 else 0 end) as 'Running', 
SUM(case when PatchState.Status = 'Completed' then 1 else 0 end) as 'Completed',
(SUM(case when PatchState.Status = 'Planned' then 1 else 0 end) + SUM(case when PatchState.Status = 'Running' then 1 else 0 end)) / 
    (SUM(case when PatchState.Status = 'Planned' then 1 else 0 end) + SUM(case when PatchState.Status = 'Running' then 1 else 0 end) + SUM(case when PatchState.Status = 'Completed' then 1 else 0 end)) as totalPercentage
PatchState.Timestamp

FROM
    PatchState 
    INNER JOIN PatchPhase 
    ON PatchState.Phase = PatchPhase.PhaseTech

WHERE        
(PatchPhase.PhaseName IN (@Phase)) 
AND (PatchState.Application IN (@Application)) 
AND (PatchState.Timestamp >= @StartDate) 
AND (PatchState.Timestamp <= @EndDate)

GROUP BY 
    PatchPhase.PhaseName, 
    PatchState.Application, 
    PatchState.Objectname, 
    PatchState.Jobname, 
    PatchState.Streamname, 
    PatchState.Jobdescription, 
    PatchState.Status, 
    PatchState.Timestamp

ORDER BY 
    PatchState.Application