SSRS: creating a report optimized for exporting to Excel

Question

I have a client that needs a particular report that he can export to excel. I haven’t started to build the report, but I’m thinking the best way to do this is to keep the report as simple as possible. This way, when the client exports the report, it should be relatively clean.

Does anyone have any recommendations? I already have a report created for him. I plan to make a separate report with the sole purpose of exporting to excel.

Edit: I should also mention that the original report is a straightforward report. It has some groupings, report header and footer, description (text box). I plan to remove all of these for the excel-optimized report.

Answer 1

I would still do a custom footer with the url to the report with parameters. Including the footer will help trouble-shooting any questions about the data and it won't show in the spreadsheet, only if they preview or print. It's always good to have a reference of where the data is coming from and how to recreate it. If not for the end-user, for the developer.

=Globals!ReportServerUrl + "/ReportServer?" 
+ Replace(Globals!ReportFolder, " ", "+") + "%2f" 
+ Replace(Globals!ReportName, " ", "+") + "&rs:Command=Render"
+ "&single_value_parameter=" + Parameters!single_value_parameter.Value 
+ "&multi_value_parameter=" + Join(Parameters!multi_value_parameter.Value, "&multi_value_parameter=") 
+ IIf(IsNothing(Parameters!week_date_start.Value), "&week_date_start:isnull=True", "&week_date_start=" & Format(Parameters!week_date_start.Value, Variables!FormatDate.Value))
+ IIf(IsNothing(Parameters!week_date_end.Value), "&week_date_end:isnull=True", "&week_date_end=" & Format(Parameters!week_date_end.Value, Variables!FormatDate.Value))

Also, I usually add this as a report variable and then you can have a standard textbox for the footer that doesn't have to change.

=Variables!UrlReportWithParameters.Value

On the report server, you could also change the configuration for the export of to place the SSRS header in the Excel header instead of the grid.

<Extension Name="EXCEL" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering" Visible="false">
    <Configuration>
        <DeviceInfo>
            <SimplePageHeaders>TRUE</SimplePageHeaders>
        </DeviceInfo>
    </Configuration>
</Extension>

You could also set the visibility of the controls in the header and footer to the following expression.

=IIF(Globals!RenderFormat.Name = "EXCEL" OR Globals!RenderFormat.Name = "EXCELOPENXML", TRUE, FALSE)