Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Hiding column group when exporting from SSRS 2008 R2 to Excel Expand / Collapse
Author
Message
Posted Monday, May 2, 2011 3:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 7, 2012 1:34 PM
Points: 4, 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.
Post #1101966
Posted Tuesday, May 3, 2011 6:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 12:18 PM
Points: 2,818, Visits: 2,561
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.
Post #1102279
Posted Thursday, June 23, 2011 11:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 23, 2011 11:09 PM
Points: 2, Visits: 2
This can be done!

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
Post #1130922
Posted Wednesday, September 5, 2012 6:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 20, 2014 1:29 AM
Points: 28, Visits: 207
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
Post #1354484
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse