1255823 - How to call SQL Server function which returns table in Crystal Report?

SAP Knowledge Base Article - Public

1255823 - How to call SQL Server function which returns table in Crystal Report?

Symptom

How to call SQL Server function which returns table in Crystal Report?

Reproducing the Issue

  • Crystal Reports XI Release 2
  • Operating System:- Windows server 2003.
  • Database:- SQL SERVER (Any)

Cause

Crystal Report can not call a function which is created in the database.

Resolution

  • Create a procedure in SQL Server that calls this function with a parameter which is being passed to function.
  • Pass the procedure parameter to the function.
  • Create a report in Crystal with a subreport.
  • Create one formula in main report which yields the data of the data type same as the procedure parameter.
  • For ex if procedure parameter is varchar(string) datatype then the formula should return a string value.
  • Use the procedure in the subreport(this creates the parameters of procedures in Crystal Report by default)
    In the main report link this subreport using formula and the subreport's parameter.

See Also

SQL SERVER user guide.

Keywords

SQL function crystal report , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

SAP Crystal Reports XI R2