Post reply

Hiding column group when exporting from SSRS 2008 R2 to Excel

  • May 2, 2011 at 3:18 pm

    #238936

    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.

  • May 3, 2011 at 6:54 am

    #1319018

    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.

  • June 23, 2011 at 11:10 pm

    #1343466

    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

  • September 5, 2012 at 6:32 am

    #1533765

    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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply