0

I have data like so:

NAME  TITLE  SALARY  HIREDATE
-------------------------------
HANK  BOSS   100     1/1/2016
JOHN  JERK   100     1/1/2015
MIKE  PUNK   200     1/1/2014

We want to show this data as such:

NAME      HANK   JOHN    MIKE
-------------------------------
TITLE     BOSS   JERK   PUNK
SALARY    100    100    200
HIREDATE  1/1/16 1/1/15 1/1/14

This is how my client needs to see the data, unfortunately.

How can this be done with SSRS (sql 2012)?

I tried to create a Matrix, I was able to get the names as columns on top. But when I tried to do the rest of it, no luck.

Thanks!

enforge
  • 915
  • 3
  • 10
  • 26

1 Answers1

0

You can use a matrix with column groups to pivot your data, you will need the settings of your matrix look similar to this:

enter image description here

Just add NAME in the Column Groups pane, then right click the first row and select Insert Row - inside the group, do that three times each for TITLE, SALARY and HIREDATE.

For SALARY use =SUM(Fields!SALARY.Value). Also you may want to hide the last empty row, so select the entire row, right click it and go to Row Visibility, select Hide.

You will get:

enter image description here

Let me know if this helps.

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Alejandro, if this works you are awesome. I am going to give this a shot now. Thanks – enforge Nov 11 '16 at 20:54
  • When I right click on the column I can insert cols within the group, but inserting rows just gives me above and below as options. I am using VS 2010 for SQL 2008 for this currently. – enforge Nov 11 '16 at 20:57
  • I figured it out. Not sure why you are using SUM, but I assume that is if there are multiple salary rows for the same person. This works great, thanks again – enforge Nov 11 '16 at 21:01
  • Hey Alejandro, not sure if you are still seeing these, but what if the values were all numbers, but both the Names and the row headers (title, salary, hiredate) were all dynamic, (such as VendorA, VendorB, VendorC)? – enforge Nov 14 '16 at 21:25
  • @enforge, you can ask a new question including a sample dataset and more details about the new issue you have. – alejandro zuleta Nov 15 '16 at 00:13