How to change the query in a report dynamically

The ReportSerializer class in Bold Reports® is used for serializing and deserializing report definitions in 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)
        {
            string basePath = _hostingEnvironment.WebRootPath;
            FileStream reportStream = new FileStream(basePath + @"\Resources\" + reportOption.ReportModel.ReportPath, FileMode.Open, FileAccess.Read);
            var serializer = new BoldReports.RDL.DOM.ReportSerializer();
            var reportDefinition = serializer.GetReportDefinition(reportStream);
            foreach (var dataSet in reportDefinition.DataSets)
            {
                dataSet.Query.CommandText = "";// Change the query text here.
            }
            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)
        {
            // Here, you have loaded the Test sample report from the application in the folder wwwroot\Resources.
            FileStream inputStream = new FileStream(_hostingEnvironment.WebRootPath + @"\Resources\Test.rdl", FileMode.Open, FileAccess.Read);
            BoldReports.Writer.ReportWriter writer = new BoldReports.Writer.ReportWriter();

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

            var serializer = new BoldReports.RDL.DOM.ReportSerializer();
            var reportDefinition = serializer.GetReportDefinition(inputStream);
            foreach (var dataSet in reportDefinition.DataSets)
            {
                dataSet.Query.CommandText = "";// Change the query text here..
            }

            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;
            }

            writer.LoadReport(reportDefinition);
            MemoryStream memoryStream = new MemoryStream();
            writer.Save(memoryStream, format);

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

See also