CR in 8.8 u2013 Stored procedure parameter names

[Former Member]

Hi

I have several reports which are based on stored procedures which require parameters as defined in the SP.

Running externally and in the SBO Crystal Integration addon for 2007 I have been able to define the parameter in the report to be dynamic to get selections.

For example. The SP has a parameter @periodCode. Crystal inherits this as the name of a parameter which may not be changed. Keeping the name I have defined it as a dynamic parameter accessing the OFPR table

Under 8.8, these parameters would be well suited to the standard Token parameter for periods.

Is there any way in 8.8 of mapping a token style parameter to the fixed name parameter that Crystal inherits from a stored procedure.

I have tried changing the name of the parameter in the SP to match a typical token name but it didnu2019t like that at all. Too many commas and @ signs etc.

Regards

Rob

[Former Member · Accepted solution]

Hi Rob,

Are you using Crystal Reports 2008? I have no problems changing parameter names in Crystal regardless of them coming from stored procedures. I just click on the parameter name and press F2 to rename (or you can right click and select Rename).

So using your example of @periodCode inherited from the stored procedure, just rename to

periodCode@ SELECT Code FROM OFPR ORDER BY Code

and this will give you a drop down list of period values on the parameter screen within SAP.

Regards,

Andrew.

[Former Member · Accepted solution]

Hi Andrew

This works well provided that you let the SP parameters come through into the report first and then rename them. I have found when doing a Verify Database sometimes the renamed parameters reset back to their original names from the SP. Not sure why.

I thought I tried this before. Perhaps it was a patch level fix

Thanks

Rob

[Former Member · Accepted solution]

Andrew

Your the man!!! I tried it and it is working like a charm. I cant tell you how many problems this simple solution solve for me.

Something I might add that could help other users If they want a dynamic list with a All option that would return all instances of the data source the parameter is linked to

My return is a list of item codes. My parameter name in Crystal is:

Param01@ select '!All'[ItemCode],'All Items'[ItemName] UNION ALL select ItemCode,ItemName from OITM

(This return a Item code and description when clciking the dropdown in Business One)

If the user then select !All you controll it with a IF in Crystal Filter or your SQL stored prosedure depending on your "setup"

Regards

Burger

[Former Member · Accepted solution]

Hi Rob,

That sounds like a strange issue alright, haven't come across it myself.

Hi Burger,

Glad I could help. Nice tip about about the All values. As you say depending on the design of the report and/or underlying SQL you have a few options for dealing with "All" type values.

Andrew.

[Former Member]

Hi Rob

I am trying to do the same. Have you found a solution

Regards

Burger

[Former Member]

Hi Burger

Not yet

Rob