How to stop crystal report viewer from asking login credentials when opening subreport
I currently have a crystal report embedded in a visual studio website. I deploy the website and install it on an IIS, and provide a link to the user so they can access globally and see this report. This system has been working great.
However, when I embedded a crystal report that has a sub report, the credentials are not being passed to the sub-report automatically. When i debug the solution, the initial report opens fine, when I click the item to open the sub-report, crystal report viewer asks me for database login credentials.
How can i pass those credentials automatically in the code so that users dont have to enter the code when viewing in the crystal report viewer.
Below is the code i use in my default.aspx.cs page. It contains the connection strings.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
namespace CFIBInventory
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
SqlConnection con = new SqlConnection("Data Source=HPL-WTS;Initial Catalog=Enterprise32;Persist Security Info=True;User ID=sa;Password=********");
DataSet1 ds = new DataSet1();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT dbo.Material.MaterialCode, dbo.Material.CategoryCode, dbo.Material.Description, dbo.MaterialOnHand.LocationCode, dbo.Material.ValuationMethod, dbo.MaterialOnHand.Quantity FROM dbo.Material INNER JOIN dbo.MaterialOnHand ON dbo.Material.MaterialCode = dbo.MaterialOnHand.MaterialCode WHERE (dbo.Material.CategoryCode = 'CFIB3') AND (dbo.Material.ValuationMethod = 1) AND (dbo.Material.InactiveFlag = 0)", con);
adapter.Fill(ds.cfibInventory);
CrystalReport1 report = new CrystalReport1();
report.SetDataSource(ds);
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
}
}
}
The crystal report viewer is embedded into my .aspx page via the code below:
<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true"
CodeBehind="Default.aspx.cs" Inherits="CFIBInventory._Default" %>
<%@ Register assembly="CrystalDecisions.Web, Version=13.0.2000.0, Culture=neutral, PublicKeyToken=692fbea5521e1304" namespace="CrystalDecisions.Web" tagprefix="CR" %>
<asp:Content ID="HeaderContent" runat="server" ContentPlaceHolderID="HeadContent">
<meta http-equiv="X-UA-Compatible" content="IE=9"/>
</asp:Content>
<asp:Content ID="BodyContent" runat="server" ContentPlaceHolderID="MainContent">
<p>
</p>
<CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server"
AutoDataBind="true" />
</asp:Content>
The query for the sub report is in the sub-reports' database expert.
By the way, the database is connecting to a windows server 2005 machine. I do not believe it has public access setup, such as its own IIS. The previous reports, that do not have subreports, are installed on a newer 2012 Server as thats where the database is. Not sure if the machine has anything to do with the additional login prompt in crystal report viewer.
Any help will be great. Thank you in advance!
EDIT: After implementing solution from Nimesh
Well i added the connected connection type however, visual studio is underlining 'report' saying: Cannot use local variable 'report' before it is declared.
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in report.Database.Tables)
and
foreach (ReportDocument subreport in report.Subreports)
I noticed i declare report later below as new crystalreport1. So I move that declaration above Nimesh code block and the red underline for 'report' goes away, but then all 6 instances of 'crtableLogoninfo' get underlined in red with the error: The name 'crtableLogoninfo' does not exist in the current context'
Any further help will be greatly appreciated.
Below is how my code looks now:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
namespace CFIBInventory
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//Nimesh code
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crConnectionInfo.ServerName = "HPL-WTS";
crConnectionInfo.DatabaseName = "Enterprise32";
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "*********";
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in report.Database.Tables)
{
crTableLogoninfo = CrTable.LogOnInfo;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crtableLogoninfo);
}
foreach (ReportDocument subreport in report.Subreports)
{
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in subreport.Database.Tables)
{
crtableLogoninfo = CrTable.LogOnInfo;
crtableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crtableLogoninfo);
}
} // nimesh code end
//Old connection string, i assume this shouldnt be here since nimesh code is for connecting
//SqlConnection con = new SqlConnection("Data Source=HPL-WTS;Initial Catalog=Enterprise32;Persist Security Info=True;User ID=sa;Password=123qwerTy987");
DataSet1 ds = new DataSet1();
SqlDataAdapter adapter = new SqlDataAdapter("SELECT dbo.Material.MaterialCode, dbo.Material.CategoryCode, dbo.Material.Description, dbo.MaterialOnHand.LocationCode, dbo.Material.ValuationMethod, dbo.MaterialOnHand.Quantity FROM dbo.Material INNER JOIN dbo.MaterialOnHand ON dbo.Material.MaterialCode = dbo.MaterialOnHand.MaterialCode WHERE (dbo.Material.CategoryCode = 'CFIB3') AND (dbo.Material.ValuationMethod = 1) AND (dbo.Material.InactiveFlag = 0)", con);
adapter.Fill(ds.cfibInventory);
CrystalReport1 report = new CrystalReport1();
// OLD CODE
//report.SetDataSource(ds);
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
}
}
}
Answer
The problem is in Crystal Report login info. before showing the report you have to set login info to all tables which are included in main report and their sub reports. You are using Disconnected Datasource Report Show method. so you don't need to provide login info to report document. your problem is here.
report.SetDataSource(ds);
when you are using SetDataSource method it is necessary to provide all tables which are included in crystal report document. here, you have passed only a single table in dataset. you must pass all tables including subreport tables.
I am suggesting you if you are using subreport then use connected datasource method instead if disconnected datasource(report.SetDataSource()). in Connected datasource you have to set login info before showing the report.
private void PrintReport()
{
ReportDocument report = new ReportDocument();
report.Load("ReportPath");
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crConnectionInfo.ServerName = "HPL-WTS";
crConnectionInfo.DatabaseName = "Enterprise32";
crConnectionInfo.UserID = "sa";
crConnectionInfo.Password = "*********";
TableLogOnInfo crTableLogoninfo = new TableLogOnInfo();
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in report.Database.Tables)
{
crTableLogoninfo = CrTable.LogOnInfo;
crTableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crTableLogoninfo);
}
foreach (ReportDocument subreport in report.Subreports)
{
foreach (CrystalDecisions.CrystalReports.Engine.Table CrTable in subreport.Database.Tables)
{
crTableLogoninfo = CrTable.LogOnInfo;
crTableLogoninfo.ConnectionInfo = crConnectionInfo;
CrTable.ApplyLogOnInfo(crTableLogoninfo);
}
}
CrystalReportViewer1.ReportSource = report;
CrystalReportViewer1.ToolPanelView = CrystalDecisions.Web.ToolPanelViewType.None;
}
if you want to use disconnected datasource even you have not included subreport then you can use a single command instead of multiple tables. that will make easy and gives better performance. becuase you just need to pass only a single table into SetDataSource method. but, dont forget to set tablename in DataTable otherwise report will not be displayed.