Limit report from being exported to excel
We have a report that we do not want to be able to be exported to excel. Is there a way, just on one report to limit the types it is exported to
October 12th, 2009 6:42pm

There is not a way to disable excel rendering (or any specific rendering format) for a single report. You are able to disable excel rendering for the server, but that would disable it for all reports on that server.
Free Windows Admin Tool Kit Click here and download it now
October 12th, 2009 11:57pm

Hi DBAMT,

 

As Pete mentioned, by default, we can only disable Excel Render Extension for the server.

 

However, a possible workaround to disable Excel Render Extension for a simply report is using JavaScript to remove the Excel option from the export formats list.

 

Please follow these steps to do so:

1.       Go to the physical folder of the Report Manager.

By default, the folder is:

C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportManager

 

2.       Please open the folder “Pages”

3.       Open the file “Report.aspx” with notepad.

4.       Embed the following JavaScript at the end of the file.

<script language = "Javascript">
//javascript: get parameter from URL
function getParameter(paraStr, url)
{
    var result = ""; 
    //get all parameters from the URL
    var str = "&" + url.split("?")[1];
    var paraName = paraStr + "=";
    //check if the required parameter exist
    if(str.indexOf("&"+paraName)!=-1)
    {
        //if "&" is at the end of the required parameter
        if(str.substring(str.indexOf(paraName),str.length).indexOf("&")!=-1)
        {
            //get the end string
            var TmpStr=str.substring(str.indexOf(paraName),str.length);
            //get the value.
            result=unescape(TmpStr.substr(TmpStr.indexOf(paraName) + paraName.length,TmpStr.indexOf("&")-TmpStr.indexOf(paraName) - 
paraName.length));   
        } 
        else
        {   
            result=unescape(str.substring(str.indexOf(paraName) + paraName.length,str.length)); 
        }
    }   
    else
    {   
        result="Null";   
    }   
    return (result.replace("&",""));   
}
 
var timer2;
var dueTime2=0
function RemoveCTLExportFormats(format)
{              
                dueTime2 += 50;
                if(dueTime2 > 30000)
                {
                                clearTimeout(timer2);
                                return;
                }
 
                var obj=document.getElementsByTagName("Select");
                for(var i=0;i<obj.length;i++)
                {
                                if (obj[i].title == "Export Formats")
                                {
                                                var k = -1;
                                                for(var j = 0; j < obj[i].length; j ++)
                                                {
                                                                if(obj[i].options[j].value.toLowerCase() == format.toLowerCase())
                                                                {
                                                                                k = j;      
                                                                                obj[i].options.remove(k);
                                                                                clearTimeout(timer2);   
                                                                                return;                                                                  
                                                                }
                                                }
                                                                                
                                                                                                                                
                                }
                }
                timer2=setTimeout("RemoveCTLExportFormats('" + format + "')",50);
                
}
 
function RemoveOption(report, format)
{
                if(getParameter("ItemPath", location.href).toLowerCase() == report.toLowerCase())
                {
                                timer2=setTimeout("RemoveCTLExportFormats('" + format + "')",50);
                }
                else
                {
                                return;
                }
}
 
RemoveOption("/ReportSamples/10000rows", "Excel");
</script>
 

To enable the function, please pass in the “report path”, and the format in the method RemoveOption.

Sample: RemoveOption("/ReportSamples/report1", "Excel");

 

Please feel free to ask if you have any more questions.

 

Thanks,

Jin Chen

October 14th, 2009 5:21am

sir i am Adnan

i want to restrict export options i have SPS 2007 and Reporting Services 2008 and in Sharepoint Integrated Mode.

One of the report is employee time attendance sheet.rdl i dont want that user will able to export it to any other format except pdf how it is possible please guid me , i  also write this function as it is described and tell me one more thing that in my case

i have these steps to do:

1.       Go to the physical folder of the Report Manager.

By default, the folder is:

C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportManager

 

2.       open the folder “Pages”

3.       Open the file “Report.aspx” with notepad.

4.       Embed the following JavaScript at the end of the file.

but i could not get that how to
To enable the function,

please pass in the “report path”, and the format in the method RemoveOption. // i did not get this please guid

Sample: RemoveOption("/ReportSamples/report1", "Excel");

Free Windows Admin Tool Kit Click here and download it now
March 13th, 2010 3:37pm

