Monday 12 November 2012

SSRS Automatically View Report after selecting drop down parameter value

In SQL Server Reporting Services (SSRS), I had a client who wanted to automatically rerun their report after selecting a value in a drop down without having to click on the "View Report" button. This can be done using JQuery and below is how it is done. 1.) Go to the "Report.aspx" page in the ReportManager directory. On My machine it is here. C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Pages\Report.aspx" 2) At the top of the file after the "Page" directive in asp.net add the following script.
<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.min.js"></script>
<script language = "Javascript">

 $(function(){
 $('body').on('change', 'select', function () {
 $('input[value="View Report"]').click();
 });
});
</script>
3) Now every time you click on the value in the drop box it will automatically postback and refresh the form with your selected parameter.

Postscript
After various feedback, people have asked for how to make the report refresh only on a particular control. This can be done with jQuery. Naturally with jQuery there are lots of ways of achieving this, so I have opted for the simplest. The short answer is here is the code below.
<%@ Register TagPrefix="MSRS" Namespace="Microsoft.ReportingServices.UI" Assembly="ReportingServicesWebUserInterface" %>

<%@ Page Language="c#" CodeBehind="Report.aspx.cs" AutoEventWireup="false" Inherits="Microsoft.ReportingServices.UI.ReportWrapperPage" EnableEventValidation="false" %>

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.min.js"></script>
<script type="text/javascript">
    $(function () {
        $('body').on('change', 'select', function () {
            var currentControl = this
            //Need to add checks here that we have selected values etc.
            var allBoxes = $('select');
            var fire = true
            allBoxes.each(function () {
                if (this.value == "0") {
                    fire = false;
                }
            });
            if (fire == true) {
                var relevantControlPrompt = 'Asset Type';
                var relaventspanText = $('label[for="' + currentControl.id + '"]').children("span").html();
                if (relaventspanText == relevantControlPrompt) {
                    fire = true;
                } else {
                    fire = false;
                }
            }
            if (fire == true) {
                $('input[value="View Report"]').click();
            }
        });
    });
</script>

How does it work? It relies on the way that SSRS renders the report page. SSRS programmatically generates the controls on the form. We don't know the name/id of the controls before we start looking at the HTML that is generated. However Microsoft kindly took advantage of the HTML "for" attribute which links form prompt labels with their relevant input control. So in jQuery I can take advantage of this.
SSRS a) Creates a list of controls with prompts stored in labels. b) the labels are flagged with "for" with a pointer to the relevant select drop down list box. c) using jQuery we can now find out if the current Select is the one that we want to watch. In the example below, we have chosen to look at the "Asset Type" drop down. ...snippet from outputted html by SSRS
<label for="ctl32_ctl04_ctl06_ddValue"><span>Asset Type</span></label>
the relevant select looks like this
<select name="ctl32$ctl04$ctl06$ddValue" id="ctl32_ctl04_ctl06_ddValue" style="width: 182px;">
So the label has a "for" attribute "ctl32_ctl04_ctl06_ddValue" which points to the id of the relevant select. The code will run for every select on the form but will only refresh the report for the chosen select. The selector uses the jquery syntax to search for a label with the for attribute = to my control id. I then get the child span and then get its html. This should then say "Asset Type" or whatever is the relevant select for your requirement.
var relaventspanText = $('label[for="' + currentControl.id + '"]').children("span").html();
Note: The javascript also includes logic to suppress refreshing until all values have been selected, if that is required.

9 comments:

  1. Hi Cameron,

    For some strange reason, it does not work for me.
    I have moved the code to the top as well as between the <%> tags, but nothing seems to work.
    Any Idea?
    I was not able to post my code as the editor does not allow for it. Please contact me somerinoz@gmail.com
    Thanks
    Jakes

    ReplyDelete
  2. Jakes,
    Typically problems here are due to Jquery not loading up properly. Firebugs is your friend as it will help you debug the issue. I have emailed you the final file and I will post it to the blog as well.

    ReplyDelete
  3. Hi. Thank you for this solution. Is is possible to specify which parameter will fire the view report event?

    ReplyDelete
  4. Hi,
    Can it only refesh on a specified drop down list instead of every list ?

    e.g I have a Date from, Date To calendar pick up list, I have an items list, I have a sub-cateogry list (I just want the report start refresh once the sub-cateogry is changed)

    ReplyDelete
    Replies
    1. Hi there are a number of ways of achieving this. The simplest is to put an if statement in to the jQuery so that the code only runs the click event if it matches the drop down list that you specify.
      Now this is a little more difficult in practice as ReportServer generates controls on the fly. One trick we can use though is to use the "label for" html code that Report Builder uses. Based on this you can then check that the relevant select has been chosen. Here is the code (change the prompt text to the control that you want to look at.)
      var relaventspanText = $('label[for="' + currentControl.id + '"]').children("span").html();

      Delete
  5. Does something have to be installed on the SSRS Server for this to work. I am really looking for my SSRS reports to auto refresh when the end-user selects a different company name.

    ReplyDelete
    Replies
    1. Hi nothing needs to be installed on the SSRS server for this to work. Just follow the instructions on the original post i.e. create a reference to jQuery in the relevant ASPX page that is installed as part of the SSRS install.

      Delete
  6. Hi,

    Can we add search filters below the table columns. Is it possible?

    Please contact me exe.dragoon@gmail.com
    Thanks
    Balaji

    ReplyDelete
  7. Balaji
    You provide filters by using the regular SSRS filtering options. i.e. add parameters to the query/data set.

    ReplyDelete