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

Multiple SQL Data Sources in a single report

8 Answers 414 Views

General Discussions

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

Soumya asked on 04 Mar 2014, 12:55 PM

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.

8 Answers, 1 is accepted

answered on 12 Mar 2014, 12:27 PM

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?

answered on 17 Mar 2014, 11:51 AM

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.

answered on 23 Mar 2014, 03:56 PM

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.