Using Functions in Reports

[sparky2708]

I am trying to find out how to use functions in reports. I am running my reports off of an MSSQL dbserver. FYI MSSQL has these things called functions which are the same as stored procedures but they have the ability to return a table.
A lot of the queries that I make always involve this one subquery but I need to pass it parameters like year and period. So I was thinking of making this query into a function cause then I can do:

select FuncTable.data, MyTable2.data, ...
from function_name(year, period) FuncTable, table2 MyTable2, ...
where ...

How do I do this through Crystal Reports? How do I do this functionality in Crystal Reports

 

[rosemaryl]

Dear Sparky2708,

Crystal can link to a Stored Procedure but not to a function. However, a stored procedure can call a function if you need to, however I don't think you do.

When you have parameters in a stored procedure (Year and Period) and Crystal Links to the Stored Procedure it will automatically create the parameters in the report.

The result of the stored procedure becomes the "Table" that crystal uses in the report.

When you run the report it will request the year and period for which to return data.

Hope this information is helpful,

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[sparky2708]

rosemaryl,
thank you for the quick reply. I like stored procs but can't figure out a way to reuse them. I am basically looking for a way to reuse queries which is why I was looking at functions since they can be literally treated as separate tables. Any number of functions can be joined in the FROM clause as if they were normal tables. Crystal Reports provides a SEEMINGLY-similar functionality when it comes to stored procs BUT you need to be aware that stored procs CAN'T be linked to other things therefore the queries executed within them are not reusable. Is there any way to make reusable queries? (This is why I was looking at functions) and yes, currently my stored procs do something like:

select *
from function(...) myfunction

and another stored proc that is the derivative of this stored proc would be

select *
from function(...) myfunction inner join function2(...) myfunction2

I guess what I want to know is if there is another way of doing this more elegantly.

 

[rosemaryl]

Hey Sparky2708,

Well Stored Procedures can be linked to things. You can link a stored procedure to a subreport. I do this all the time.

Stored procedures cannot be linked in the Visual Linking Expert to Tables in a database but you can certainly do this with a subreport that links to a field or formula in the main report that uses the stored procedure.

I am not sure what you mean by reusable?

The result of the stored procedure can be different everytime depending on how you set it up and the parameters you feed it?

More info on what you are trying to do...with an example for instance.

ro Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.