How to Change the Query in a Report Dynamically

The ReportSerializer class in the Bold Reports is used for serializing and deserializing report definitions in the RDL (Report Definition Language) format. It provides a way to modify the report definition, including the query text, dynamically at runtime.

The ReportSerializer helper is available in our Bold Reports library to modify the report object.

Report Viewer

To change the dataset query dynamically at runtime in the Report Viewer, the following code can be used:

    [NonAction]
    public void OnInitReportOptions(ReportViewerOptions reportOption)
    {
        // Get the base path of the web root.
        string basePath = _hostingEnvironment.WebRootPath;

        // Open the report file stream.
        FileStream reportStream = new FileStream(Path.Combine(basePath, "Resources", reportOption.ReportModel.ReportPath), FileMode.Open, FileAccess.Read);

        // Deserialize the report definition.
        ReportSerializer serializer = new ReportSerializer();
        ReportDefinition reportDefinition = serializer.GetReportDefinition(reportStream);

        // Modify the query text for each dataset.
        foreach (var dataSet in reportDefinition.DataSets)
        {
            dataSet.Query.CommandText = ""; // Update the query text here
        }

        // Set the modified report definition in the report model.
        reportOption.ReportModel.ReportDefinition = reportDefinition;
    }

Report Writer

To change the dataset query dynamically at runtime in the Report Writer, the following code can be used:

     [HttpPost]
    public IActionResult Export(string writerFormat)
    {
        // Load the sample report file.
        FileStream inputStream = new FileStream(Path.Combine(_hostingEnvironment.WebRootPath, "Resources", "Test.rdl"), FileMode.Open, FileAccess.Read);
        
        // Create a ReportWriter instance.
        ReportWriter writer = new ReportWriter();

        string fileName = null;
        WriterFormat format;
        string type = null;

        // Deserialize the report definition.
        ReportSerializer serializer = new ReportSerializer();
        ReportDefinition reportDefinition = serializer.GetReportDefinition(inputStream);
        
        // Modify the query text for each dataset.
        foreach (var dataSet in reportDefinition.DataSets)
        {
            dataSet.Query.CommandText = ""; // Update the query text here.
        }

        // Determine the format and file name based on the writerFormat.
        if (writerFormat == "PDF")
        {
            fileName = "Test.pdf";
            type = "pdf";
            format = WriterFormat.PDF;
        }
        else if (writerFormat == "Word")
        {
            fileName = "Test.docx";
            type = "docx";
            format = WriterFormat.Word;
        }
        else if (writerFormat == "CSV")
        {
            fileName = "Test.csv";
            type = "csv";
            format = WriterFormat.CSV;
        }
        else
        {
            fileName = "Test.xlsx";
            type = "xlsx";
            format = WriterFormat.Excel;
        }

        // Load the report definition.
        writer.LoadReport(reportDefinition);

        // Create a memory stream to hold the exported report.
        MemoryStream memoryStream = new MemoryStream();
        
        // Save the report to the memory stream.
        writer.Save(memoryStream, format);

        // Set the memory stream position to the beginning.
        memoryStream.Position = 0;

        // Return the generated export document to the client side.
        FileStreamResult fileStreamResult = new FileStreamResult(memoryStream, "application/" + type);
        fileStreamResult.FileDownloadName = fileName;
        return fileStreamResult;
    }

See also

• Getting Started with the ASP.NET Core Report Viewer

• Getting Started with the ASP.NET Core Report Writer