Create multiple report rows from single row of data

Question

I've been asked to create a report grouped by values in multiple columns on a single row from a SQL database. For example

Employee Name | Type1Column1 | Type1Column2 | Type2Column1 | Type2Column2
John Doe      | 0.00         | 10.00        | 20.00        | 20.00

Report Output

Name          | Type      | Total
John Doe      | Type1     | 10.00
              | Type2     | 40.00

I hope you get the idea and no i did'nt design the table and no i can't change it but i might be able to tweak the stored procedure which produces the output...

I played around with a few ideas but I'm at a loss; I feel like need some sort of calculated something to group against but i've never come up against this before as i've always designed the datawarehouse and cube myself.

Does anyone have any idea how i could achieve this in SSRS 2008R2 ?

Thanks in advance.

Are the columns always a fixed number for each type, and are the types known ahead of time? I.E. in your example, will the dataset always have `Type1Column1`, `Type1Column2`, `Type2Column1` and `Type2Column2`? If so, then you won't need to group on these, you can just add another details row to the report and sum the fields in the report. — Jamie F, Mar 07 '12 at 03:28
There's always the same number of data columns (5 x 7) and you know that they are there ahead of time. I still don't get quite what you mean ? Do you mean repeat the John Doe Column into each detail line (one per type) and then hide the ones which have no data ? i.e Zero totals for that type ? — John Kattenhorn, Mar 07 '12 at 08:41

score 3 · Accepted Answer · answered Mar 06 '12 at 23:46

Can you apply the UNPIVOT operator to the query returning that output?

select
EmployeeName, 
case
    when Type like 'Type1%'
    then 'Type1'
    else 'Type2'
end TypeCol,
Total 
from (select EmployeeName, Type1Column1, Type1Column2, Type2Column1, Type2Column2
     from storedprocdata ) as f
unpivot (Total for Type in (Type1Column1, Type1Column2, Type2Column1, Type2Column2)
) as u

Note that the derived table f is grabbing data from a table called storedprocdata. Also the case statement in the main select statement allows grouping the columns you have explicitly by the different types you want.

You can then do an aggregate sum on the Total column within your SSRS report.

Interesting idea; I've heard of the UnPivot statement but never used it; I;m sure you can appreciate that in real life the dataset is a lot more complex than my example but i'll have a go and see if i can make it work - thanks. — John Kattenhorn, Mar 07 '12 at 08:43

score 1 · Answer 2 · answered Mar 07 '12 at 12:00

1

The unpivot operator should be more flexible, but (expanding on Jamie F's comment on the question) it would be possible to do this by setting up 5 detail rows (one for each type) and setting the expression for the Total column to be like:

=Fields!Type1Column1.Value+ Fields!Type1Column2.Value+ Fields!Type1Column3.Value+
 Fields!Type1Column4.Value+ Fields!Type1Column5.Value+ Fields!Type1Column6.Value+
 Fields!Type1Column7.Value

- for the Type 1 detail row, with similar expressions for each of the subsequent Type detail rows.

answered Mar 07 '12 at 12:00

Sounds daft but how could i make the report look like above; if i add 5 details lines there will be a small 'step' between the header i.e. John Doe and the detail lines. I guess i could repeat the Name on each detail line and then hide any line which the total add's up to 0. Sorry - i'm typing whilst i'm thinking! – John Kattenhorn Mar 07 '12 at 16:20
@JohnKattenhorn: Don't include a group header line on Employee Name - instead, put the Employee Name on the first of the five detail rows (and leave the other four detail row cells for the column name empty). – Mar 07 '12 at 16:42

Create multiple report rows from single row of data

2 Answers2