This might work fine with web version of reportviewer but how do i accomplish this for windows version of reportviewer. I am facing a problem in 2010 reportviewer wherein I can not export to word (works fine for excel and pdf) if i have "Go to report". How will I disable export to Word alone??? I know this is a known issue but there must be a work around

 

Also, the header repeater columns wont work in 2010 if the report spans more than one page. I have to do it either VS 2005/2008 and then convert it in 2010.

November 24th, 2010 1:22pm

Hi Jin Chen,

This was really useful code. The only catch is that in your code it does not accept report names with spaces. When I removed the spaces from my report name it worked brill.

Thanks Again for the code, this is really useful when we need to restrict some reports which cause issues while exporting to excel. I have not tried it yet but can I apply it to multiple reports?

 

Regards,

Vikram Kansal

Free Windows Admin Tool Kit Click here and download it now
January 26th, 2011 6:17pm

Yup just checked accepts multiple reports as well. Only Spaces is the issue.

Also Can you let me know what needs to be done to restrict Excel in the Report Viewer Control?

January 26th, 2011 6:23pm

Hi All,

I just followed the steps Jin Chen proposed on a SQL 2008 R2 report server, unfortunately without any succes. Did any of you try that ?

Your feedback will be appreciated,

Cees

 

Free Windows Admin Tool Kit Click here and download it now
February 22nd, 2011 10:10am

curious to see if the new SQL Server 2012 has a fix for this limitation so you do not have to hack with javascript?  Perhaps you know?
March 14th, 2012 9:37pm

Hi Jin,

 

This code does not work for Ajax based report viewer 2010 control. Can you provide code for report viewer 2010 also?

Thanks in advance.

Regards

Suresh

Free Windows Admin Tool Kit Click here and download it now
May 17th, 2012 3:13pm

The code for the 2010 Report Viewer is pretty similar.  As you may see if you view the source of a page containing a ReportViewer, the difference is that there is now a floppy disk which, when clicked, opens a div that contains the options, instead of a dropdownlist.  So instead of searching for an option titled "Excel" you will be searching for an anchor (that is <a href /> tag) with the same title.  The prettier way to hide it is to set the display style of its parent to "none".

In other words, what you want is something like this:

