Loading chart in report viewer control and setting data source dynamically

[John North]

We have a report with a chart works fine loading it from the class and setting the data source at run time and load it in the report control works fine. The same report we are serializing(Telerik.Reporting.XmlSerialization.ReportXmlSerializer)

and storing it in the database and deserializing it and then setting the data source at run time but when we load it in the report control it says have no or empty data series. 

[Steve]

Hi John,

The provided information is insufficient for us to determine what might be wrong. How do you provide data to the report and respectively the chart? Since you're storing the serialized report in database, do you display it in the viewer by using XmlReportSource?
Generally when you deserialize the report you end up with a report instance. Thus you can set up the datasource of data items as shown in the following code snippet:

Telerik.Reporting.Report report;
var connectionString = ConfigurationManager.ConnectionStrings["ReportConnectionString"].ConnectionString;


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

using (System.Xml.XmlReader xmlReader = System.Xml.XmlReader.Create("SampleReport1.trdx", settings))
{
    Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer =
        new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();

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

((SqlDataSource)report.DataSource).ConnectionString = connectionString;

var chartDataSource = (SqlDataSource)((Chart)report.Items["chart1"]).DataSource;
chartDataSource.ConnectionString = connectionString;

Kind regards,
Steve
the Telerik team

[Patrick]

Dear Steve,

Thanks for the code example!
I am trying to do the same thing for our project, but our template uses 2 data sources.

I have accomplished to set the connection string for the first data source the way you have shown in your example.
But I can't set the connection string for the second data source. I was not able to get the second data source programmatically.
It looks like the Report instance only supports one data source via the DataSource property. I would expect a DataSources property on the Report with a list of data sources though. Because when I look at the xml contents of the trdx file, I see a DataSources element:

<DataSources>
    <SqlDataSource ConnectionString="..." SelectCommand="SELECT ..." Name="Query1">
    </SqlDataSource>
    <SqlDataSource ConnectionString="..." SelectCommand="SELECT ..." Name="Query2">
    </SqlDataSource>
</DataSources>
<Items>
....

Can you show me how to set the connection string for the second data source?

Thanks in advance,
Patrick Koorevaar

[Patrick]

I solved it by myself (for tables) using the following code:

string connectionString = @"Data Source=datasource\SQLEXPRESS;Initial Catalog=test;User ID=test;Password=12345";

IEnumerable sectionList = report.Items.Where(i => i is Telerik.Reporting.ReportSection);

foreach (Telerik.Reporting.ReportSection section in sectionList)
{
    IEnumerable tableList = section.Items.Where(i => i.GetType() == typeof(Telerik.Reporting.Table));

    foreach (Telerik.Reporting.Table table in tableList)
    {
        Telerik.Reporting.SqlDataSource tableDataSource = table.DataSource as Telerik.Reporting.SqlDataSource;
        if (tableDataSource != null)
        {
            tableDataSource.ConnectionString = connectionString;
        }
    }
}