reporting services unpivoting in chart

I'm looking to be able to create a report which shows some summary data and full details from the same source. Sample data is like this:

create table #data (id INT,[type] NVARCHAR(30), error1 INT, error2 INT, error3 INT, error4 INT,error5 INT)

INSERT INTO #data values (1,'a',1,0,1,0,1)
INSERT INTO #data values (2,'a',0,0,1,1,0)
INSERT INTO #data values (3,'b',1,1,0,0,0)
INSERT INTO #data values (4,'c',1,1,1,1,1)
INSERT INTO #data values (5,'b',0,0,1,0,1)
INSERT INTO #data values (6,'a',0,0,0,0,1)

And what it's showing is for each id and type the number of errors (in fact there are 18 columns of error type and about 40,000 rows of data).

What I want is to be able to show on my report 1) a chart showing for each type the number of each error and 2) a table showing the full details of the errors are.

As it stands I am able to create a horizontal stacked bar chart which shows for each type the number of errors - i.e:

A | error1 sum | error2 sum | error3 sum | error4 sum | error5 sum     
B | error1 sum | error2 sum | error3 sum | error4 sum | error5 sum     
C | error1 sum | error2 sum | error3 sum | error4 sum | error5 sum 

But what I would like to get is:

Error1 |  Sum of A  |  Sum of B  |  Sum of C    
Error2 |  Sum of A  |  Sum of B  |  Sum of C    
Error3 |  Sum of A  |  Sum of B  |  Sum of C    
Error4 |  Sum of A  |  Sum of B  |  Sum of C    
Error5 |  Sum of A  |  Sum of B  |  Sum of C

And then ideally to be able to sort by the total开发者_如何学运维 of A + B + C (descending)?

Hopefully this makes sense - new to reporting services so not sure how the best way to approach this would be - I could rerun my data to output in a different format if it would help!

Thanks :)

First create a view to make your data to look more flat (table like)

Eg

SELECT type, 'error1' As errorname, error1 AS error
FROM data

UNION ALL

SELECT type, 'error2', error2
FROM data

UNION ALL

....

Then make a query that selects from the view

SELECT type,errorname, SUM(error) AS error
FROM data
GROUP BY type,errorname

You can use the matrix of reporting services to display the data and make charts with any combination you need