Login Prompt for Crystal Reports

One of the great things about Crystal Reports and Visual Studio 2010 is the ability to embed your reports directly in a web page. This is accomplished by installing the Crystal Reports for Visual Studio add-in. Then simply drag a report viewer control onto your page, point it to an existing Crystal Report (or create your own) and you have a nice clean solution for publishing reports. I was developing a web application with this very same idea in mind when I ran into a snag. When I refreshed the datasource or changed any parameters I was greeted with a login dialog. This is a nice feature for just me, but I didn’t want to share the database password with the end users, let alone rely on them to remember it.

So I went out searching for a solution in which I could pass the login credentials to the datasource in code and effectively suppress the login prompt in the process. The code snippet below does just that. First make sure you import the following libraries at the top of your code behind page:

Imports CrystalDecisions.Shared
Imports CrystalDecisions.CrystalReports.Engine

UPDATE: Report won’t page properly when loaded from Page_Load.

Then paste this in your Page_Load Page_Init event handler:

Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
Dim myReport As New ReportDocument()
myReport.Load(Server.MapPath("ReportName")) ' name of the crystal report

Dim myTables As Tables = myReport.Database.Tables

For Each myTable As CrystalDecisions.CrystalReports.Engine.Table In myTables
Dim myTableLogonInfo As TableLogOnInfo = myTable.LogOnInfo
myConnectionInfo.ServerName = "" ' <SQL servername>
myConnectionInfo.DatabaseName = "" ' leave database name blank
myConnectionInfo.UserID = "" ' username
myConnectionInfo.Password = "" ' password
myTableLogonInfo.ConnectionInfo = myConnectionInfo
myTable.ApplyLogOnInfo(myTableLogonInfo)
Next

CrystalReportViewer1.ReportSource = myReport

When the page loads, it creates a new report object using the report name and path that you specify. Then it grabs all of the tables used in the Crystal Report. The code loops through these tables and passes the necessary credentials to the database. Finally, it stores that connection information in the report object. If you don’t feel like exposing your username and password in code, you can also store them in the connection strings section of your web.config file. A better explanation of this code can be found at the MSDN website.

[facebook_ilike]