Is there any way to ask a ReportViewer if the result of the query inside a Report will yield more than 65k rows?

Question

I have my reports in a Reporting Services server, inside my .rdl there is a query that accepts parameters. I pass those parameters with an instance of ReportViewer. I have a method that downloads the result of the report in Excel format without using the ReportViewer directly. The method is the following:

private void CreateEXCEL(Dictionary<string, string> parametros, string nombreReporte)
{
    // Variables
    Warning[] warnings;
    string[] streamIds;
    string mimeType = string.Empty;
    string encoding = string.Empty;
    string extension = string.Empty;

    // Setup the report viewer object and get the array of bytes
    string ReportServerURL = ConfigurationManager.AppSettings["ReportServerCompletitudURL"];
    string ReportName = ConfigurationManager.AppSettings["ReportNameRankingVentaPDV"] + "/" + nombreReporte;

    MyReportViewer.Reset();

    MyReportViewer.ProcessingMode = ProcessingMode.Remote;
    MyReportViewer.ServerReport.ReportPath = ReportName;
    MyReportViewer.ServerReport.ReportServerUrl = new Uri(ReportServerURL);

    List<ReportParameter> parameters = new List<ReportParameter>();

    foreach (var d in parametros)
    {
        parameters.Add(new ReportParameter(d.Key, d.Value));
    }

    MyReportViewer.ServerReport.SetParameters(parameters);

    byte[] bytes = MyReportViewer.ServerReport.Render("EXCEL", null, out mimeType, out encoding, out extension, out streamIds, out warnings);

    // Now that you have all the bytes representing the PDF report, buffer it and send it to the client.
    Response.Buffer = true;
    Response.Clear();
    Response.ContentType = mimeType;
    Response.AddHeader("content-disposition", "attachment; filename=" + nombreReporte + "." + extension);
    Response.BinaryWrite(bytes); // create the file
    Response.Flush(); // send it to the client to download

}

Now the idea is that I can't create a file with more that 65536 rows as an Excel file, the idea is to "Ask" if the result of the query inside the Report will yield more than 65k rows, then use csv format. I dont see that reportviewer server control have a method that checks the result of the query. I don't want to use pagebreaks inside the SSRS reports. Is there any way to ask in my code behind?

Answer 1

Not sure if this helps but this is a work around for exporting to excel.

Create a parent group on the tablix (or table, or list) and in the Group on: field enter the expression below.

Add Page break between each instance of a group

=CInt(Ceiling(RowNumber(nothing)/65000))

See Question on Here.

Answer 2

I found the solution to this particular problem like this:

Put this expression in my "Details" Group. In Disabled property: =IIF(rownumber(nothing) mod 10000=0,false,true) BreakLocation: End. After this change, I can save this excel divided in different worksheets in the same excel sheet for every 10k rows. I tried doing the ceiling but if you have a rownumber expression inside that group it wont work.