Multiple SQL Data Sources in a single report

9 posts, 0 answers
  1. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 04 Mar 2014 Link to this post

    Hi,

    I create a report using standalone report designer. There are a lot of datasources in my report which I add by clicking Data->SQL Data Source. Reports created will be run on reportviewer. While running report, connectionstring for all the datasources in the report should be changed. Following is a piece of my asp.net code. 

    string report_key = Request.QueryString["Report"];
                    if (report_key == null)
                        Response.Redirect("reports.aspx");
                    else
                    {
                        string ConnectionString = newConn;
                        string ConnectionStringRedemption = "";
                        string userName = ParafaitCommon.getUserName();
                        string role = ParafaitCommon.getRole();
                        long userId = ParafaitCommon.getUserId();
                        string fromDate = ParafaitTimezone.getLocalTime(DateTime.Now.AddDays(-1)).ToString("MM/dd/yyyy") + " 06:00:00 AM";
                        string toDate = ParafaitTimezone.getLocalTime(DateTime.Now).ToString("MM/dd/yyyy") + " 06:00:00 AM";
                        
                        Telerik.Reporting.UriReportSource URI = new Telerik.Reporting.UriReportSource();
                        try
                        {
                            if (report_key.Contains("Dynamic Report"))
                                URI.Uri = "Dynamic Report.trdx";
                            else
                                URI.Uri = report_key + ".trdx";

                            XmlReaderSettings settings = new XmlReaderSettings();
                            settings.IgnoreWhitespace = true;
                            using (XmlReader xmlReader = XmlReader.Create(Server.MapPath(URI.Uri), settings))
                            {
                              Telerik.Reporting.XmlSerialization.ReportXmlSerializer xmlSerializer =
                                                    new Telerik.Reporting.XmlSerialization.ReportXmlSerializer();

                              Telerik.Reporting.Report report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
                              (report.DataSource as SqlDataSource).ConnectionString = newConn;
                              rptViewer.Report = report;
                              rptViewer.ReportSource.Parameters.Add("fromdate", fromDate);
                              rptViewer.ReportSource.Parameters.Add("todate", toDate);
                              rptViewer.ReportSource.Parameters.Add("offSet", Convert.ToString(ParafaitTimezone.FinalOffset()));
                              rptViewer.ReportSource.Parameters.Add("user", userName);
                              rptViewer.ReportSource.Parameters.Add("loggedInUserName", userName);
                              rptViewer.ReportSource.Parameters.Add("role", role);
                              rptViewer.ReportSource.Parameters.Add("loggedInUserId", userId.ToString());
                              if (ParafaitCommon.checkIsCorporate())
                                  rptViewer.ReportSource.Parameters.Add("isCorporate", "Y");
                              else
                                  rptViewer.ReportSource.Parameters.Add("isCorporate", "N");
                              rptViewer.RefreshReport();
                            }
                        }
                        catch
                        {

                        }
                    }
                }

    After the statement, Telerik.Reporting.Report report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
    report.Datasource has value null.

    In case I add datasource following report wizard, report.Datasource has value but only for the datasource that I create using the wizard. I need to change connectionstring for all the datasources in the report. How do I add multiple datasources and change connectionstring for all of them. I attach a sample report for reference.
  2. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 07 Mar 2014 Link to this post

    Hi Soumya,

    Check out the Changing the connection string dynamically according to runtime data KB article that elaborates on your case.

    Regards,
    Peter
    Telerik

    DevCraft Q1'14 is here! Watch the online conference to see how this release solves your top-5 .NET challenges. Watch on demand now.

  3. DevCraft banner
  4. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 07 Mar 2014 Link to this post

    Thank you Peter. I got it working. Now I am trying to change connectionstring for all the datasources in drill through report. Could you please help me with this?
  5. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 12 Mar 2014 Link to this post

    Hi Soumya,

    The example in the referenced KB article should handle even drillthrough reports. On our side using the demos, the drillthrough report connection strings are successfully updated. Thus we will appreciate if you elaborate further on the exact difficulties you have experienced using the code in the referenced KB article and the drillthrough configuration.

    Regards,
    Peter
    Telerik
     

    DevCraft Q1'14 is here! Watch the online conference to see how this release solves your top-5 .NET challenges. Watch on demand now.

     
  6. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 12 Mar 2014 Link to this post

    Hi Peter,

    I referred reference that you sent and added class ReportConnectionStringManager. Now I had another issue. From reportviewer page if I pass filename as it is, I get an error 'File C:\...\gamemetric.trdx not found'. Following is the code for the same.

    Telerik.Reporting.UriReportSource URI = new Telerik.Reporting.UriReportSource();
    URI.Uri = report_key + ".trdx";
    var sourceReportSource = URI;
                            var connectionStringHandler = new ReportConnectionStringManager(newConn);
                            var reportSource = connectionStringHandler.UpdateReportSource(sourceReportSource);
                            this.rptViewer.ReportSource = reportSource;
                            rptViewer.ReportSource.Parameters.Add("fromdate", fromDate);
                            rptViewer.ReportSource.Parameters.Add("todate", toDate);
                            rptViewer.ReportSource.Parameters.Add("offSet", Convert.ToString(ParafaitTimezone.FinalOffset()));
                            rptViewer.ReportSource.Parameters.Add("user", userName);
                            rptViewer.ReportSource.Parameters.Add("loggedInUserName", userName);
                            rptViewer.ReportSource.Parameters.Add("role", role);
                            rptViewer.ReportSource.Parameters.Add("loggedInUserId", userId.ToString());
                            if (ParafaitCommon.checkIsCorporate())
                                rptViewer.ReportSource.Parameters.Add("isCorporate", "Y");
                            else
                                rptViewer.ReportSource.Parameters.Add("isCorporate", "N");
                            this.rptViewer.RefreshReport();

    I changed constructor in ReportConnectionStringManager class so that it accepts two parameters. Second parameter is directory where the trdx file is which I got using the following statement. 
    string path = Server.MapPath("~/");
    var connectionStringHandler = new ReportConnectionStringManager(newConn, path);
    Changes that I made to  ReportConnectionStringManager class is as follows

    public class ReportConnectionStringManager
    {
        readonly string connectionString;
        readonly string path;

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

        public ReportSource UpdateReportSource(ReportSource sourceReportSource)
        {
            if (sourceReportSource is UriReportSource)
            {
                 var uriReportSource = (UriReportSource)sourceReportSource;
                    uriReportSource.Uri = path + uriReportSource.Uri;
                    var reportInstance = DeserializeReport(uriReportSource);
                    ValidateReportSource(uriReportSource.Uri);
                    this.SetConnectionString(reportInstance);
                    return CreateInstanceReportSource(reportInstance, uriReportSource);
                    .....

    Now it seems like it is in an infinite loop and report never opened. I don't even get to see report processing notification. It looked like report would open but nothing happened. I got windows error 'A problem caused program to stop working correctly. Windows will close the program and notify you if a solution is available.' 

    I have a report gamemetric.trx and drill through report viewplaydetails.trdx and both are in the same folder. I first thought of getting reportsource's URI by using Server.MapPath("....trdx") but even when I did that it would give error 'File C:\....\viewplaydetails.trdx not found'. 

    Am I doing anything wrong?









  7. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 17 Mar 2014 Link to this post

    Hello Soumya,

    In the provided code files we have noticed that you actually utilize the ConfigurationManager to extract the new connection string:
    String newConn = ConfigurationManager.ConnectionStrings["ParafaitConnectionString"].ConnectionString;
    Thus our recommendation is to use the reporting build-in support for resolving application configuration (web.config) connection strings. Just set the SqlDataSource ConnectionString to the used connection string name as you have already done. do you experience any issues if you don't use the ReportConnectionStringManager?

    In the provided code files we have noticed that you have put part of the code in a try catch block:
    if (sourceReportSource is UriReportSource)
    {
        try
        {
            var uriReportSource = (UriReportSource)sourceReportSource;
            uriReportSource.Uri = path + uriReportSource.Uri;
            var reportInstance = DeserializeReport(uriReportSource);
            ValidateReportSource(uriReportSource.Uri);
            this.SetConnectionString(reportInstance);
            return CreateInstanceReportSource(reportInstance, uriReportSource);
        }
        catch (Exception ex)
        {
        }
    }
    Do you try to suppress any exceptions?

    Up to the error this error may be caused due to the IE add ons issue. Check out the "Internet Explorer has stopped working. A problem caused the program to stop working correctly. Windows will close the program and notify you if a solution is available." MS thread on the topic. Another reason could be just some IE troubles in rendering too big pages. How big is the report you try to render?  Have you tried to run the report in Chrome or Firefox?

    Additionally we have noticed that you set the reportsource parameters through the report viewer, instead our recommendation is to set the parameters directly to the report source as shown in the following code snippet:
    var URI = new Telerik.Reporting.UriReportSource();
    URI.Uri = report_key + ".trdx";
    var sourceReportSource = URI;
    var connectionStringHandler = new ReportConnectionStringManager(newConn);
    var reportSource = connectionStringHandler.UpdateReportSource(sourceReportSource);
     
    reportSource.Parameters.Add("fromdate", fromDate);
    reportSource.Parameters.Add("todate", toDate);
    reportSource.Parameters.Add("offSet", Convert.ToString(ParafaitTimezone.FinalOffset()));
    reportSource.Parameters.Add("user", userName);
    reportSource.Parameters.Add("loggedInUserName", userName);
    reportSource.Parameters.Add("role", role);
    reportSource.Parameters.Add("loggedInUserId", userId.ToString());
    if (ParafaitCommon.checkIsCorporate())
        reportSource.Parameters.Add("isCorporate", "Y");
    else
        reportSource.Parameters.Add("isCorporate", "N");
    this.rptViewer.ReportSource = reportSource;

    Regards,
    Peter
    Telerik
     

    DevCraft Q1'14 is here! Watch the online conference to see how this release solves your top-5 .NET challenges. Watch on demand now.

     
  8. Soumya
    Soumya avatar
    19 posts
    Member since:
    Feb 2014

    Posted 23 Mar 2014 Link to this post

    Hello Peter,

    I made changes that you suggested. I still get error "Could not find file 'C:\Program Files\Common Files\Microsoft Shared\DevServer\10.0\ViewPlayDetails.trdx'." Following is the code behind.

    public partial class reportviewer : System.Web.UI.Page
    {
        String newConn = ConfigurationManager.ConnectionStrings["ParafaitConnectionString"].ConnectionString;
        public ParafaitUtils.Utilities Utils;
        protected void Page_Load(object sender, EventArgs e)
        {
            newConn = ParafaitCommon.changeConnectionString(newConn, ParafaitCommon.getDbName());
            if (!IsPostBack)
            {
                if (!ParafaitCommon.checkValidSession())
                {
                    Response.Redirect("~/login.aspx");
                }
                else
                {
                    //string report_key = ParafaitCommon.getReportKey();
                    string report_key = Request.QueryString["Report"];
                    DateTime fromDate = Convert.ToDateTime(Request.QueryString["fromdate"]);
                    DateTime toDate = Convert.ToDateTime(Request.QueryString["todate"]);
                    string path = Server.MapPath("~/");
                    if (report_key == null)
                        Response.Redirect("reports.aspx");
                    else
                    {
                        string ConnectionString = newConn;
                        string userName = ParafaitCommon.getUserName();
                        string role = ParafaitCommon.getRole();
                        long userId = ParafaitCommon.getUserId();
                        //string fromDate = ParafaitTimezone.getLocalTime(DateTime.Now.AddDays(-1)).ToString("MM/dd/yyyy") + " 06:00:00 AM";
                        //string toDate = ParafaitTimezone.getLocalTime(DateTime.Now).ToString("MM/dd/yyyy") + " 06:00:00 AM";

                        Telerik.Reporting.UriReportSource URI = new Telerik.Reporting.UriReportSource();
                        try
                        {
                            if (report_key.Contains("Dynamic Report"))
                                URI.Uri = "Dynamic Report.trdx";
                            else
                                URI.Uri = report_key + ".trdx";
                            var sourceReportSource = URI;
                            var connectionStringHandler = new ReportConnectionStringManager(newConn);
                            var reportSource = connectionStringHandler.UpdateReportSource(sourceReportSource, path);

                            reportSource.Parameters.Add("fromdate", fromDate);
                            reportSource.Parameters.Add("todate", toDate);
                            reportSource.Parameters.Add("offSet", Convert.ToString(ParafaitTimezone.FinalOffset()));
                            reportSource.Parameters.Add("user", userName);
                            reportSource.Parameters.Add("loggedInUserName", userName);
                            reportSource.Parameters.Add("role", role);
                            reportSource.Parameters.Add("loggedInUserId", userId.ToString());
                            if (ParafaitCommon.checkIsCorporate())
                                reportSource.Parameters.Add("isCorporate", "Y");
                            else
                                reportSource.Parameters.Add("isCorporate", "N");
                            this.rptViewer.ReportSource = reportSource;
                        }
                        catch
                        {

                        }
                    }
                }
            }
        }
    }

    ReportConnectionStringManager class is as follows
    public class ReportConnectionStringManager
    {
        readonly string connectionString;
        readonly string path;

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

        public ReportSource UpdateReportSource(ReportSource sourceReportSource, string path)
        {
            if (sourceReportSource is UriReportSource)
            {
                var uriReportSource = (UriReportSource)sourceReportSource;
                uriReportSource.Uri = path + uriReportSource.Uri;
                var reportInstance = DeserializeReport(uriReportSource);
                ValidateReportSource(uriReportSource.Uri);
                this.SetConnectionString(reportInstance);
                return CreateInstanceReportSource(reportInstance, uriReportSource);
            }

            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, path);
                    drillThroughAction.ReportSource = updatedReportInstance;
                }

                if (item is SubReport)
                {
                    var subReport = (SubReport)item;
                    subReport.ReportSource = this.UpdateReportSource(subReport.ReportSource, path);
                    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;
                    }
                }
            }
        }
    }

    In my previous post I added try catch block to know after which statement there is exception. 






















































































  9. Peter
    Admin
    Peter avatar
    1611 posts

    Posted 26 Mar 2014 Link to this post

    Hi Soumya,

    The error you experience is general one related to the deployment of the ViewPlayDetails.trdx. Generally the error states that the file is not available in the expected location.
    For more information see: Could not find file MSDN thread on the topic.

    If you need additional assistance we will need a runnable sample project to review on our end.

    Regards,
    Peter
    Telerik
     

    Build cross-platform mobile apps using Visual Studio and .NET. Register for the online webinar on 03/27/2014, 11:00AM US ET.. Seats are limited.

     
  10. john
    john avatar
    3 posts
    Member since:
    Mar 2016

    Posted 08 Jun Link to this post

     "Internet Explorer has stopped working" problem with me

     

Back to Top
DevCraft banner