SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hiding column group when exporting from SSRS 2008 R2 to Excel


Hiding column group when exporting from SSRS 2008 R2 to Excel

Author
Message
daisyd65
daisyd65
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 34
I'm working with a tablix in SSRS 2008 R2. I had to add a parent group with the code
"=Int((RowNumber(Nothing)-1)/65000)" as the group expression because my users will be exporting the data to Excel and we have an older version that cuts off at 65,000 rows and I had to break the data on two sheets when that happens.

Anyway, because it's a Matrix, it automatically adds it as a column group. I don't want this empty column to show, since it's only a container for the row limit code. I can hide the column, but it leaves white space when it renders in SSRS (no big deal), but it also creates a blank column in Excel when it exports, which I absolutely don't want. I tried to change the dataelementoutput to no output, but what that does is remove all of my detail rows, since the grouping for the rowlimit has to be on the details. In SSRS 2005, when you added a group it was automatically a row (not a column) and when you hid it, it stayed hidden and did not export to Excel.

Does anyone know of a way to not export the column group to Excel? Thanks, Denise.
Daniel Bowlin
Daniel Bowlin
SSCrazy Eights
SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)SSCrazy Eights (8.3K reputation)

Group: General Forum Members
Points: 8250 Visits: 2629
I am not aware of a way to do this outside of building another report that does not contain the data you do not want to export.
frank.pearson
frank.pearson
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 2
This can be done! :-D

The way you do this is check the Globals!RenderFormat.Name variable. This will work in 2008 R2 but not earlier versions. Check for the string value EXCEL. You can then hide columns based on this using an expression on the Hidden property:

=IIF(GlobalsRenderFormat.Name = "EXCEL",true,false)

The way you add the hidden expression to an actual column instead of one of or a collection of fields is to click on advanced mode on the right of the grouping pane while a tablix is selected. Your columns will be in the right pane, your rows will be in the left pane. The columns in the right pane will be listed in order top to bottom representing from left to right for the columns in the tablix.

http://technet.microsoft.com/en-us/library/dd255242%28SQL.100%29.aspx

Adjust the "Hidden" property

Frank
ioana-477197
ioana-477197
SSC Veteran
SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)SSC Veteran (271 reputation)

Group: General Forum Members
Points: 271 Visits: 278
Hey!

This does not work for CSV. Do you know a work around for export to CSV? I need to set the DataElementOutput dynamically, based on some parameters.

Thank you,
Iana
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search