| Author |
Message |
sajesh
Newbie
Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
|
   Topic: Passing a Crystal report parameter to stored proc.
Posted: 25 Jan 2008 at 1:22pm |
Hi,
I am really new to crystal report. I am trying to do a report which accepts one parameter and use that parameter in a stored procedure in CR. I am not able to find exact syntax for executing SP. Please help me ? It will be great if you can provide some example .orn suggest any other way to get records depending on the parameter passed through webform to CR.
Thanks in advance
|
 IP Logged |
|
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 25 Jan 2008 at 1:47pm |
|
You can select a stored procedure just as you would any other database table. However, most people don't see it listed b/c that option is turned off on the computer. Go to File > Options and click the Database tab. Then check the box for Stored Procedures. Now it should show up in your list.
If your stored procedure has an input paramter, Crystal Reports will automatically create a corresponding parameter for you in the report and give it the same name as what is in the stored procedure.
Lastly, since you are using CR.NET 2003, you need to prepopulate the parameter with a value from the webform before calling the report. This is a bit tricky b/c different things can go wrong. This is MUCH easier with .NET 2005 though.
Are you coding in C# or VB.NET?
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
sajesh
Newbie
Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
|
Posted: 28 Jan 2008 at 6:21am |
Thanks a lot.....
I am using c#. I am trying to work on your sugestion. I will be back with result.
Thanks a lot.... for the help
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 28 Jan 2008 at 9:34am |
Ok. Also, I have a complete chapter that talks about all this and gives complete code samples in my book Crystal Reports .Net Programming
Edited by BrianBischof - 28 Jan 2008 at 9:34am
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
sajesh
Newbie
Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
|
Posted: 28 Jan 2008 at 1:57pm |
Thanks .... I got it. Actually the SPs are not showing in VS. So i used the CR10 environment.some how I made it.THanks a lot.
Appreciated
|
|
IP Logged |
|
BrianBischof
Admin Group
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
|
Posted: 28 Jan 2008 at 3:01pm |
|
Cool. If you don't see the stored procedures, you can turn them on by going to Crystal Reports, Design > Default Settings > Database tab. Check the box for stored procedures.
|
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
|
|
IP Logged |
|
sajesh
Newbie
Joined: 25 Jan 2008
Location: Canada
Online Status: Offline
Posts: 8
|
Posted: 29 Jan 2008 at 10:20am |
HI, I have one more trouble in formula. After executing SP I get 4 diffrent tables.How can I check if one coloumn values is null then next tables diffrent coloum value so on.
thanks
|
|
IP Logged |
|
Hyvong
Newbie
Joined: 29 Jan 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 19 Feb 2008 at 12:52pm |
Hi there,
I'm using VS 2005 with SQL Server 2005.
Have any one ever run into this issue, i passed the parameters into the reportDocument, and i know that the report is receiving it, but somehow the report doesn't pass these parameter values into its stored procedure. Is there some sort of setting or anything we have to do to pass these values to the report's stored procedure's required parameters?
Are there a particular order i have to do when comes to passing the database connecting or passing parameter?
Note: All of my parameter names are unique.
I would appreciate if someone can help me with this issue.
This is the error i received:
Failed to open a rowset. Details: ADO Error Code: 0x Source: Microsoft OLE DB Provider for SQL Server Description: Procedure or Function 'ReportStandardPartSummary' expects parameter '@p_ProfileKey', which was not supplied. ....
This is how i passed my parameter value:
' Declare the parameter related objects.
Dim crParameterDiscreteValue As ParameterDiscreteValue
Dim crParameterFieldDefinitions As ParameterFieldDefinitions
Dim crParameterFieldLocation As ParameterFieldDefinition
Dim crParameterValues As ParameterValues
' Get the report's parameters collection.
crParameterFieldDefinitions = reportDocument.DataDefinition.ParameterFields
' - Set the first parameter
' - Get the parameter, tell it to use the current values vs default value.
' - Tell it the parameter contains 1 discrete value vs multiple values.
' - Set the parameter's value.
' - Add it and apply it.
' - Repeat these statements for each parameter. (my report param)
crParameterFieldLocation = crParameterFieldDefinitions.Item( "CompanyCode")
crParameterValues = crParameterFieldLocation.CurrentValues
crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
crParameterDiscreteValue.Value = "ABC"
crParameterValues.Add(crParameterDiscreteValue)
crParameterFieldLocation.ApplyCurrentValues(crParameterValues)
'Stored proc param
crParameterFieldLocation = crParameterFieldDefinitions.Item( "@p_ProfileKey")
crParameterValues = crParameterFieldLocation.CurrentValues
crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue
crParameterDiscreteValue.Value = ProfileKey.ToString
crParameterValues.Add(crParameterDiscreteValue)
crParameterFieldLocation.ApplyCurrentValues(crParameterValues)
|
|
Love your love ones like there is no tomorrow.
|
|
IP Logged |
|
Hyvong
Newbie
Joined: 29 Jan 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 19 Feb 2008 at 1:33pm |
I even tried to pass my parameters this way, and still receive the error.
reportFileName = "StandardPartSummaryReport.rpt" 'Set the Report File Name based on the ReportEnum
reportDocument.Load(reportFilePath + reportFileName) 'Load the Report file
Dim tableLogonInfo As New TableLogOnInfo
tableLogonInfo.ConnectionInfo = myConnectionInfo
'Set logon info for the main report
For Each reporTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.Tables
reporTable.ApplyLogOnInfo(tableLogonInfo)
Next
'Set logon info for any sub report in the main report
For Each subReportDocument As ReportDocument In reportDocument.Subreports
For Each subReportTable As CrystalDecisions.CrystalReports.Engine.Table In subReportDocument.Database.Tables
Try
subReportTable.ApplyLogOnInfo(tableLogonInfo)
Catch ex As Exception
End Try
Next
Next
(instead of the code from prev post, this is what i have)
reportDocument.SetParameterValue("CompanyCode", "USF")
reportDocument.SetParameterValue( "@p_ProfileKey", ProfileKey.ToString)
myCrystalReportViewer.ReportSource = reportDocument ' Set the Crytal Report Viewer control's source to the report document.
myCrystalReportViewer.DataBind()
|
|
Love your love ones like there is no tomorrow.
|
|
IP Logged |
|
Hyvong
Newbie
Joined: 29 Jan 2008
Location: Canada
Online Status: Offline
Posts: 7
|
Posted: 20 Feb 2008 at 5:27am |
After some further testing, i think the parameter begins with @ are the one that causes issues. (I think). These @ parameters are my stored proc's parameter, however, they have a different datatype (hmm...)
Any help is greatly appreciated.
Any one??
Just wondering, is it because my stored proc's parameter has a type of GUID...
I noticed that in the report file, it treats these @ parameters as string datatype.
Edited by Hyvong - 20 Feb 2008 at 2:05pm
|
|
Love your love ones like there is no tomorrow.
|
|
IP Logged |
|
|
|
