Change ConnectionString in runtime

[Aleksandar]

Hi.

I need to change connection string in my reports dynamicly, sinse different users might have different databases.

I saw this article http://www.telerik.com/support/kb/reporting/details/changing-the-connection-string-dynamically-according-to-runtime-data and everything worked great, but now we have a report with sub report section. In SubReport section we have a path that depends on a value selected in the main report:

ReportSource: ="./Content/reportDefinitions/"+Replace(Parameters.ReportType.Value, " ", "_")+".trdp"

Problem is that solution suggested in the article above fails. It tries to desirialize path wich is "./Content/reportDefinitions/"+Replace(Parameters.ReportType.Value, " ", "_")+".trdp" and of cause can't find it.

 

I also tried to hardcode subreport name and than use Unpackage method.

var report = reportPackager.Unpackage(sourceStream);

But if I do so, I get another error. Both reports Main one and sub report depend on the same parameter ReportId, and some how subreport claims that this parameter was not passed, main section works fine.

[Stef]

Hello Aleksandar,

I can see other forum thread of yours, related to Kendo UI. I am guessing you use the HTML5 Viewer.

If my guess is correct, you need a custom resolver for the Reporting REST service like this one. You can add the string you will use for ReportType parameter in the string description sent by the viewer to the resolver. Then use the value to create a valid file path to get the sub report's file.

Regards,
Stef
Telerik by Progress

[Aleksandar]

Hi Stef.

You are right. It's HTML5 Viewer. And I aready have a custom report resolver: 

