Sunday, February 03, 2013 5:09 AM
When I export a report to Excel I'd like my Group Value to repeat on each row. I figured I could add a bit parameter to my report that asks the user if this report is an "export" version, and simply add a hidden column that can be displayed if the report is the "export" version. Basically I would hide/display the Group column or the new column that contains the group value but in every row. I can't get that to work though because there isn't an option to hide the group column.
My request is similar to this one, but the difference is I'm using SSRS 2012. I can't get this approach to work in 2012.
Any suggestions are appreciated. Thanks.
Tuesday, February 05, 2013 3:20 AMModerator
Microsoft Excel has limitations with how it manages hidden and displayed report items when they are exported. To work around this issue, please refer to the following steps:
1. Add a new column inside the group, and display the group values in this new column.
2. Click the column header of the group column to select the whole column, select “Delete Columns” and then “Delete Columns only”.
When preview the report the group values repeat on each row and you can get the exact result export to Excel.
TechNet Community Support
Tuesday, February 05, 2013 5:54 AM
Thanks for the reply Fanny. I tried to follow your steps but I'm not getting it to work. I can add the new column just fine, but I set the value to the same exact value I have in the other group column right next to it. When I click on the first group column and select delete the column is simply deleted. I don't have an option to delete column only. Just to be sure we're in the same version, I'm in SQL Server Data Tools (2012).
Separately, what I really want is for one report to serve two purposes - to group or not group. Are you saying that's not a good option because of the Excel's limitations displaying the hidden items? Is your recommendation to have 2 reports in that case? Another approach I tried, that seems to work, is to have 2 separate tablix in the report; 1 with grouping 1 without grouping. I show/hide the tablix based on a parameter that asks the user if it's an export version or not. It seems to work without any issues, but I will have to format/maintain 2 tablix in the report. What are your thoughts on this approach?