Restricting Exporting Options in SQL Server Reporting Services (SSRS)
Reports are one of the most important components in any BI projects. SQL Server Reporting Services (SSRS) is one of the various reporting tools available in the market. SSRS comes bundled with SQL Server and is free when you buy SQL Server License.
One of the most common need in any reporting tools is to be able to export/save the report in a different format. Reporting Services offers various exporting options like Excel, PDF etc. Often times few reports are suitable for exporting to a particular format and does not fit perfectly in other formats. Example: A huge report with some 50 fields is most suitable to be exported to Excel and will not fit into other formats like PDF/Word etc.
Following are the formats supported by SQL Server 2008 Reporting Services:
XML file with report data
CSV (comma delimited)
Acrobat (PDF) file
MHTML (web archive)
Often business users like formats like Excel, PDF etc. and do not want the other exporting formats to be available/displayed in the pull down menu for choosing an exporting format in the Report Manager.
We can control the list of exporting options available for the users using the “rsreportserver.config” file. Here is how we can do it.
Let us say we need to remove the “Word” option from the drop down for exporting option.
Initially all the exporting options would be available in the Report Manager as shown below.
Now go to the SQL Server/SSRS Installation directory and locate “rsreportserver.config” file. In my case it is located in the path: “D:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer“.
Open the “rsreportserver.config” file with notepad and locate “<render></render>” tag. This tag contains sub tags called as “<extension></extension>” for various exporting options. The “<extension></extension>” tag for exporting to Word for example is as follows:
<Extension Name=”WORD” Type=”Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering”/>
Now comment this statement/tag as follows:
<!– <Extension Name=”WORD” Type=”Microsoft.ReportingServices.Rendering.WordRenderer.WordDocumentRenderer,Microsoft.ReportingServices.WordRendering”/> –>
Now refresh the Report in the Report Manager/Server using the browser refresh button and see that the option for exporting to Word disappears from the list as shown below.
Now the business can be relaxed to see that they only have limited set of exporting options which they are interested in. This will make the presentation better and the users happy 🙂
Note: This demo is based on SQL Server 2008 Reporting Services. In other versions of SQL Server the options might slightly vary.