Dynamically changing report's data source in report viewer

[Bill]

I created a report using the stand alone designer. I am programmatically setting the report's source as such...

Dim uriReportSource As New Telerik.Reporting.UriReportSource()

            ' Specifying a URL or a file path
            Const Directory As String = "~/Custom/"
            Dim myPath As String = Server.MapPath(Directory & "41x_44x.trdx")
            uriReportSource.Uri = myPath

            Me.ReportViewer1.ReportSource = uriReportSource
            Me.ReportViewer1.DataBind()

How do I dynamically change the datasource of the report that is already loaded into my report viewer (when clicking a button or other similar event)? More detail: The report already contains it's own data source based on a sql view through the report designer component. Let's say I want to click a button on my page to dynamically switch to another sql view and then rebind the report. I already have my sql data source code, but I can't seem to find a way to attach that data source to an existing report loaded in my report viewer. Is this even possible to do?

[Elian]

Hello Bill,

When you set UriReportSource the viewer gets the file from the specified Uri and instantiates the report in order to display it in the viewer. If you want to be able to modify the report dynamically, you have to do the same (changing the DataSource means changing the report definition dynamically). 
If you are using .trdx files, the easiest way is the following:

1. Read the file
2. Deserialize it into a report. 
3. Change the DataSource of the deserialized report object
4. Assign the modified report to the viewer by using InstanceReportSource

If further modifications are needed, you can get the ReportSource from the viewer and cast it to InstanceReportSource and then get its ReportDocument (which will be the same report object that you have deserialized and modified earlier). 
 
Greetings,
Elian
the Telerik team

[Bill]

I don't think I quite understand what you mean by "reading" the file and deserializing it. I have attempted to cast my existing report loaded in my reportviewer to an instancereportsource, but I keep getting errors saying that the methods are obsolete. I would prefer to work with .trdx files, since this is more like using Crystal Reports.

So, do I need to create a new report from my existing report definition and then set it's datasource? I really have no clue what to do after I have declared my sqlDataSource string. I have referred to the online help, but there are no real examples of how to manipulate this. The examples seem more geared towards working with compiled reports from a web project, rather than working directly with .trdx reports. Compiling reports into their own libraries seems to be a tedious and administrative nightmare. This is quite frustrating, to say the least. Could you provide a real-world example? Thanks.

[Elian]

Hello Bill,

The ReportSource property of the viewer holds the value that you assign to it. So if you are using UriReportSource initially, you can't later cast it to InstanceReportSource or anything else different than UriReportSource or the base ReportSource.

The .trdx itself is a xml serialized report definition.

What you need to do is:

• deserialize the .trdx file and get the report definition as an object (see how: Serializing Report Definition in XML)

• alter the report definition that you just deserialized according to your needs
• set the modified report definition as ReportSource of the viewer

The code should look something like the following:

var settings = new System.Xml.XmlReaderSettings();
settings.IgnoreWhitespace = true;

using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create("<dir>\Report_Name.trdx", settings))
{
    var xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();

    var report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);

    //modify report

    this.reportViewer1.ReportSource = new InstanceReportSource
    {
        ReportDocument = report,
    };
}

 
Greetings,
Elian
the Telerik team

[Mark]

I'm posting this in case others had the same problem as I did.....
I couldn't find any solutions that were web specific, so this is a quick solution using jQuery.

// When the reportName is switched, it will load it 
this.switchReports = function (reportName) {

    // In order to switch reports, remove data from the div
    $("#reportViewer1").removeData();

    // Load the new report
    ({
        serviceUrl: '@Url.Content("~/api/reports/")',
        templateUrl: '@Url.Content("~/templates/telerikReportViewerTemplate-8.1.14.618.html")',
        reportSource:
        {
            report: reportName
        },
    });

}

[Florian]

I also tried to change my datasource at runtime - this is my code: my problem is that after executing this code - the data will not be updated, because the query in this code is another query I entered at design time. What I'm doing wrong?

  System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
            settings.IgnoreWhitespace = true;


            //read the .trdx file contents
            using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create(@"C:\Projekt\M+F\Testreport.trdx", settings))
            {
                Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer =
                    new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();


                //deserialize the .trdx report XML contents
                Telerik.Reporting.Report report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
                report.DataSource = "select * from versicherte4711 where vers_nummer = 200930190";
                
                


                Telerik.Reporting.InstanceReportSource reportSource = new Telerik.Reporting.InstanceReportSource();
                reportSource.ReportDocument = report;
                
                ReportViewer1.ReportSource = reportSource;
                //ReportViewer1.RefreshData();
                //ReportViewer1.DataBind();
                ReportViewer1.RefreshReport();
                
            }

[Bill]

This is an example of what I have based on the click event firing for a button...

protected void rbtnGo_Click(object sender, System.EventArgs e)
{
 // Click handler for rbtnGo.
 Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
 sqlDataSource.ConnectionString = "YOUR_DB_CONNECTION_STRING";
 sqlDataSource.SelectCommand = "SELECT * FROM [YOUR_TABLE]";
 System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
 settings.IgnoreWhitespace = true;
 //WHERE THE REPORTS RESIDE...
 const string Directory = "~/Custom/";
 using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create(Server.MapPath(Directory) + "REPORT_NAME.trdx", settings)) {
  Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();
  dynamic report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
  report.DataSource = sqlDataSource;
  this.ReportViewer1.ReportSource = new InstanceReportSource { ReportDocument = report };
 }
 this.ReportViewer1.DataBind();
}


You should be able to plug-in your own values for the connection string and report path. Hope this helps.

[Florian]

