How to change the database in an SSRS report at run-time

Question

I am attempting to produce a c# program to run an SSRS report on any one of a number of identical databases, the target database to be specified at run-time. To this end, I create a solution and project, and in this project include an SSRS report. This report has a dataset LegislationData which invokes a stored procedure in a specimen database.

I am now trying to get this to run in the C# project. I create a form with a report viewer and a go button and attempt to set up the report. I envisaged some code along the following lines:-

MyReport my_report = new MyReport();

my_report.ConnectionString = "blah blah";               // or
my_report.DataSet.ConnectionString = "blah blah";       // or
my_report.LegislationData.ConnectionString = "blah blah"

and then

report_viewer.Report = my_report;        // or
report_viewer.LocalReport = my_report;   // or
report_viewer.SetReport(my_report);

but none of these things actually exist.

Can someone explain to me very slowly and in words of one syllable what I need to do here? I have looked at the answers to similar questions here and here but to be frank the answers make no sense.

score 1 · Answer 1 · answered Dec 20 '17 at 14:06

The first thing you need to realise is that SSRS has to be added into your C# application as a web reference. There's a guide on how to do this here: https://msdn.microsoft.com/en-gb/library/ms169926.aspx. Basically it sounds worse than it is, and it should only take a few minutes to configure all this. I found that MSDN link was corrupted for me, so here's another place that discusses how to do this: https://sqluninterrupted.com/2012/03/04/adding-a-reporting-services-web-reference-to-net-application/.

Once you have your report running from a C# application you will need to decide what you want to do with the output, convert it to PDF, stream it to the screen, save it as Excel, etc.

I haven't done this before, but it looks as though you can embed a data source into your report that uses an expression based on a parameter. So you would pass in a parameter to run the report that would be a connection string. You would also need to pass in any other parameters you might have in your report.

So step 1 add the web reference for SSRS.

Step 2 add some code to run your report, e.g. here's an example that returns the report as a byte array in PDF format:

public byte[] RenderReport(ReportExecutionService rs, string reportName, int variant)
{
    Console.WriteLine("Rendering " + reportName + "_" + variant.ToString("00"));

    byte[] result = null;
    string reportPath = "/Prototypes/Inheritance Letters/" + reportName;
    const string format = "PDF";
    const string devInfo = @"<DeviceInfo><Toolbar>False</Toolbar></DeviceInfo>";

    //Prepare report parameters
    var parameters = new ParameterValue[2];
    parameters[0] = new ParameterValue { Name = "row_id", Value = variant.ToString() };
    parameters[1] = new ParameterValue { Name = "bulk_run", Value = "1" };
    rs.ExecutionHeaderValue = new ExecutionHeader();
    rs.LoadReport(reportPath, null);
    rs.SetExecutionParameters(parameters, "en-gb");
    try
    {
        string encoding;
        string mimeType;
        string extension;
        Warning[] warnings;
        string[] streamIDs;
        result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);
        rs.GetExecutionInfo();
        return result;
    }
    catch (SoapException e)
    {
        Console.WriteLine(e.Detail.OuterXml);
        return null;
    }
}

Step 3 pass the connection string as one of the parameters, and use an expression in an embedded data source in your report to pick this up and use it.

Step 4 decide what to do with the rendered output. For example, here I render a report then save the output to a PDF:

        byte[] result = new Render().RenderReport(rs, "ACQ_Welcome_Letter", i);
        new Render().CreatePDF(i, "Welcome Letter", "ACQ_Welcome_Letter" + "_" + fuelType, result);

Here's the CreatePDF method, it has a lot of other garbage in for my particular solution, but it gives you a taste of how to do this:

public string CreatePDF(int variant, string subFolder, string reportName, byte[] result)
{
    //We want 16 variants, but we pass in a number from 1 to 48, so use modulo division to convert this back to a 1 to 16 range
    variant = (variant - 1) % 16 + 1;
    Console.WriteLine("Saving " + reportName + "_Variant_" + variant.ToString("00") + ".pdf");
    try
    {
        //Determine the target folder/ filename for the PDF
        //Snail Mail has its own folder, all PDFs go into that folder and then are manually processed
        string folder = @"S:\Change Management Documents\SMETS1\Inheritance Comms\" + subFolder + @"\";
        string filename = reportName + "_Variant_" + variant.ToString("00") + ".pdf";

        //Remove any existing content
        string[] filePaths = Directory.GetFiles(folder, filename);
        foreach (string filePath in filePaths)
            File.Delete(filePath);

        //Now save the PDF
        string path = folder + @"\" + filename;
        FileStream stream = File.Create(path, result.Length);
        stream.Write(result, 0, result.Length);
        stream.Close();
        return filename;
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
        return "";
    }
}

How to change the database in an SSRS report at run-time

1 Answers1