function HideExtension(ext)
{
        reportViewer = document.getElementById("<% = ReportViewer1.ClientId %>");
        if (reportViewer) {
            var Ancors = reportViewer.getElementsByTagName("a");

            for (var i = 0; i < Ancors.length; i++) {
                if (Ancors[i].title.toUpperCase() == ext.ToUpperCase()) {
                   Ancors[i].parentNode.style.display = "none";
                }
}

You can modify the above sample as it best fits your needs and make it more similar to Jinchun's code if you like.

Also, there is another method that kind of works.  I will post it here only because it has a single advantage: hiding stuff with javascript allows people to view your page's source and with a Web-Console execute the commands  you meant to disable.  Hiding stuff on the server-side, whenever possible, causes contols to not be rendered onto the page at all, so such hacks aren't accessible any more.

Many blogs and forums around the web show a way to hide export options for reports displayed in Local mode (for example, here).  With a little manipulation, this or similar code samples can be modified to work on Remote mode as well...but there is one problem: the first time that the report is rendered, the changes are not visible.  If the report-viewer is refreshed once more afterwards, the second time around, the changes will be visible.  So it's not quite as clean and I wouldn't necessarily recommend it as a solution, but it provides for a server-side manner in which to hide the extensions.  My server-side solution looks like this:

Imports Microsoft.VisualBasic
Imports System.Collections
Imports System.Reflection
Imports Microsoft.ReportingServices.ReportRendering
Imports Microsoft.Reporting.WebForms

Public Class ReportViewerExtensionManager
    Public Shared Sub RemoveReportingExtension(ByVal viewer As ReportViewer, ByVal name As String)
        Const flags As BindingFlags = BindingFlags.NonPublic Or BindingFlags.Public Or BindingFlags.Instance

        ' CommonService.ListRenderingExtension is an internal method that returns a list of supported
        ' rendering extensions. This list is also stored in a class field so we can simply get this list
        ' and add Aspose.Words for Reporting Services rendering extensions to make Microsoft Word
        ' export formats appear on the dropdown.

        ' Get the service type.

        If (viewer.ProcessingMode = ProcessingMode.Local) Then
            Dim previewService As FieldInfo = viewer.LocalReport.GetType().GetField("m_previewService", flags)
            Dim listRenderingExtensions As MethodInfo = previewService.FieldType.GetMethod("ListRenderingExtensions", flags)
            ' Get the ListRenderingExtensions method info.
            Dim extensions As IList = TryCast(listRenderingExtensions.Invoke(previewService.GetValue(viewer.LocalReport), Nothing), IList)

            ' process all extensions and remove any visible ones (invisible ones must not be removed, as they are required internally by the ReportViewer)
            Dim i As Integer = 0
            If (extensions IsNot Nothing) Then
                While (i < extensions.Count())
                    If (extensions(i).IsVisible) And (extensions(i).Name.ToString().ToUpper() = name.ToUpper()) Then
                        extensions.RemoveAt(i)
                        Exit While
                        ' since we just removed an item here, the next item will fall on this same index.  Hence, we must not increment the index i
                    Else
                        ' if the item was not visible and did not get removed, we must increment the index i so as to check the next item.
                        i = i + 1
                    End If
                End While
            End If
        Else
            ' Get the ListRenderingExtensions method info.
            Dim renderingExtensions As FieldInfo = viewer.ServerReport.GetType().GetField("m_renderingExtensions", flags)

            Dim extensions As IList = renderingExtensions.GetValue(viewer.ServerReport)

            ' process all extensions and remove any visible ones (invisible ones must not be removed, as they are required internally by the ReportViewer)
            Dim i As Integer = 0
            If (extensions IsNot Nothing) Then
                While (i < extensions.Count())
                    If (extensions(i).Visible) AndAlso (extensions(i).Name.ToString().ToUpper() = name.ToUpper()) Then
                        Dim isVisible As FieldInfo = extensions(i).GetType().GetField("m_isVisible", flags)
                        isVisible.SetValue(extensions(i), False)
                        Exit While
                        ' since we just removed an item here, the next item will fall on this same index.  Hence, we must not increment the index i
                    Else
                        ' if the item was not visible and did not get removed, we must increment the index i so as to check the next item.
                        i = i + 1
                    End If
                End While
            End If
        End If

    End Sub
End Class
Now you can call the above by simply using:  ReportViewerExtensionManager.RemoveReportingExtension(myReport, "EXCEL")

Again. if your report is running in LocalMode, it will work perfectly.  If your report is running in RemoteMode, this code will set the Excel option to be cleared, but it won't actually clear it until the report is refreshed once more (you can refresh it an extra time in your code, right then, but obviously this will cause an unnecessary second rendering each time).

I hope you have found this post helpful.

August 28th, 2012 8:20pm

Hi,

Thanks for the above post. I wanted to find out the steps to implement the above code in BIDS project.

Free Windows Admin Tool Kit Click here and download it now
September 4th, 2012 2:22pm

Hi Jin,

I have placed the above java script code into 'Report.aspx' file and i passed my report path (to which i want to disable export to Excel option) to "RemoveOption("/ReportSamples/myreport", "Excel");"

but still i m able to view the Excel option in Export list. Please let me know what the issue could be...

January 25th, 2013 1:33pm

Hi Vikram,

Can you tell what values you passed for the function to disable excel, when tried in SQL server 2008R2 it is not working 

I have passed 

RemoveOption("ServerName/ReportServer/ReportFolder/ReportName", "Excel")

Please help if this correct format or not..

Free Windows Admin Tool Kit Click here and download it now
November 7th, 2014 1:26am

Hi RamaKrishna,

Are you able to resolve it, i'm also facing same issue.

Please let me know if you any solution.

Thanks

November 7th, 2014 2:20am

By doing that, the above script is rendered in the browser outside the html tag and therefore is not executed by the browser.

Free Windows Admin Tool Kit Click here and download it now
March 12th, 2015 1:28pm

Hi Mr Chen,

I've added the code to the end of the page and changed the report name in the execution of method but still the report renders to excel. Do I need to restart server or something else in order for it to take effect?

April 1st, 2015 3:33am

Is there anything to change if using SSRS R2?

It doesn't seem to work for me.

Thanks

Free Windows Admin Tool Kit Click here and download it now
April 12th, 2015 2:22am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics