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.

calculate total %

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

Do you have access to the source code for the dataset? It would be much easier to achieve this in your code. — Schmocken, Nov 09 '17 at 13:04
yes I can access and change the code.. but I am not sure how can I do that — Piyush Jain, Nov 09 '17 at 14:07
If you add your final select statement and confirm the DBMS you are using help can be given — Schmocken, Nov 09 '17 at 14:30

score 0 · Answer 1 · answered Nov 09 '17 at 13:03

0

You can get a value displayed in a text box from expression =Reportitems!Textbox133.Value

instead of textbox133 in the above expression you have to give the textbox name of the textbox from which you want the value. In your case the total column

answered Nov 09 '17 at 13:03

NiveaGM

249
2
11

score 0 · Accepted Answer · answered Nov 10 '17 at 09:32

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

SSRS calculate % of desired colums in column group in matrix

2 Answers2