Passing a Crystal report parameter to stored proc.
Printed From: Crystal Reports Book
Category: Crystal Reports .NET 2003
Forum Name: Data Connectivity
Forum Discription: How to connect to data sources and export reports
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2143
Printed Date: 04 May 2024 at 1:19am
Topic: Passing a Crystal report parameter to stored proc.
Posted By: sajesh
Subject: Passing a Crystal report parameter to stored proc.
Date 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
|
Replies:
Posted By: BrianBischof
Date 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> |
Posted By: sajesh
Date 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
|
Posted By: BrianBischof
Date 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 http://www.amazon.com/exec/obidos/ASIN/0974953652/bischofsystem-20 - Crystal Reports .Net Programming
------------- 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> |
Posted By: sajesh
Date 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
|
Posted By: BrianBischof
Date 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> |
Posted By: sajesh
Date 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
|
Posted By: Hyvong
Date 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.ParameterDiscreteValuecrParameterDiscreteValue.Value = "ABC"crParameterValues.Add(crParameterDiscreteValue) crParameterFieldLocation.ApplyCurrentValues(crParameterValues) 'Stored proc param crParameterFieldLocation = crParameterFieldDefinitions.Item( "@p_ProfileKey")crParameterValues = crParameterFieldLocation.CurrentValues crParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValuecrParameterDiscreteValue.Value = ProfileKey.ToString crParameterValues.Add(crParameterDiscreteValue) crParameterFieldLocation.ApplyCurrentValues(crParameterValues) ------------- Love your love ones like there is no tomorrow. |
Posted By: Hyvong
Date 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 ReportEnumreportDocument.Load(reportFilePath + reportFileName) 'Load the Report file Dim tableLogonInfo As New TableLogOnInfotableLogonInfo.ConnectionInfo = myConnectionInfo 'Set logon info for the main report For Each reporTable As CrystalDecisions.CrystalReports.Engine.Table In reportDocument.Database.TablesreporTable.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 TrysubReportTable.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. |
Posted By: Hyvong
Date 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. ------------- Love your love ones like there is no tomorrow. |
Posted By: Hyvong
Date Posted: 21 Feb 2008 at 6:39am
|
I went and changed all of my stored proc datatype to nvarchar instead of GUID and allow NULL.
I was able to see the data when i clicked on "Main Report Preview".
But when i run the report from my web app, I received this error.
One or more fields could not be found in the result set. Use Verify Database to update the report. Error in File C:\DOCUME~1\HyVong\LOCALS~1\Temp\4\StandardPartSummaryReport {6F44DA3D-7290-49DC-B5E2-7F734A4D6C8F}.rpt: The rowset column could not be found.
But i verified the database, and i tried to use only 1 field from the report and still receiving this error. Does anyone knows why? is it because the value i passed into the report are not coming through and therefore my report couldn't retrieve any record from the database?
If i don't select any field to display on my report, then the report works, but as soon as i select a field from the 'Database field' i get this error.
I am new when it comes to hooking up CR with stored proc, perhaps somebody out there can help me. I have been struggle through this for days.
Thanks in advance! ------------- Love your love ones like there is no tomorrow. |
Posted By: Hyvong
Date Posted: 02 Mar 2008 at 4:00pm
|
Based on the code i posted from above; I find that i can pass the report's stored proc parameter successfully when i didn't have the database logon info. Once i set the connection to my database, all of the report stored proc parameter values are not being passed through. Does any one know if this is a Crystal REport bug, and whether there is a hot fix i should apply?
Your help is greatly appreciated.
Thanks, ------------- Love your love ones like there is no tomorrow. |
Posted By: Hyvong
Date Posted: 03 Mar 2008 at 11:25am
|
After weeks of frustration, I found out that the issue posted above is from Crystal Report 10 bug.
I applied the hot fixed and it works for me but i have read some people claimed that it didn't work them. However, here is the patch since it might be helpful to some of you.
http://support.businessobjects.com/library/kbase/articles/c2018840.asp - http://support.businessobjects.com/library/kbase/articles/c2018840.asp
Direct link to patch http://ftp1.businessobjects.com/outgoing/CHF/crnet20win32x86_en_chf.zip - http://ftp1.businessobjects.com/outgoing/CHF/crnet20win32x86_en_chf.zip Hope it helps!------------- Love your love ones like there is no tomorrow. |
Posted By: rickysch
Date Posted: 25 Mar 2008 at 12:07pm
|
Thank you man! This patch fixed a similar issue for me! Ricky |
Posted By: Hyvong
Date Posted: 28 Mar 2008 at 1:05pm
|
Glad i could help.
I totally understand the frustration when it comes to bug like this.
Cheers! ------------- Love your love ones like there is no tomorrow. |
Posted By: usman009
Date Posted: 26 Mar 2009 at 3:18am
|
Hi I am having the same issue has any one got solution for it.
Thanks in advance.
|