class CustomReportResolver : Telerik.Reporting.Services.Engine.IReportResolver
    {

        public IUserProvider UserProvider
        {
            // do not use dependency property because of IIS caching
            get
            {
                return UnityConfig.ConfiguredContainer.Resolve<IUserProvider>();
            }
        }

        public Telerik.Reporting.ReportSource Resolve(string reportId)
        {
            try
            {
                Report report = null;
                var reportPath = string.Empty;

                if (!string.IsNullOrEmpty(ConfigurationManager.AppSettings["TelerikReportFolder"]))
                {
                    reportPath = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["TelerikReportFolder"].ToString() + reportId + ".trdp");
                }
                else
                {
                    reportPath = System.Web.HttpContext.Current.Server.MapPath("~/Content/reportDefinitions/" + reportId + ".trdp");
                }

                var reportPackager = new ReportPackager();
                using (var sourceStream = System.IO.File.OpenRead(reportPath))
                {
                    report = reportPackager.Unpackage(sourceStream);
                }

                var instanceReportSource = new Telerik.Reporting.InstanceReportSource();
                instanceReportSource.ReportDocument = report;

                var currentClient = UserProvider.GetCurrentUser().Client;
                var reportConnectionManager = new ReportConnectionStringManager(currentClient.DatabaseConnection);
                reportConnectionManager.UpdateReportSource(instanceReportSource);


                return instanceReportSource;

            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
    }

 

Btw. my reports are *.trdp files.

There is main report that has a subrport section in it.

And this subreport is also a *.trdp file.

 

I also use code suggested at this forum how to update connection strings. Problem is thatsubreport it's uri, and then we end up at code 

if (sourceReportSource is UriReportSource)
        {
            var uriReportSource = (UriReportSource)sourceReportSource;
            var reportInstance = DeserializeReport(uriReportSource);
            ValidateReportSource(uriReportSource.Uri);
            this.SetConnectionString(reportInstance);
            return CreateInstanceReportSource(reportInstance, uriReportSource);
        }

 

Problem is it's not an XmlReport. I found in the other thread, that I can use report packager. So this is how I've adjusted ReportConnectionStringManager

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Telerik.Reporting;

namespace ColumbusUI.Controllers.Reporting
{
  public class ReportConnectionStringManager
  {
    readonly string connectionString;

    public ReportConnectionStringManager(string connectionString)
    {
      this.connectionString = connectionString;
    }

    public ReportSource UpdateReportSource(ReportSource sourceReportSource)
    {

      if (sourceReportSource is UriReportSource)
      {
        //var uriReportSource = (UriReportSource)sourceReportSource;
        //var reportInstance = DeserializeReport(uriReportSource);
        //ValidateReportSource(uriReportSource.Uri);
        //this.SetConnectionString(reportInstance);
        //return CreateInstanceReportSource(reportInstance, uriReportSource);

          var uri =
              @"C:\_TFS\Columbus\Columbus SaaS\trunk\source\Columbus\Frontends\ColumbusUI\Content\reportDefinitions\Nodes_Inbound_KPIs.trdp";

                var reportPackager = new ReportPackager();
                using (var sourceStream = System.IO.File.OpenRead(uri))
                {
                    var report = reportPackager.Unpackage(sourceStream);

                    var source = new InstanceReportSource();
                    source.ReportDocument = report;

                    this.SetConnectionString(report);

                    return source;
                }
      }

      if (sourceReportSource is XmlReportSource)
      {
        var xml = (XmlReportSource)sourceReportSource;
        ValidateReportSource(xml.Xml);
        var reportInstance = this.DeserializeReport(xml);
        this.SetConnectionString(reportInstance);
        return CreateInstanceReportSource(reportInstance, xml);
      }

      if (sourceReportSource is InstanceReportSource)
      {
        var instanceReportSource = (InstanceReportSource)sourceReportSource;
        this.SetConnectionString((ReportItemBase)instanceReportSource.ReportDocument);
        return instanceReportSource;
      }

      if (sourceReportSource is TypeReportSource)
      {
        var typeReportSource = (TypeReportSource)sourceReportSource;
        var typeName = typeReportSource.TypeName;
        ValidateReportSource(typeName);
        var reportType = Type.GetType(typeName);
        var reportInstance = (Report)Activator.CreateInstance(reportType);
        this.SetConnectionString((ReportItemBase)reportInstance);
        return CreateInstanceReportSource(reportInstance, typeReportSource);
      }

      throw new NotImplementedException("Handler for the used ReportSource type is not implemented.");
    }

    ReportSource CreateInstanceReportSource(IReportDocument report, ReportSource originalReportSource)
    {
      var instanceReportSource = new InstanceReportSource { ReportDocument = report };
      instanceReportSource.Parameters.AddRange(originalReportSource.Parameters);
      return instanceReportSource;
    }

    void ValidateReportSource(string value)
    {
      if (value.Trim().StartsWith("="))
      {
        throw new InvalidOperationException("Expressions for ReportSource are not supported when changing the connection string dynamically");
      }
    }


    Report DeserializeReport(UriReportSource uriReportSource)
    {
      var settings = new System.Xml.XmlReaderSettings();
      settings.IgnoreWhitespace = true;
      using (var xmlReader = System.Xml.XmlReader.Create(uriReportSource.Uri, settings))
      {
        var xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();
        var report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
        return report;
      }
    }

    Report DeserializeReport(XmlReportSource xmlReportSource)
    {
      var settings = new System.Xml.XmlReaderSettings();
      settings.IgnoreWhitespace = true;
      var textReader = new System.IO.StringReader(xmlReportSource.Xml);
      using (var xmlReader = System.Xml.XmlReader.Create(textReader, settings))
      {
        var xmlSerializer = new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();
        var report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
        return report;
      }
    }

    void SetConnectionString(ReportItemBase reportItemBase)
    {
      if (reportItemBase.Items.Count < 1)
        return;

      if (reportItemBase is Report)
      {
        var report = (Report)reportItemBase;

        if (report.DataSource is SqlDataSource)
        {
          var sqlDataSource = (SqlDataSource)report.DataSource;
          sqlDataSource.ConnectionString = connectionString;
        }
        foreach (var parameter in report.ReportParameters)
        {
          if (parameter.AvailableValues.DataSource is SqlDataSource)
          {
            var sqlDataSource = (SqlDataSource)parameter.AvailableValues.DataSource;
            sqlDataSource.ConnectionString = connectionString;
          }
        }
      }

      foreach (var item in reportItemBase.Items)
      {
        //recursively set the connection string to the items from the Items collection
        SetConnectionString(item);

        //set the drillthrough report connection strings
        var drillThroughAction = item.Action as NavigateToReportAction;
        if (null != drillThroughAction)
        {
          var updatedReportInstance = this.UpdateReportSource(drillThroughAction.ReportSource);
          drillThroughAction.ReportSource = updatedReportInstance;
        }

        if (item is SubReport)
        {
          var subReport = (SubReport)item;
          subReport.ReportSource = this.UpdateReportSource(subReport.ReportSource);
          continue;
        }

        //Covers all data items(Crosstab, Table, List, Graph, Map and Chart)
        if (item is DataItem)
        {
          var dataItem = (DataItem)item;
          if (dataItem.DataSource is SqlDataSource)
          {
            var sqlDataSource = (SqlDataSource)dataItem.DataSource;
            sqlDataSource.ConnectionString = connectionString;
            continue;
          }
        }

      }
    }
  }
}

I've hardocded uri because it comes as ="./Content/reportDefinitions/"+Replace(Parameters.ReportType.Value, " ", "_")+".trdp" 

 

But now I get another problem that ReportID (one of the report parameters) is not defined.

If I don't call code that updates connection strings it works fine:

var reportConnectionManager = new ReportConnectionStringManager(currentClient.DatabaseConnection);
reportConnectionManager.UpdateReportSource(instanceReportSource);

 

So I assume that code which updates connections strings does something wrong. Code of ReportConnectionStringManager is a bit  above.

 

Thanks

[Stef]

Hi Alexander,

Please check teh CreateInstanceReportSource method in the KB article.

ReportSource CreateInstanceReportSource(IReportDocument report, ReportSource originalReportSource)
  {
    var instanceReportSource = new InstanceReportSource { ReportDocument = report };
    instanceReportSource.Parameters.AddRange(originalReportSource.Parameters);
    return instanceReportSource;
  }

Note that we copy the original ReportSource instance's Parameters collection to the new ReportSource instance. The ReportSource.Parameters collection contains the mappings of the sub report's ReportParameters to the main report's values.

You need to add the same logic in your custom handling of UriReportSource.

Regards,
Stef
Telerik by Progress

[Aleksandar]

Hello Stef,

thanks for your reply. I managed to pass paramters from the original report source. The only thing I don't know how to fix is that subreport is a dynamic value as I mention in the first place: "./Content/reportDefinitions/"+Replace(Parameters.ReportType.Value, " ", "_")+".trdp" 

 

I can't find how can I access Parameters.ReportType.Value otherwise I won't be able to choose correct sub report in my custom report resolver.

[Aleksandar]

Hi Stef,

thanks for your reply. I managed to pass report parameters. The only thing I don't know how to fix is subreport source.

As I mentioned in the very begining, my subreport dependes on the main report parameter:

ReportSource: ="./Content/reportDefinitions/"+Replace(Parameters.ReportType.Value, " ", "_")+".trdp"

Unfortunately I cannot find where to get this value when I create a report in custom report resolver. Is it possible at all to access report parameters in report resolver? When no , how can I achieve such behaviour?

 

Thanks.

[Stef]

Hi Aleksandar,

The idea is to send custom information from the viewer, that includes a string that can replace Parameters.ReportType.Value in the UriReportSource.Uri string.

For example:

• Viewer
  

  <script type="text/javascript">
          $(document).ready(function () {
              $("#reportViewer1")
                  .telerik_ReportViewer({
                      serviceUrl: "api/reports/",
                      templateUrl: 'ReportViewer/templates/telerikReportViewerTemplate-9.1.15.731.html',
                      //ReportSource - report description
                      reportSource: {
                          report: "SampleReport.trdx-filepath10",
                          // Parameters name value dictionary
                          parameters: { }
                      },
                      viewMode: telerikReportViewer.ViewModes.INTERACTIVE,
                  });
          });

• Service
  
  static ReportsController()

        {

            configurationInstance = new ReportServiceConfiguration

            {

                HostAppId = "Html5DemoApp",

                Storage = new FileStorage(),

                ReportResolver = new MyResolver(),//resolver,

            };

        }

           

  //the custom resolver

      public class MyResolver : IReportResolver

      {

          //this method will be called on each request for report (refresh, navigate to report, sub report, parameters updates)

         //the method will be called 3 times on initial load

         public Telerik.Reporting.ReportSource Resolve(string reportDescription)

          {        

             var receivedValues = reportDescription.Split('-');

   

             var report=receivedValues[0];

              Report reportInstance =null;

             if(report.Contains("SampleReport"))

             {

             //retrieve an instance of the report     

              reportInstance = DeserializeReport(report);     

             //change the report's DataSource settings

              (reportInstance.DataSource as Telerik.Reporting.SqlDataSource).ConnectionString = "new string here";      

             //change a nested data item's DataSource

              ((reportInstance.Items.Find("table1",true)[0] as Telerik.Reporting.Table).DataSource =GetStoredOnTheServerData();

            

               //sub report - how to get the real path and the report file

              var subReportURS=(reportInstance.Items.Find("subReport1",true)[0] as Telerik.Reporting.SubReport).ReportSource

                                                                                                                                                                                     as UriReportSource;

             var getFolderbyKey = GetFolder(receivedValues[1]);

              var actualSubReportFilePath subReportURS.Uri.Replace("Parameters.ReportType.Value",getFolderbyKey);
/* further processing of the sub report */


             }



              return new InstanceReportSource { ReportDocument = reportInstance};

          }

      }

Note that once you process the sub report, you will have an InstanceReportSource, and the previously used expression (UriReportSource.Uri) will be no longer used.

Regards,
Stef
Telerik by Progress