Limit report from being exported to excel

Monday, October 12, 2009 11:57 PM

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.

Pete Browning SQL Server Reporting Services This posting is provided ‘AS IS’ with no warranties, and confers no rights.

Wednesday, October 14, 2009 5:21 AM

Moderator

1

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

Jin Chen - MSFT

Marked As Answer by Jinchun ChenMicrosoft Employee, Moderator Thursday, October 22, 2009 10:29 AM

Saturday, March 13, 2010 3:37 PM

0

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");

Wednesday, November 24, 2010 1:22 PM

0

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.

Wednesday, January 26, 2011 6:17 PM

0

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

Wednesday, January 26, 2011 6:23 PM

0

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?

Tuesday, February 22, 2011 10:10 AM

0

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

Please remember to mark replies as answers or at least vote helpfull if they help and unmark them if they provide no help.

Wednesday, March 14, 2012 9:37 PM

0

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?

Thursday, May 17, 2012 3:13 PM

0

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

Suresh Thakur

Tuesday, August 28, 2012 8:20 PM

0

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.

Tuesday, September 04, 2012 2:22 PM

0

Hi,

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

Friday, January 25, 2013 1:33 PM

0

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...

Limit report from being exported to excel

All Replies

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support by product

Other support links

Not an IT pro?