SSRS Report Builder Transpose Columns to Rows

0
votes

I have set up an SSRS report against a SharePoint list as the data source using Report Builder 3.0. The list has a lot of fields that don't suit well for being listed across the top of the page as columns.

I am looking for a simple way to transpose the data so that the column headers become row headers. I just think there has to be a simpler way than creating a row group and selecting/inserting each field and typing a heading one by one.

Essentially I want a simple way to have report builder convert this:

**Title**     **Field1**    **Field2**      **Fieldn**

Record 1      A             B               C

Record 2      D             E               F

...to this:

**Title**   Record 1

**Field1**  A

**Field2**  B

**Fieldn**  C

-page break-

**Title**   Record 2

**Field1**  D

**Field2**  E

**Fieldn**  F

...without having to manually set up the expression for each field and the associated title.

1
reporting-servicesssrs-2008-r2transposereportbuilder3.0

1 Answers

0
votes

For Example:

Your primary dataset (tableA_DataSet):

select [some columns].... from tableA

Add another dataset to get column names (Columns_DataSet):

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'tableA' [and other filters if applicable...]

　

******************************************************************
** Listview to tableA_DataSet and group by unique primary key   **
******************************************************************
****            Tableview to Columns_DataSet                  ****
****     Show table and a simple formula to the cell value:   ****
****  First(Columns_DataSet!fieldName.Value, "tableA_DataSet")****
******************************************************************