This is a migrated thread and some comments may be shown as answers.

Reporting with runtime db connection change

12 Answers 1324 Views

General Discussions

This is a migrated thread and some comments may be shown as answers.

Al asked on 06 Jun 2016, 12:55 PM

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)?

12 Answers, 1 is accepted

answered on 07 Jun 2016, 02:39 PM

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

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items

answered on 08 Jun 2016, 11:31 AM

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>

answered on 08 Jun 2016, 03:56 PM

Hi Al,

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

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

answered on 15 Aug 2016, 06:48 AM

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;

}