SSRS How to display data in table columns

Question

I have two fields, one named "StatusCode" and one name "Projects"

Status         projects

Active         Project A

Not started    Project B

Active         Project C

How do I display the data in SSRS in such way in a table?

Active       Not Started

Project A    Project B

Project C

So what I ended up doing was to create multiple lists (tablix) with filters in them.

(Since only tablix could apply filters in there)

Thanks for the answers!

Use 2 lists with a filter so each list only contains projects for the one status. — mmarie, Aug 19 '14 at 18:25

score 0 · Answer 1 · edited May 23 '17 at 11:57

Method 1: SSRS

Update the SQL

SELECT Project, Status , Row_Number() OVER (Partition BY Status ORDER BY Project) RN FROM ProjStatTbl
Create a matrix
Column Grouping on Status
Row Grouping on newly created Rank RN
Delete left most column only not the associated group.
In the main cell put the Project. Here is final matrix.
Voila! you are done

Method 2: SQL
You can transpose the columns in SQL using pivot transformation or using Aggregates.

Here is the SQL Statement

SELECT  
  MAX(CASE WHEN Status = 'Active' THEN Project ELSE NULL END) AS Active
, MAX(CASE WHEN Status = 'Not Started' THEN Project ELSE NULL END) AS NotStarted
FROM
  (SELECT Project, Status
      , Row_Number() OVER (Partition BY Status ORDER BY Project) RN
   FROM ProjStatTbl) Y
GROUP BY RN

Once data is transposed now you can use table to represent your data.

If you have more Status then you would need to update your SQL statement or use Dynamic pivot to generate Columns. Here is a link which shows different methods of transposing your SQL data.

Simple way to transpose columns and rows in Sql?

Method 1 works fine, but it will create an empty value in each row...be something like Active | Not Started A | | B | C — Felixable, Aug 19 '14 at 20:43
@user3755064 Check the updated answer. Updated the SQL query and used the new column for row grouping. — Anup Agrawal, Aug 20 '14 at 05:43

score 0 · Answer 2 · answered Aug 20 '14 at 16:50

0

So what I ended up doing was to create multiple lists (tablix) with filters in them.

(Since only tablix could apply filters in there)

Thanks for the answers!

answered Aug 20 '14 at 16:50

Felixable

21
5

If the number of Statuses change you would need to add more tablix(s). – Anup Agrawal Aug 20 '14 at 19:57
Indeed. I still want to see if there's a way to create just on tablix but do the same thing. – Felixable Aug 20 '14 at 20:16

SSRS How to display data in table columns

2 Answers2