CODEWITHSUNDEEP
sqlreportbuilder
Adam
Adam

Reputation: 57

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 wasted all day trying all possible combinations to achieve this and failed Can someone guide which values should go in Column Groups, Row Groups and Values

Also, i would like to give background cell color to certain cells where the value exceeds a certain threshold

Upvotes: 3

Views: 93

Answers (2)

FortyTwo
FortyTwo

Reputation: 2639

Can someone guide which values should go in Column Groups, Row Groups and Values -> I assume you are using the table/matrix wizard. In that case:

Company -> Row Groups

Severity -> Column Groups

SubTotal -> Values

I would not use Total, rather let the matrix sum up the sub totals for total

Upvotes: 1

SandPiper
SandPiper

Reputation: 2906

Based on your query data (which I call SourceTable), you can use a PIVOT clause to put the data into the tabular form you are after. Give this a 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

Upvotes: 2

Related Questions