What really happens when you have visible parameters and click the "View Report" button? RRS feed

  • Question

  • My end game here is to use filtering instead of parameters.  I'd like for my query to initially bring back all the data that could possibly be viewed, and then use filtering to limit/shape what the user sees.  I don't like the look of the stock parameter drop down boxes and want to create text boxes that when clicked on, will alter the filtering of the report.  The only way I know to make this work is to go into the Text Box properties and choose the Action tab, and then "Go to report" with the new parameter values.  But, won't doing this actually just rerun the report from scratch by re-fetching the query results with the new parameter values?

    I've been trying to educate myself by reading articles, and I'm not sure I'm understanding things completely.  I'm thinking, though, that if you use the traditional parameter approach and keep them visible, and click the "View Report" button, SSRS is smart enough to just redisplay/filter the report without rerunning the query...is this correct?  If so, is there a way to mimic clicking the "View Report" button?

    Monday, December 10, 2012 8:14 PM

All replies

  • You would create hidden or internal parameters for each of your text boxes. In the action property where you rerun the report, Add each of these parameters to "Use these parameters to run the report:" and set their values to the appropriate text box value with ParameterName in the Name column and ReportItems!TextboxName.Value in the Value column. This does rerun the report althogh it should leverage the cache to speed execution.

    You also have a choice whether to filter the dataset using the parameter values or filter the tablix/chart with the parameter values. Usually, filtering the tablix or chart is quicker. That is done by opening the Tablix Properties, select the Filters tab and add the appropriate filters, ensuring that data type for the filter matches both the expression and the value.

    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    Monday, December 10, 2012 9:41 PM
  • Thanks for the quick response Tim!  I'm doing things exactly as you described in your first paragraph, but just wasn't sure if this was the equivalent of hitting the "View Report" button, or if maybe there was another trick that I could use that would better utilize the caching.  You did answer one of my other questions in your second paragraph, however, and that was "where is the best place to use the filtering, in the query or in the report". 
    Tuesday, December 11, 2012 12:57 PM
  • Hi Puffster,

    As pre my understanding, when filter data for a report at query, the query will includes only the data that we need for the report. When create a filter expression in the report, the report will filter data which after all of them are retrieved from the data source. So, we filter report data at the source level has superior performance than in the report level.
    For more information, please see:
    Troubleshooting Reports: Report Performance
    Filtering Data in a Report

    Fanny Liu

    Fanny Liu
    TechNet Community Support

    Tuesday, December 25, 2012 1:27 AM