Easy way to Flip the Rows and Columns in a Report

This setting on the Report Editor's Advanced Setting's tab rearranges the rows and columns of the Result Set table displayed in the Report.  The values in the first n columns become the row headers. When specifying this action, your setting includes how many of the first columns should appear as bolded headers in the table.

Setting this option alone does not modify column headers nor does it affect the Report's Pivot Tables or any Chart associated with the Result Set.. Only the display of your result set in the Report is impacted by this action.

If you want to store the data in the flipped format, set the flag in the Data tab of the report.

That makes it easier to create metrics in cases where the source system stores dates in columns instead of rows

1. Access Report Editor > Advanced tab

Review the default settings and result:

  1. Flip rows and column defaults to "no"
  2. Drill to Report for data not generated set to "generates Report instance" 

1.1. Open Report Content tab

  1. Set to Include query results table in Report

1.2. Result Set table is displayed in the Report in its "normal" format

2. Return to Report Editor > Advanced tab

Return to Report Editor > Advanced tab

Flip Rows and Columns using First Row:

  1. Set Flip Rows and Columns to "Yes"
  2. Count of rows to use as column headers set to "1"

Save & publish

2.1. Modified Result Set table

The Result Set table in the report now is "wide" and "short" rather than having many rows as in the default example above

3. Illustration of Flip Rows and Columns using First 2 rows

4. Optionally, store data in new format

To Store data in transposed state, set Use Report as Source to 'Yes'

Save & publish

0 Comments

Add your comment

E-Mail me when someone replies to this comment