Passing parameters from Crystal report to a table valued function

[Former Member]

I am writing a complex report which the source data is coming from a table valued function (Tried it using a stored procedure also).  I have to pass 2 parameters say, Item and batch.  I did add a command in crystal report and my command is " Select * from fn_Report_Trial({?Item},{?batch}) ". When I try to execute the report it is giving me error "Failed to retrieve data from data base.  Source: Microsoft OLE DB Provider for SQL Server.  SQL State:42S22. Native error 207 "  But if I directly pass parameters then ("Select * from fn_Report_Trial('D45D','1-RET456') " it runs fine.  Any help to resolve this issue will be helpful.  Also,  I would like to populate the parameters with values (for items, select item from Item master and for batch select BatchNo from Batch where Item = 'the parameter item')...

[abhilash_kumar · Accepted solution]

Hi Anil,

Let's take it one issue at a time. In fact, I would suggest creating a separate discussion for the Dynamic Cascading part of the question as the solution would not be straightforward and it also because it doesn't confuse others.

Did you try enclosing the prompt names in quotes? Like so:

Select * from fn_Report_Trial('{?Item}','{?batch}')

I've seen issues where the database driver and connection type also plays a role in parsing a SQL statement. Since it's a SQL Server database, make sure you're using the SQL Server Native Client 10.0 (SQLNCLI 10.0) driver to connect to the database.

-Abhilash

[Former Member · Accepted solution]

Thank you Abhilash.  The second part,  I would like to display the Batch Parameter based on selection from the Item parameter.   Say, select BatchNo from Batch where Item = 'the item Parameter')

[abhilash_kumar · Accepted solution]

Has the 1st issue been resolved?

If yes, please create a separate discussion for the 2nd one.

-Abhilash