The Question:
I am attempting in my Web Application to use a Crystal Reports
report. In the designer I am able to connect to my database, but when I try
to view report in the Web browser, I get "Logon Failed" error message. Why would
the DB logon work in the designer, but fail at runtime?
The Answer:
Crystal Reports for Visual Studio.NET is a great tool for creating
professional-looking reports in the .NET platform. This tool allows you to host
your reports in a Windows Application, in a Web Application, and to publish
reports like Web Services. It has a rich programming model. It was built using
the framework of Crystal Reports 8.0, but has been modified for the .NET platform
to give us the richest API possible and has open and flexible architecture to
allow you to share reports on the Web.
Using Crystal Reports for Visual Studio .NET, you can create very complex reports.
The Crystal Report Designer allows you to create and format the report you need
without coding. The Report Engine processes all the formatting, grouping, and
charting criteria that you specify when you design the report.
If you use Crystal Report Experts, you can choose from report layout options
ranging from standard reports to form letters, or you can build your own report
from scratch. You can display charts that users can drill down on to view detailed
report data, calculate summaries and subtotals, format text or rotate text
objects, and so on.
Let's see how you can create a report using Crystal Reports
and publish it on a Web page. We are going to use Pubs database from SQL Server
for data source of our report.
When you create a report, you specify the data source of the report, design
the report layout, and finally you have to decide how you want users to access
the report data. Now, it's time to try to use Crystal Reports for Visual Studio.NET.
First, start Visual Studio.NET and create ASP.NET Application project. In
the Solution Window, right click on project name and select "Add New Item" option.
On the AddNewItem window in the templates pane, look for the icon that represent Crystal Reports.
Click on it, and click on Open button.
In the Crystal Reports gallery dialog click on the OK button
to move to the Standard Report Expert window. In this window expand "OLE DB
(ADO)" option, which will open the OLE DB (ADO) window. Select "Microsoft OLD
DB Provider for SQL Server" from list of available providers, because we are
going to use MS SQL Server and Pubs database that is installed by default when
you first install your SQL Server, and click the "NEXT" button.
On next connection window, enter information about your SQL Server:
- Server: localhost
- User ID: sa (in your case maybe something else)
- Password: test (in your case maybe something else)
- Database: pubs
and click on the "FINISH" button.
From the Pubs connection, select three tables: Authors, Titles,
and TitleAuthor. Click on the "NEXT" button, and skip the link part because
Crystal Reports will correct link tables by clicking on "NEXT" button. From
Available Fields, from Title table select Title and Price fields, and from Authors
table select au_fname and au_lname fields. At this point just click on the "FINISH"
button. Crystal Reports will generate the report. Note that in this case we
were using a "Pull" model to access data. Crystal Reports can use two different
models to access data "Pull" and "Push" models. In the "Pull" model the
database driver will connect to the database and will pull data. In the "Push"
model, the developer must write code to connect to the database, to create recordset
and to pass that object to the report.
In the next picture you can see the report that Crystal Reports created for
us. Save the report and go back to the Web Form.
To include the report on the Web page, we need to drag and
drop the Crystal Report Viewer control from the Toolbox. Because the Crystal
Report Viewer control doesn't have a ReportSource property available at design
time, you have to set that property inside your code.
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class WebForm1
Inherits
System.Web.UI.Page
Protected WithEvents
CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
#Region " Windows Form
Designer generated code "
Private Sub
Page_Load(ByVal sender As
System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
CrystalReportViewer1.ReportSource = Server.MapPath("crystalreport1.rpt")
End Sub
End Class.
|
or you can create instance of the report class which was created by Visual Studio.NET when you
designed your report. The name of that file is same as report that you create CrystalReport1.vb.
To see this file expand CrystalReport1.rpt (click on + sign in front of the CrystalReport1.rpt).
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class WebForm1
Inherits
System.Web.UI.Page
Protected WithEvents
CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
#Region
" Windows Form Designer generated code "
Dim
crpt As CrystalReport1
Private Sub
Page_Load(ByVal sender As
System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
crpt = New
CrystalReport1()
CrystalReportViewer1.ReportSource =
crpt
End Sub
End Class.
|
If you set WebForm1.aspx to be the Start Up page for the project,
and start the project, you will get the report page if you have blank password
in database for "sa". If "sa" or any other user name that you want to use to
access database has password, when you run the report, you will see the "LogonFailed"
error. If we were developing windows application, Crystal Reports will ask us
for password information. This error occured, because when you design and save
report, all of the connection information is saved within the report except
password. If the password is blank, there will be no problem to create or generate
the report.
To prevent this, you will need to provide login information
in your code before you set ReportSource property. To do so, you will add some
code in Page_Load event. Now, your code should look like:
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Public Class WebForm1
Inherits
System.Web.UI.Page
Protected WithEvents
CrystalReportViewer1 As CrystalDecisions.Web.CrystalReportViewer
#Region
" Windows Form Designer generated code "
Dim
crpt As CrystalReport1
Dim
myTable As CrystalDecisions.CrystalReports.Engine.Table
Dim
myLogin As CrystalDecisions.Shared.TableLogOnInfo
Private Sub
Page_Load(ByVal sender As
System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
crpt = New
CrystalReport1()
For
Each myTable In crpt.Database.Tables
myLogin = myTable.LogOnInfo
myLogin.ConnectionInfo.Password
= "test"
myLogin.ConnectionInfo.UserID
= "sa"
myTable.ApplyLogOnInfo(myLogin)
Next
CrystalReportViewer1.ReportSource
= crpt
End Sub
End Class.
|
Now, if you run the report, you will not get an error page
and evrything will look OK. The report will be generated and shown in your Web
page.
Summary
-
Now we can say that we know the answer to the question above.
Logon failed, because in the designer Crystal Reports maintains connection
information, but when you save the report and later on try to use same report,
an error will be thrown because of missing password information. This is
logical, because nobody wants password information to be saved within reports,
and this required password information to be submited to the Crystal Reports
engine before you try to open the report.
-
Crystal Reports is a very powerful tool and can help you
create professional-looking reports. It will allow you to even publish your
report as a Web Service.
-
When you are accessing the database, you will need to provide
the connection information for each table in your report. ConnectionInfo
object has several properties that you can setup at runtime before you setting
ReportSource property of the Crystal Report Viewer object. You can set a
new database name, new user, new password, or even new server that is different
that one that was setup when the report was designed. This will give you
a lot of flexibility.
In my next articles, I will try to show you how to host a report
in a Windows Applications, how to host a report in a Web Application (caching,
how to incorporate reports as untyped report components in Web and Windows projects
alike, how to incorporate reports as strongly-typed report components in Web
projects, etc.), publish a report as a Web Service, how to export a report in
other formats (like HTML, PDF, RTF, Excel, or Word), how to optimize Report
Performance, how Crystal Enterprise can help extend the scalability and the
speed of Web-based reports.