Change Connection String for Reports designed in the Standalone Designer

8 posts, 0 answers
  1. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 27 Aug 2012 Link to this post


    I would like to run reports designed by the standalone designer from my application.
    So far I have managed to load the report definition (.trdx file) and run it in the app.

    What's the best way to specify the connection string dynamically from the application?
    I did not find a pointer in the docs.
    Changing the ConnectionString attribute on the SqlDataSource Node in the XML Document in memory would probably be an option, but is there a direct Telerik API for that?

    I am confused a bit with the different options to set the Windows Forms Report Viewer ReportSource property:

    If I use XmlReportSource to load a trdx file, I apparently do not get all the functionality of the Report object (DataSource, ReportParameters),

    However, the following code seems to work:

    // ReportDefinitionFile is the path to my .trdx file
     
    var xmlSerializer = new ReportXmlSerializer();
    var reportDocument = (Report)xmlSerializer.Deserialize(ReportDefinitionFile);
      
    var reportSource = new InstanceReportSource();
    reportSource.ReportDocument = reportDocument;
      
    SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();

    .... // initialize connection string
     
     
    SqlDataSource sds = (SqlDataSource)reportDocument.DataSource;
    sds.ConnectionString = scsb.ConnectionString;
     
    this.reportViewer1.ReportSource = reportSource;
    this.reportViewer1.RefreshReport();



    Regards
    Erwin
  2. Steve
    Admin
    Steve avatar
    10941 posts

    Posted 30 Aug 2012 Link to this post

    Hi Erwin,

    In the standalone report designer, when you use SqlDatasource component, you're asked to save the connectionString with a desired name. This name is the name of the connectionString, so basically you can have a connectionString with the same name attribute in your application config file (app.config) and the SqlDatasource component would handle the binding automatically.

    If you prefer to work with CLR types and objects, you can deserialize the xml report definition and proceed from there like you would normally do following the basic concepts of the programming language and the .NET platform.

    Greetings,
    Steve
    the Telerik team

    BLOGGERS WANTED! Write a review about Telerik Reporting or the new Report Designer, post it on your blog and get a complimentary license for Telerik Reporting. We’ll even promote your blog and help bring you a few fresh readers. Yes, it’s that simple. And it’s free. Get started today >

  3. DevCraft banner
  4. Ted
    Ted avatar
    5 posts
    Member since:
    Apr 2012

    Posted 12 Nov 2012 Link to this post

    Huh?  The previous response from Telerik does not answer the question.  

    There is a Telerik.ReportViewer.WinForms.ReportViewer control in a Windows Form app.  The user selects a pregenerated *.trdx report file within the app.  The app binds the file to the ReportViewer control's ReportSource property using a Telerik.Reporting.UriReportSource instance.  

    The questions is: how can the app DYNAMICALLY set the connection string for the report?  The user wants to enter/choose this connection string DYNAMICALLY (i.e., it is not in the app.config file or any other pre-defined config file deployed with the app).  How should this be done using the ReportViewer control?


  5. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 28 Nov 2012 Link to this post

    Interested in this is well. I believe there's many reasons why a connection string wouldn't want to be saved in an app.config file. And for now I see no option to configure this dynamically.
  6. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 28 Nov 2012 Link to this post

    The above trick works for basic reports, but when using multiple datasources in a dashboard like report, I can't seem to find out how to update the connection strings. For example in datasources referenced by crosstabs or charts.
  7. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 28 Nov 2012 Link to this post

    If all your DataSources point to the same Database, you can inspect the Report Object and replace the Datasource Configuration:

    (my App's name is Octopus, so replace that with your Name ...)

    // use the telerik ReportXmlSerializer to create a Report object from XML
                      var xmlSerializer = new ReportXmlSerializer();
                      var reportDocument = (Telerik.Reporting.Report)xmlSerializer.Deserialize(this.ReportDefinitionFile);
     
                      // create a ReportSource object
                      var reportSource = new InstanceReportSource();
                      reportSource.ReportDocument = reportDocument;
     
                      // get the applications connection string (I'm using LLBLGEN here)
                      SqlConnectionStringBuilder scsb = new SqlConnectionStringBuilder();
                      scsb.ConnectionString = CommonDaoBase.ActualConnectionString;
     
                      // ... needed by other parts ot the app
                      this.connectionString = CommonDaoBase.ActualConnectionString;
                      this.databaseDatabase = scsb.InitialCatalog;
                      this.databaseServer = scsb.DataSource;
                      this.databaseLogin = scsb.UserID;
                      this.databasePassword = scsb.Password;
                      this.integratedSecurity = scsb.IntegratedSecurity;
     
                      // fill in well-known Report Parameters if the report defines them
                      // if ReportParameter "OctopusConnectionString" is present, the report uses Binding to set up it's DataSource objects
                      if (!this.SetReportParameters(reportDocument))
                      {
                          // no OctopusConnectionString Parameters, set the ConnectionString on the Report DataSource
                          SqlDataSource sds = (SqlDataSource)reportDocument.DataSource;
                          sds.ConnectionString = scsb.ConnectionString;
     
                          // try to patch DataItems in the report (Table, Crosstab, List, Subreports have their own DataSource objects)
                          this.SetConnectionStrings(reportDocument.Items, scsb.ConnectionString);
                      }

    This is experimental, seems to work for my cases so far

    private void SetConnectionStrings(Telerik.Reporting.ReportItemBase.ItemCollection items, string connectionString)
        {
            foreach (Telerik.Reporting.ReportItemBase ib in items)
            {
                if (ib.Items != null)
                {
                    if (ib.Items.Count > 0)
                    {
                        this.SetConnectionStrings(ib.Items, connectionString);
                    }
                }
     
                SqlDataSource ds = null;
     
                if (ib is Telerik.Reporting.Chart)
                {
                    Telerik.Reporting.Chart chart = ib as Telerik.Reporting.Chart;
                    ds = chart.DataSource as SqlDataSource;
                }
                else if (ib is Telerik.Reporting.Crosstab)
                {
                    Telerik.Reporting.Crosstab crosstab = ib as Telerik.Reporting.Crosstab;
                    ds = crosstab.DataSource as SqlDataSource;
                }
                else if (ib is Telerik.Reporting.Table)
                {
                    Telerik.Reporting.Table table = ib as Telerik.Reporting.Table;
                    ds = table.DataSource as SqlDataSource;
                }
                else if (ib is Telerik.Reporting.List)
                {
                    Telerik.Reporting.List list = ib as Telerik.Reporting.List;
                    ds = list.DataSource as SqlDataSource;
                }
                else if (ib is Telerik.Reporting.SubReport)
                {
                    Telerik.Reporting.SubReport rpt = ib as Telerik.Reporting.SubReport;
                    ds = rpt.Report.DataSource as SqlDataSource;
                }
     
                if (ds != null)
                {
                    ds.ConnectionString = connectionString;
                }
            }
        }


    For advanced reports that bind to multiple datasources (multiple different databases) i have found 2 possible solutions so far:

    1: Use naming conventions for your datasources so that you can find out which datasource configuration to overwrite with which dynamic database connection after you have created the Report object from the .trdx (or use XML Tools to manipulate the .trdx before de-serializing into a Report object).

    2: Use Report Parameters and use Binding within the datasource configuration in the designer. While this is cleaner conceptually,
    it has the problem that you now have to enter the values for the Report Parameters for tests in the designer.

    Regards
    Erwin
  8. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 04 Dec 2012 Link to this post

    Silly thing is there is a 'Datasources' property in the report, but it's 'protected' and not public. Simply changing this would make all our problems go away.

    I tried creating an inherited class that would open the property, but naturally it's not possible to deserialize the report to the inherited class.
  9. Myth
    Myth avatar
    54 posts
    Member since:
    Jan 2010

    Posted 22 May 2013 Link to this post

    I've 'improved' upon Erwin's solution using reflection:

    [code]
    private void SetConnectionString(Report reportDocument, String connectionString)
            {
                SqlDataSource sds = (SqlDataSource)reportDocument.DataSource;

                if (sds != null)
                    sds.ConnectionString = connectionString;

                this.SetConnectionString(reportDocument.Items, connectionString);
            }

            private void SetConnectionString(Telerik.Reporting.ReportItemBase.ItemCollection items, String connectionString)
            {
                foreach (Telerik.Reporting.ReportItemBase ib in items)
                {
                    if (ib.Items != null)
                    {
                        this.SetConnectionString(ib.Items, connectionString);
                    }

                    Type type = ib.GetType();
                    PropertyInfo dataSourceProperty = type.GetProperty("DataSource");
                    if (dataSourceProperty == null)
                        continue;

                    var dataSource = dataSourceProperty.GetValue(ib, null) as SqlDataSource;
                    dataSource.ConnectionString = connectionString;
                }
            }
    [/code]
Back to Top
DevCraft banner