have change it - but the same result... it does not show the new record....

 Telerik.Reporting.SqlDataSource sqlDataSource = new Telerik.Reporting.SqlDataSource();
            sqlDataSource.ConnectionString = "Data Source=WIN-DOH63H9EM08;Initial Catalog=Assekuranz;Integrated Security=True";


            sqlDataSource.SelectCommand = "select Vers_Name,Vers_Nation,Vers_Nation1,Vers_Nation2,Vers_Nummer,Vers_Ort,Vers_PLZ,Vers_PolC5,Vers_Police,Vers_EMail,Vers_Reduziert from versicherte where vers_nummer = 200930190";


             System.Xml.XmlReaderSettings settings = new System.Xml.XmlReaderSettings();
            settings.IgnoreWhitespace = true;


            //read the .trdx file contents
            using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create(@"C:\Projekt\M+F\Testreport.trdx", settings))
            {
                Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer =
                    new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();


                //deserialize the .trdx report XML contents
                Telerik.Reporting.Report report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
                report.DataSource = sqlDataSource;
                
                Telerik.Reporting.InstanceReportSource reportSource = new Telerik.Reporting.InstanceReportSource();
                reportSource.ReportDocument = report;


                this.ReportViewer1.ReportSource = new InstanceReportSource { ReportDocument = report };


                //ReportViewer1.ReportSource = reportSource;
                //ReportViewer1.RefreshData();
                ReportViewer1.DataBind();
                //ReportViewer1.RefreshReport();

[Bill]

Hi Florian,

Can you insert breakpoints to debug and step through your code line-by-line? Make sure that the event that triggers the report datasource is firing correctly. If it resides inside an ajax panel you may have to set its postback property to true. Also, while debugging, make sure that your telerik report path is getting set correctly and that your datasource is actually populating with data. Your new report should be an exact copy of your original report. The only thing that is changing is the report's datasource.

[Florian]

Hi Bill,

in my point of view I've exact the same report - because - here:
 using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create(@"C:\Projekt\M+F\Testreport.trdx", settings))
I'm loading the report - which I've created in the report designer.
And at this point:
 Telerik.Reporting.Report report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);

When I change the column-names in my select commands and I enter wrong column-names so I got an error. This says me that my current source code checks the query. 

All columns in the trdx-file are in my select-command (I changed it). Of course - I stepped every line with the debugger...

But I've no way to change the where-clause... whatever I write in the where-part - it will be ignored.
There was any post ....  in which was written that it is necessary to reassign the datasource... I don't know...

[Bill]

Hi Florian,

I know this is a pain for you and I'm trying to think of everything possible to help. :)

Just out of curiosity, can you create a sql view, such as: SELECT [Vers_Name], [Vers_Nation], [Vers_Nation1], [Vers_Nation2], [Vers_Nummer], [Vers_Ort], [Vers_PLZ], [Vers_PolC5], [Vers_Police], [Vers_EMail], [Vers_Reduziert] FROM [dbo].[versicherte_view] WHERE [vers_nummer] = 200930190

Save that view in your db. Make sure your application database user has permission to Read the view. Then try to change the code line for the datasource as such:

sqlDataSource.SelectCommand = "SELECT * FROM [versicherte_view]";

I'll try to create a test report and application to see if I'm missing something here.

[Florian]

Hi Bill,

first of all thank you for your support :-)
Meanwhile I found out that I can create reports in Visual Studio. But when I want to change the data source at runtime - I need the 
NeedDataSource-Event....

But my problem is - that I don't see this event in my Visual Studio when I'am in my report.cs file..

Do you have any Idea how I get this event fired?

[Bill]

I don't think the NeedDataSource event is available through Visual Studio intellisense, so you have to manually add it:

private void Report_NeedDataSource(object sender, System.EventArgs e)

{

    //Take the Telerik.Reporting.Processing.Report instance

    Telerik.Reporting.Processing.Report report = (Telerik.Reporting.Processing.Report)sender;

  

    // Set the SqlDataSource component as it's DataSource

    report.DataSource = this.sqlDataSource1;

}


<
You can find a great deal of information on Telerik's Reporting Documentation site: http://www.telerik.com/help/reporting/overview.html

[KS]

Hi all,

And this should do the work - Changing the connection string dynamically according to runtime data.

-KS

[Javier]

Hi Bill,

 I was trying the same without luck

 

This is my code 

 

Dim settings = New System.Xml.XmlReaderSettings()
        settings.IgnoreWhitespace = True

        Using xmlReader As System.Xml.XmlReader = System.Xml.XmlReader.Create(Server.MapPath(Page.ResolveUrl("~") & "Reports/") & "AdR.trdx", settings)
            Dim xmlSerializer = New Telerik.Reporting.XmlSerialization.ReportXmlSerializer()

            Dim report = DirectCast(xmlSerializer.Deserialize(xmlReader), Telerik.Reporting.Report)
            Dim sqlDataSource As Telerik.Reporting.SqlDataSource = New Telerik.Reporting.SqlDataSource()
            sqlDataSource.ConnectionString = ""
            sqlDataSource.SelectCommand = ""

            report.DataSource = sqlDataSource


            Dim reportSource As New Telerik.Reporting.InstanceReportSource
            reportSource.ReportDocument = report
            ReportViewer1.ReportSource = reportSource
            ReportViewer1.Resources.ProcessingReportMessage = "Processing Ad report..."

            ReportViewer1.DataBind()

            ReportViewer1.RefreshReport()
        End Using

 

Do I need to do something else?

[Bill]

Javier,

 Are you actually populating these values with anything?:

sqlDataSource.ConnectionString = ""
sqlDataSource.SelectCommand = ""

[Javier]

Thanks for this post KS,

 

that saves my day.

 

Regards