Home > MSDN, SSRS > SSRS Transpose columns of a report dynamically when rendering in different format

SSRS Transpose columns of a report dynamically when rendering in different format

I got this question from MSDN, Link here

User Comments:-

Is it possible while exporting to excel, I can interchange rows data to columns data and columns data to rows data ? (so that customer name comes in row area; and customer code comes in column area)

I have tried to do dynamic grouping based on the Format of rendering, however in dynamic grouping not allowed to use the Global Built-In Fields, Variables, ReportItems collections.

Finally only way left for us to use our classical trick to set the visibility on and off.

This trick can be applied by creating two different Matrix and show one matrix depending on Format rendering.

My trick is to create two groups within the matrix and hide and show one group based on format rendering.

Basic design of the Matrix as shown below:-

BasicReportDesign

In above screen we have two groups, one in row group and another in column group

Let’s create one more group in each side (Row group and Column group) as shown in below screen:-

CreateAdditionalGroup

If first group is based on CustomerCode then create the new group with CustomerName

As shown in below screen:-

CreateAdditionalGroupColumn

Repeat same thing for Column Group as well.

Final Report Design will look like below screen:-

ReportDesign

In above screen the Result Field put as shown in the screen.

Now go to the visibility option for each group and set with below expression:-

=IIF(Globals!RenderFormat.Name=”EXCEL”,false,true)

Second group will be reverse of first group

=IIF(Globals!RenderFormat.Name=”EXCEL”,true,false)

Visibilyforfirstgroup

VisibilyforSecondGroupgroup

Let’s run the report

ReportResult

Let’s export the report into excel and see the output

ExcelResult

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: MSDN, SSRS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: