Tables in Report Builder
I have a query that returns the following data:
Company Severity SubTotal Total
A low 604777 2917023
K low 253850 1497324
E low 1234336 1234336
A high 451630 2917023
B high 259880 2106841
A med 451630 2917023
K med 1243474 1497324
D med 1219110 1219110
C med 1224336 1224336
G med 1224336 1224336
H med 1219880 1219880
B med 1245461 2106841
R med 1219880 1219880
A very high 384336 2917023
F very high 1224134 1224134
B very high 601500 2106841
I want to use Report Builder and build a table like this:
Company very high high med low Total
A 384336 451630 451630 604777 2917023
...
I have spent all day trying all possible combinations to achieve this and have not been able to Can anyone point out which values should go in Column Groups
, Row Groups
andValues
Also, I would like to give the background color of the background to certain cells where the value exceeds a certain threshold
source to share
Can someone point, what values should go in Column Groups
, Row Groups
and Values
→ I assume that you are using the master table / matrix. In this case:
Company → Row Groups
Severity → Column Groups
SubTotal → Values
I would not use Total, rather let the matrix sum the totals for the total
source to share
Based on your request data (which I am calling SourceTable), you can use the PIVOT clause to put the data in the table form you are using. Try:
SELECT * FROM
(SELECT Company, Severity, SubTotal FROM My_Existing_Query) AS SourceTable
PIVOT
(
MAX(SubTotal)
FOR Severity IN ('very high' AS Very_High, 'high' AS High,
'med' AS Medium, 'low' AS LOW)
) AS PivotTable
source to share