Reporting with runtime db connection change

[Al]

Hi,

I want end-users to design their own reports to run in an application where the database connection is dynamic. This was sort-of covered in a previous post but now that I understand things better I wanted to ask the right questions. The options I have got are:

1. Use sql connector in the designer with a 'shared' connection string (where the alias is the same name as stored in web.config). This works great as the user can do their own joins using the query builder, slap the reports into the website and they work. BUT there is no connection string in my web.config, it is stored elsewhere.

2. Extend the standalone designer so the users can use an Object Datasource to use my custom dll BUT then all the joins need to be pre-defined in the dll code. (I have already got this working, but joining all the tables is a real problem + confusing for the uesr)

 

So what I want to ask - is there any way I can use case 1 by switching the connection string at run time (I have all the info available in memory, it's just not static in web.config)?

 

[Stef]

Hello Al,

By default, SqlDataSource components read their connection strings from the application's configuration file via ConfigurationManager.

If you want to make changes, you can get a Telerik.Reporting.Report instance and update all DataSource properties of data items in it. Please consider the example in Changing the connection string dynamically according to runtime data.
In the case of the HTML5 Viewer and Reporting REST service, such code can be executed in a custom resolver for the Reporting REST service. An example is available in this forum post.


Let us know if you have further questions.

Regards,
Stef
Telerik

[Al]

Thanks Stef,

That sounds like it's just what I need. Quite a lot to digest though, I'll report back once I've run some tests.

[Al]

Ok, It seems I have this working after combining/reworking some of the concepts at the links provided by Stef.

 - ReportConnectionStringManager class comes from here as-is

 - The standard reports controller now uses a custom resolver instead of the default one

- The custom resolver returns a report instance with the db connection switched

 

@Stef: please confirm that my approach is correct and all aspects of the db connection will be updated (ie. all report items/parameters etc.)

using System;
using System.IO;
using System.Web;
using Telerik.Reporting;
using Telerik.Reporting.Cache.File;
using Telerik.Reporting.Services;
using Telerik.Reporting.Services.Engine;
using Telerik.Reporting.Services.WebApi;
using Telerik.Reporting.XmlSerialization;

namespace Scratch.Controllers
{

    //The class name determines the service URL.
    //ReportsController class name defines /api/report/ service URL.
    public class ReportsController : ReportsControllerBase
    {
        static ReportServiceConfiguration configurationInstance;

        static ReportsController()
        {

            //This uses a custom resolver
            configurationInstance = new ReportServiceConfiguration
            {
                HostAppId = "Html5App",
                Storage = new FileStorage(),
                ReportResolver = new MyResolver(),
                // ReportSharingTimeout = 0,
                // ClientSessionTimeout = 15,
            };

        }

        public ReportsController()
        {
            //Initialize the service configuration
            this.ReportServiceConfiguration = configurationInstance;
        }
    }


    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 report)
        {
            ReportSource reportInstance = null;

            //retrieve an instance of the report
            var connectionString = "Data Source=XXXXXXXX;Initial Catalog=XXXXXXXXXXX;User ID=XXXXXXXX;Password=XXXXXXXXXXX;";
            ReportConnectionStringManager csm = new ReportConnectionStringManager(connectionString);

            var appPath = HttpContext.Current.Server.MapPath("~/");
            var reportsPath = Path.Combine(appPath, "Reports");
            var uri = Path.Combine(reportsPath, report);

            var sourceReportSource = new UriReportSource() { Uri = uri };

            reportInstance = csm.UpdateReportSource(sourceReportSource);

            return reportInstance;
        }
    }



    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);
            }


            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;
                    }
                }
            }
        }
    }

}

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="ReportViewerForm1.aspx.cs" Inherits="Scratch.ReportViewerForm1" %>

<%@ Register TagPrefix="telerik" Assembly="Telerik.ReportViewer.Html5.WebForms" Namespace="Telerik.ReportViewer.Html5.WebForms" %>

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Telerik HTML5 Web Forms Report Viewer Form</title>
    <script src="http://code.jquery.com/jquery-1.9.1.min.js"></script>

    <style>
        #reportViewer1 {
            position: absolute;
            left: 5px;
            right: 5px;
            top: 5px;
            bottom: 5px;
            overflow: hidden;
            font-family: Verdana, Arial;
        }
    </style>

</head>
<body>
    <form runat="server">
        <telerik:ReportViewer
            ID="reportViewer1"
            Width="1300px"
            Height="900px"
            runat="server">
            <ReportSource IdentifierType="UriReportSource" Identifier="Report1.trdx">
            </ReportSource>
        </telerik:ReportViewer>

    </form>
</body>
</html>

[Stef]

Hi Al,

In general, the code snippet illustrates the suggested approach. The issue you may have are:

1. Where you are deserializing a report by using a UriReportSource.Uri, make sure that the path is routed correctly as it is done for the first UriReportSource;
2. If reports use NavigateToReportAction, the NavigateToReportAction.ReportSource property will be  using an instance of a report. This is not supported for actions and the navigation will not be available in the HTML5 Viewer.
   You can replace the NavigateToReportAction with a NavigatetoUrlAction, where the URL can point to the same page but with different query string. The information from the query string can be used to update the viewer on the page.

Regards,
Stef
Telerik

[Al]

I remember now that when I tried this with .trdp files it did not work - just checking it it a requirement that I use a.trdx file for the report viewer's report source identifier?

[Stef]

Hi Al,

TRDP files are introduced in R2 2016. They can be handled in a similar way as TRDX files - Report Packaging. Thus the code from Changing the connection string dynamically according to runtime data should be extended to cover TRDP files recognition and unpackaging.

Regards,
Stef
Telerik

[Al]

Hi,

Can you pls explain what you mean by "should be extended to cover TRDP files recognition and unpackaging."? I don't see any reference to trdp or trdx in the ReportConnectionStringManager class.

[Stef]

Hello Al,

Please check the UpdateReportSource method and the case handling UriReportSource. In this method you need to check the Uri for the file's extension - TRDX or TRDP, and to provide proper handling (deserializing or unpackaging). There are two methods for deserializing TRDX files or XML - DeserializeReport, but you will need to add your own for unpackaging.

Regards,
Stef
Telerik by Progress

[Al]

Thanks Stef,

Quick question, I use UriReportSource for both tdrp and tdrx, it seems to work fine but is this correct?

ie. I am only editing the DeserializeReport(UriReportSource uriReportSource) overload to cater for tdrx and tdrp

[Stef]

Hello Al,

It is possible to include unpackaging in the DeserializeReport method.

Regards,
Stef
Telerik by Progress

[Al]

Ok thanks. Here is the code I have working in my custom resolver to resolve trdp + trdx, if anyone needs it:

 

Report DeserializeReport(UriReportSource uriReportSource)
{
    Report report;

    if (Path.GetExtension(uriReportSource.Uri).ToLower() == ".trdx")
    {
        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();
            report = (Telerik.Reporting.Report)xmlSerializer.Deserialize(xmlReader);
        }
    }
    else
    {
        //tdrp
        var reportPackager = new ReportPackager();
        using (var sourceStream = System.IO.File.OpenRead(uriReportSource.Uri))
        {
            report = reportPackager.Unpackage(sourceStream);
        }
    }
    return report;
}

[Andrew]

Amazing Example Al -- Totally helped with my issue I was having, you rock!