In sql server 2005, I created the following stored procedure:
CREATE PROCEDURE
sp_GetReport
@classid
int,
@period int
AS
BEGIN
SET NOCOUNT
ON;
SELECT
Roster.Student, Scores.Score,
Assignments.AssignmentName, Assignments.Category,
Categories.CategoryName
FROM Roster JOIN Scores
ON Roster.sID=Scores.sID
JOIN Assignments
ON Scores.AssignmentID=Assignments.AssignmentID
JOIN Categories
ON Categories.Category=Assignments.Category
WHERE Roster.ClassID=@classid
AND Assignments.Period=@period
ORDER BY Roster.Student
ASC
END
This is the code I created to create the dataset in vs2005 for webform1:
Dim conn
As New SqlConnection
conn.ConnectionString =
"server=servername; user=username; pwd=password; database=databasename;"
Dim cmd
As New SqlCommand
Dim da As
New SqlDataAdapter
Dim ds As
New DataSet
Dim objReport
As New ReportDocument
If conn.State = ConnectionState.Closed
Then
After much trial and error, here is a simple solution for connecting a stored procedure to a crystal report, passing values to a stored procedure for a crystal report and logging into a database through the report viewer. The code is in vb.net, but it can
easily be converted to C#.
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.
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.
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.
Code Or Die!
Marked as answer by Juwar on Aug 16, 2007 12:47 AM
I am new to stored crystall reports.I am using same code as mentioned above but I get following error
The types of the parameter field and parameter field current values are not compatible.
Here is a sample code that I am using
Imports
CrystalDecisions.CrystalReports.Engine
Imports
CrystalDecisions.Shared
Imports
System.Data
Imports
System.Data.SqlClient
Partial Class _Default
Inherits System.Web.UI.Page
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 tenId
As String = 106
tenId =
"@TennantId"
Dim dates
As String =
Date.FromOADate(1 / 11 / 2007)
dates =
"@dateStart"
Dim datese
As String =
Date.FromOADate(12 / 12 / 2007)
datese =
"@dateEnd"
Dim myConnectionInfo
As ConnectionInfo =
New ConnectionInfo()
myConnectionInfo.ServerName =
"Server"
myConnectionInfo.DatabaseName =
"ab"
myConnectionInfo.UserID =
"sa"
myConnectionInfo.Password =
""
Dim ReportPath
As String = Server.MapPath("report1.rpt")
CrystalReportViewer1.ReportSource = ReportPath
Dim field1 As ParameterField =
Me.CrystalReportViewer1.ParameterFieldInfo(0)
Dim field2
As ParameterField = Me.CrystalReportViewer1.ParameterFieldInfo(1)
Dim field3 As ParameterField =
Me.CrystalReportViewer1.ParameterFieldInfo(2)
Dim val1 As
New ParameterDiscreteValue()
Dim val2 As
New ParameterDiscreteValue()
Dim val3 As
New ParameterDiscreteValue()
val1.Value =
"@dateStart"
val2.Value =
"@dateEnd"
val3.Value =
"@TennantId"
field1.CurrentValues.Add(val1)
field2.CurrentValues.Add(val2)
field3.CurrentValues.Add(val3)
SetDBLogonForReport(myConnectionInfo)
End If
End Sub
Private
Sub SetDBLogonForReport(ByVal myConnectionInfo
As ConnectionInfo)
Dim myTableLogOnInfos
As TableLogOnInfos = CrystalReportViewer1.LogOnInfo()
For Each myTableLogOnInfo
As TableLogOnInfo
In myTableLogOnInfos
Hi, You have mentioned not to provide any parameter values when adding the stored procedure to the report, But I am not able to do that. The wizard won't go past the parameter screen unless I set a value for the interger datatype parameter. This is causing
me problem later on when I try to pass the value from my VB.Net code.
Any ideas on how I add the stored procedure without setting defaul parameters?
I am pretty new to .Net and I have a Q. I am attempting to connect a Stored Proc to a Report, in ASP.Net
I have connection string in web.config and when i attempt to connect to the database and retrieve the data into dataset i am getting error, it says that Crystal Reports is attempting to log into the database.
Do you have any examples of ASP.Net and connecting Stored Proc to a web page?
IN your code you use the ConnectionInfo class, what should i use instead of this
Dim
myConnectionInfo As ConnectionInfo =
New ConnectionInfo()
You are awesome to post your helpful solution to using a stored procedure as a datasource for a Crystal Report Viewer comtrol. It worked fabulously! Thank you! [:D]
Juwar
Member
99 Points
131 Posts
Connecting a stored procedure to a crystal report
Aug 15, 2007 10:41 AM|LINK
In sql server 2005, I created the following stored procedure:
CREATE PROCEDURE sp_GetReport@classid
int, @period intAS
BEGIN
SET NOCOUNT ON;SELECT
Roster.Student, Scores.Score, Assignments.AssignmentName, Assignments.Category, Categories.CategoryName FROM Roster JOIN Scores ON Roster.sID=Scores.sID JOIN Assignments ON Scores.AssignmentID=Assignments.AssignmentID JOIN Categories ON Categories.Category=Assignments.Category WHERE Roster.ClassID=@classid AND Assignments.Period=@period ORDER BY Roster.Student ASCEND
This is the code I created to create the dataset in vs2005 for webform1:
Dim conn As New SqlConnectionconn.ConnectionString =
"server=servername; user=username; pwd=password; database=databasename;" Dim cmd As New SqlCommand Dim da As New SqlDataAdapter Dim ds As New DataSet Dim objReport As New ReportDocument If conn.State = ConnectionState.Closed Thenconn.Open()
End Ifcmd.Connection = conn
cmd.CommandText =
"sp_GetReport"cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@classid", SqlDbType.Int).Value = value1cmd.Parameters.Add("@period", SqlDbType.Int).Value = value2da.SelectCommand = cmd
da.Fill(ds)
I created a crystal report called "studentprogress.rpt" for my app through vs2005
I then created a webform2 with a crystal report viewer.
How do I bind this dataset from webform1 to the report (webform2), then format the report with groups?
Juwar
Member
99 Points
131 Posts
Re: Connecting a stored procedure to a crystal report
Aug 16, 2007 12:35 AM|LINK
After much trial and error, here is a simple solution for connecting a stored procedure to a crystal report, passing values to a stored procedure for a crystal report and logging into a database through the report viewer. The code is in vb.net, but it can easily be converted to C#.
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.
Code Or Die!
danielleAA
Member
2 Points
1 Post
Re: Connecting a stored procedure to a crystal report
Nov 29, 2007 10:24 PM|LINK
Thank you very very much for the code. This worked Perfect!!!
asgharzi
Member
2 Points
1 Post
Re: Connecting a stored procedure to a crystal report
Mar 13, 2008 12:41 PM|LINK
Hi there
I am new to stored crystall reports.I am using same code as mentioned above but I get following error
The types of the parameter field and parameter field current values are not compatible.
Here is a sample code that I am using
Imports
CrystalDecisions.CrystalReports.EngineImports
CrystalDecisions.SharedImports
System.DataImports
System.Data.SqlClient Partial Class _Default Inherits System.Web.UI.Page Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.InitConfigureReport()
End Sub Private Sub ConfigureReport() If Not Page.IsPostBack Then Dim tenId As String = 106tenId =
"@TennantId" Dim dates As String = Date.FromOADate(1 / 11 / 2007)dates =
"@dateStart" Dim datese As String = Date.FromOADate(12 / 12 / 2007)datese =
"@dateEnd" Dim myConnectionInfo As ConnectionInfo = New ConnectionInfo()myConnectionInfo.ServerName =
"Server"myConnectionInfo.DatabaseName =
"ab"myConnectionInfo.UserID =
"sa"myConnectionInfo.Password =
"" Dim ReportPath As String = Server.MapPath("report1.rpt")CrystalReportViewer1.ReportSource = ReportPath
Dim field1 As ParameterField = Me.CrystalReportViewer1.ParameterFieldInfo(0) Dim field2 As ParameterField = Me.CrystalReportViewer1.ParameterFieldInfo(1) Dim field3 As ParameterField = Me.CrystalReportViewer1.ParameterFieldInfo(2) Dim val1 As New ParameterDiscreteValue() Dim val2 As New ParameterDiscreteValue() Dim val3 As New ParameterDiscreteValue()val1.Value =
"@dateStart"val2.Value =
"@dateEnd"val3.Value =
"@TennantId"field1.CurrentValues.Add(val1)
field2.CurrentValues.Add(val2)
field3.CurrentValues.Add(val3)
SetDBLogonForReport(myConnectionInfo)
End If End Sub Private Sub SetDBLogonForReport(ByVal myConnectionInfo As ConnectionInfo) Dim myTableLogOnInfos As TableLogOnInfos = CrystalReportViewer1.LogOnInfo() For Each myTableLogOnInfo As TableLogOnInfo In myTableLogOnInfosmyTableLogOnInfo.ConnectionInfo = myConnectionInfo
Next End SubEnd
Class__________________
<
CR:CrystalReportViewer ID="CrystalReportViewer1" runat="server" AutoDataBind="True" DisplayGroupTree="False" DisplayToolbar="False" EnableParameterPrompt="False" Height="1052px" ReportSourceID="CrystalReportSource1" Width="685px" /> <CR:CrystalReportSource ID="CrystalReportSource1" runat="server"> <Report FileName="report.rpt"> </Report> </CR:CrystalReportSource>My crystall report variables
@datestart = datetime
@dateEnd = datetime
@TennantId = number
Any suggestion
Crystal Report CrystalDecisions.CrystalReports.Engine.LogOnException Crystal Report ASP.NET crystal Report ASP.NET dataset parameters discrete range bug toolbar CrystalreportViewers115
venu_babu80
Member
638 Points
173 Posts
Re: Connecting a stored procedure to a crystal report
Apr 09, 2008 01:59 PM|LINK
Thank you very much for your post, It helps me a lot ...
I am getting Report, but not able to see Crystal Viewer Buttons.
Hopefully I can google it for this.
Thank you.
Venu B Pavuluri
My Blog Page
unbreakable
Member
2 Points
5 Posts
Re: Connecting a stored procedure to a crystal report
Jul 25, 2008 06:36 AM|LINK
Hi, You have mentioned not to provide any parameter values when adding the stored procedure to the report, But I am not able to do that. The wizard won't go past the parameter screen unless I set a value for the interger datatype parameter. This is causing me problem later on when I try to pass the value from my VB.Net code.
Any ideas on how I add the stored procedure without setting defaul parameters?
mogara03894@...
Member
2 Points
5 Posts
Re: Connecting a stored procedure to a crystal report
Jul 25, 2008 09:19 AM|LINK
Hello Juwar,
I am pretty new to .Net and I have a Q. I am attempting to connect a Stored Proc to a Report, in ASP.Net
I have connection string in web.config and when i attempt to connect to the database and retrieve the data into dataset i am getting error, it says that Crystal Reports is attempting to log into the database.
Do you have any examples of ASP.Net and connecting Stored Proc to a web page?
IN your code you use the ConnectionInfo class, what should i use instead of thisDim
myConnectionInfo As ConnectionInfo = New ConnectionInfo()or is this the correct approach?
Thanks,
Mike.
animannava
Member
39 Points
19 Posts
Re: Connecting a stored procedure to a crystal report
Aug 25, 2008 11:14 AM|LINK
Hi all,
I have an issue with Crystal Reports Stored procedures.
i have an stored procedures which have 5 parameters. and i bind that sp to my report.
and now i want to create another parameter to my reports using parameter fields section in crystal report ......
i can create a parameter like this but i cant browse the data for parameter.
can any one assist me?will be thankful to you
mugsbhagwat
Member
2 Points
1 Post
Re: Connecting a stored procedure to a crystal report
Oct 24, 2008 03:31 PM|LINK
hey am very very grateful to u..
i had been trying to solve the logon problem in crystalreports for the past 2 months..
once again, thanks a LOT!!!! [:)]
Crystal Report
catlover
Member
2 Points
9 Posts
Re: Connecting a stored procedure to a crystal report
May 15, 2009 08:41 PM|LINK
You are awesome to post your helpful solution to using a stored procedure as a datasource for a Crystal Report Viewer comtrol. It worked fabulously! Thank you! [:D]