SSRS Exporting Options for Excel

When exporting to excel, sometimes SSRS can behave in a very unpredictable ways.  This little post has a few pointers when configuring excel-friendly SSRS reports.

One frequently requested feature is remove the report headers when exporting to excel.  To do that, you must add some code to the SSRS configuration file to tell SSRS to not export headers.  To do that, locate the rsreportserver.config file by navigating to C:/Program Files/Microsoft SQL Server/MSRS[Version].[Instance]/Reporting Services/ReportServer.

Then add the following code to the <Render> tag.

<Extension Name="EXCEL (No Header)" Type="Microsoft.ReportingServices.Rendering.ExcelRenderer.ExcelRenderer,Microsoft.ReportingServices.ExcelRendering">
<OverrideNames>
   <Name Language="en-AU">Excel (No Header)</Name>
</OverrideNames>
<Configuration> 
<DeviceInfo>
   <SimplePageHeaders>True</SimplePageHeaders> 
</DeviceInfo> 
</Configuration> 
</Extension>

To get SSRS to pick up the changes, you'll have to go into report server configuration tool and restart the service.

When you navigate to a report on that server, and you click on the export icon, you should see the new option of "Excel (No Header)".

As you save the file off, you'll notice that the headers are not exported.  If you have blank rows or columns before your data begins, then you need to align your tablix of data such that there is no empty space between the tablix and the header or report edge. I find the best way to accomplish this is to open the tablix properties and set the location to 0,0.

Data Driven Subscription using SharePoint List

In this article I will walk through the steps to create a data-driven subscription using a SharePoint list as a data source.  I'm using SSRS 2012 integrated in SharePoint 2010.

In the data driven subscription pane, choose a Description, set the Data Source Type to Microsoft SharePoint List, enter the url to the SharePoint site (obviously replacing the {SharepointDomain}/{SPSite}/ with the appropriate values, and entering in the credentials to be used by the subscription to access the list and execute the report.

The Next part is building the Query to be used.  I find it easiest to build the query by opening up SSRS Report Builder or Visual Studio and using the Query Designer inside of SSRS to help me.  In the example below, I've set up a SharePoint Data Source in my report called "SharePoint" which has the same same connection string as above:  http://{SharepointDomain}/{SPSite}

Opening up the Query Designer will take you to a page that looks like this.  In this example, I'm going to use the "subscription test" list for my data-driven values for the subscription.  I select the fields I need, and in this case, I put a filter in it to restrict my list to only list items that have a BeginDate less than 10/1/2013.  You may test your data by pressing the "Run Query" button as shown in the green box below.  This allow you to see exactly how the data will be returned.

Pressing "OK" takes me back to the original window and gives me my query syntax.

I copy the selected syntax and then go back to the data-driven subscription window and paste that code into the Query section.  Pressing the "Validate" button will check to make sure the syntax is correct.  At this point, the hard part is done...the rest of the subscription should be pretty straight forward.

The next page lets us wire up the parameters with the BeginDate and EndDate we retrieved from the SharePoint list query.

The next page lets us wire up the email address to the one in our SharePoint list.  It also requires us to specify a rendering type and subject.

The remainder of the subscription is setting the schedule just like any other subscription, so I won't provide any details on that screen.  Pressing "Finish" gives you a data driven subscription using a SharePoint list as your data source!

The hardest part for me is getting that query.  I've found that using the SSRS query designer to help me write the query is much easier than typing it all in myself.  The query designer is usually where I go to get the "stub" of my query, and then I manually change the bits and pieces in it that I need for the particular situation.  Good luck in your next SharePoint driven Data-Driven subscription!