Are SSRS SubReports efficient?

Question

(I'm a developer with 25+ years experience, but I'm quite new to SSRS, and so I'm reaching out to get a better understanding of a couple of fundamentals.)

Sub-reports.

Everything I've read so far is that you create the sub-report as a standalone .rdl or .rdlc file, and then you link this file into your master report. That part makes sense.

However, in searching for help on how to do this efficiently, I see the same pattern repeated in tutorials: use an unfiltered SELECT statement (no parameters or WHERE clause) as your data source, and then use a filter to restrict the displayed data to what you need.

I need to understand just one thing. Is this the "best-and-only" way, or is this an example of one way that works, that's easiest to explain, but in reality performs horribly under load? Seriously, nobody expects NorthWind or AdventureWorks to be real-world examples of complicated data streams.

We're writing this report for a client that has tens of thousands of rows that would be returned in an unfiltered SELECT statement, but would return at most 10 rows if I can use a proper SELECT statement with a WHERE clause based on a Parameter passed from the main report to the sub-report. I have no idea yet what the response time would be for this report, but if it's going to fetch and process many thousand rows for the subreport, the speed would be terrible.

Can someone direct me to a blog or other source that discusses efficient handling of SSRS subreports?

Answer 1

I can only conclude that all of the tutorials I found so far were offering over-simplified solutions that simply do not scale well.

I lucked out onto a thread that was dealing with a different issue, but the OP put up his solution, and it was exactly what I needed.

To summarize: 1) Because a sub-report is only an .rdlc, there is no room for pre-processing at all. Any pro-processing has to be handled in the main report's .ASPX.CS code.

2) Within your .ASPX.CS Page_Load, add a new SubreportProcessingEventHandler to your ReportViewer, like:

yourReportViewer.LocalReport.SubreportProcessing += new
SubreportProcessingEventHandler(SetSubDataSource);
yourReportViewer.LocalReport.Refresh();

3) In your EventHandler, you make your database connection, and fetch back just the data you need for your subreport. You don't fetch it all, then filter it, which is horrendously inefficient.

    public void SetSubDataSource(object sender, SubreportProcessingEventArgs e)
    {
        // Reload the data required
        SqlConnection conn = null;
        SqlCommand cmd = null;
        conn = new SqlConnection();
        conn.ConnectionString =
            ConfigurationManager.ConnectionStrings["connectionStringFromWebConfig"].ConnectionString;
        conn.Open();
        cmd = new SqlCommand();
        cmd.Connection = conn;
        SqlDataAdapter dataAdapter = new SqlDataAdapter();
        DataSet dataset = new DataSet();
        if (e.ReportPath == "yourSubReport")
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "yourSubReportStoredProcedure";
            cmd.Parameters.AddWithValue("@subReportParam1", subReportParam1.Text);
            dataAdapter = new SqlDataAdapter(cmd);
            dataAdapter.Fill(dataset);
            e.DataSources.Add(new ReportDataSource("yourSubReportDataSetName", dataset.Tables[0]));
         }
    }