How to change the query in a report dynamically

To change the query in a report dynamically, load the report definition with the Bold Reports® ReportSerializer class, set the query text of each dataset, and pass the updated definition to the Report Viewer or Report Writer at runtime.

The ReportSerializer class in Bold Reports® serializes and deserializes report definitions in RDL (Report Definition Language) format. The ReportSerializer class lets you modify the report definition, including the query text, at runtime. The ReportSerializer helper is available in the Bold Reports® library to modify the report object.

How do I change the dataset query in the Report Viewer?

To change the dataset query at runtime in the Bold Reports® Report Viewer, set the query text of each dataset in the OnInitReportOptions method, as in the following code:

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

How do I change the dataset query in the Report Writer?

To change the dataset query at runtime in the Bold Reports® Report Writer, set the query text of each dataset before you call LoadReport, as in the following code:

    [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