What you will need for this example:
Visual Studio 2005, crystal reports, sql server 2005
Crystal Reports will prompt you to supply a username and password before you can see your report in your application. To automatically log in to the database and avoid this, you can do this programmatically. Instead of placing the code in the Load Event, place it in the Init event.
Here is the sample code to bypass the database login prompt:
1)
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
ConfigureReport()
End Sub
2) Set up the ConfigureReport() subroutine:
Private Sub ConfigureReport()
If Not Page.IsPostBack Then
Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
myConnectionInfo.DatabaseName = DatabaseName
myConnectionInfo.UserID = userid
myConnectionInfo.Password = password
Dim ReportPath As String = Server.MapPath("YourReport.rpt")
ReportViewer1.ReportSource = ReportPath
SetDBLogonForReport(myConnectionInfo)
End If
3) Set up the Database Logon subroutine:
Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo)
Dim myTableLogOnInfos As TableLogOnInfos = ReportViewer1.LogOnInfo()
For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
myTableLogOnInfo.ConnectionInfo = myConnectionInfo
Next
End Sub
4) Make sure you have the following Import statements at the top of the code page.
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data
Imports System.Data.SqlClient
Note: the last import statement is if you decide to use a sql connection.
5) Create your parameterized stored procedure in sql server
Sample stored procedure:
CREATE PROCEDURE sp_YourProcedure
@value1 int,
@value2 int
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM Table WHERE column1=@value1 and column2=@value2
END
6) Connect this stored procedure to your crystal report (if you haven't created a report already with its connections)
To do this, go into Visual Studio 2005, go into your project, click website>add new item>choose crystal reports
Name your report and continue through the wizard. In Database Expert, expand create new connection, expand oledb (ado), select SQL Server, enter server info, select database. Click Next. Expand your server node, expand your database node, expand the dbo node, expand stored procedure node, select your stored procedure and place it in the Selected Tables column by clicking the ">" button. Click OK.
Somewhere in the process, they will ask for values for your parameter values, don't provide any, just click OK.
7) When the report comes up in vs2005, go into Field Explorer, expand the Database Fields Node, add whatever columns to your report by drop and drag.
8) Go back into your code page. Add these lines of code to the ConfigureReport() subroutine after the line "ReportViewer1.ReportSource = ReportPath"
Dim field1 As ParameterField = Me.ReportViewer1.ParameterFieldInfo(0)
Dim field2 As ParameterField = Me.ReportViewer1.ParameterFieldInfo(1)
Dim val1 As New ParameterDiscreteValue()
Dim val2 As New ParameterDiscreteValue()
val1.Value = "value1"
val2.Value = "value2"
field1.CurrentValues.Add(val1)
field2.CurrentValues.Add(val2)
Because I have two values, I need two discrete values. Whatever the values of your parameter values, it will be passed to value1 and value2. These values can come from anywhere, a user textbox, combo box, etc. I have used session variables myself, but that has drawbacks.
Here is the complete code:
Imports CrystalDecisions.CrystalReports.Engine
Imports CrystalDecisions.Shared
Imports System.Data
Imports System.Data.SqlClient
Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
ConfigureReport()
End Sub
Private Sub ConfigureReport()
If Not Page.IsPostBack Then
Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()
myConnectionInfo.DatabaseName = DatabaseName
myConnectionInfo.UserID = userid
myConnectionInfo.Password = password
Dim ReportPath As String = Server.MapPath("YourReport.rpt")
ReportViewer1.ReportSource = ReportPath
Dim field1 As ParameterField = Me.ReportViewer1.ParameterFieldInfo(0)
Dim field2 As ParameterField = Me.ReportViewer1.ParameterFieldInfo(1)
Dim val1 As New ParameterDiscreteValue()
Dim val2 As New ParameterDiscreteValue()
val1.Value = "value1"
val2.Value = "value2"
field1.CurrentValues.Add(val1)
field2.CurrentValues.Add(val2)
SetDBLogonForReport(myConnectionInfo)
End If
End Sub
Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo)
Dim myTableLogOnInfos As TableLogOnInfos = ReportViewer1.LogOnInfo()
For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfos
myTableLogOnInfo.ConnectionInfo = myConnectionInfo
Next
End Sub
Run your report and it should show up without prompting for a username, password or parameter values.
I hope this helps someone else who was just as frustrated as I was trying to get info on this. Please feel free to comment on this solution if you like.