create a parameter that references a formula in crystal 11


Hi Guys,

Need a little help with a parameter field.

I have a formula in a report to show if stock is available:
    IF stock.physical - stock.allocated >=1 then 'available' else 'out of stock'

I now wish to create a parameter (that users can select at report start) to prompt if the report is to show either 'available' or 'out of stock' or some permitation of this.

When creating a parameter it only appears to allow you to select DB fields.

What do I need to do to complete my task.

Any help much appriciated
LVL 5
dexterhomeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Personally, I'd take this approach:

1)  Create a SQL Expression field (call it "Stock") with the following code:

"stock"."physical" - "stock"."allocated"

Open in new window


2)  Create a static String parameter
3)  Manually add values "Available" and "Out of Stock"
4)  In your record selection criteria use something like the following

(
({?Stock} = 'Available' and {%Stock} >= 1) or
({?Stock} = 'Out of Stock' and {%Stock < 1})
)

Open in new window


This does a few things:

1)  It allows you to manually enter values for a parameter
2)  It ensures the record selection criteria will be passed to the database for processing, which wouldn't happen with an equivalent formula field.

~Kurt
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mlmccCommented:
Another idea is to select with

(
({?Stock} = 'Available' and {stock.physical} > {stock.allocated} ) or
({?Stock} = 'Out of Stock' and {stock.physical} <= {stock.allocated})
)

mlmcc
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
That is a much simpler approach.  Unfortunately, the calculation implicitly creates a runtime formula, which can't be processed on the database.   If the total amount of data is small, then it's probably not a big deal and will work fine.  If the recordset is large or takes a long time to process then I'd do what I can to optimize performance through methods like the one I listed above.

~Kurt
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

dexterhomeAuthor Commented:
rhinok:

I tried as advised but get advised that the statement needs a number where the 'available' is.

0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Sounds like you created a number parameter, not a string parameter.

~Kurt
0
dexterhomeAuthor Commented:
deffo a string parameter - see screen shot
par-string.jpg
0
mlmccCommented:
How did you use it?

mlmcc
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Please paste your current record selection - did you accidentally swap the % and ? fields?

~Kurt
0
dexterhomeAuthor Commented:

I did have the ? and % mixed up.
I have now corrected this typo, but still appear to get an error.
see picture
recordset.jpg
0
James0628Commented:
You're using {?stock} in the formula, and you don't have a parameter with that name.  You have {?Stock avalability}.  Is that the parameter?


 FWIW, I think mlmcc's suggestion should also be fine.  I just tried something similar here and, according to Database > "Show SQL Query", the test _was_ being passed to the server.  I don't think it's really any different than having {field} = {?parameter}, where CR gets the parameter value, puts it in the query and passes that to the server.  In this case, CR gets the parameter value and includes test A (physical > allocated) or test B (physical <= allocated) in the query, depending on the parameter value.

 James
0
dexterhomeAuthor Commented:
confusion on my part:
That has fixed the issue and all works as required

0
dexterhomeAuthor Commented:
Thanks for that guys
0
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Apologies to mlmcc. After reading Jame's post, I went back and tested his solution and it did indeed pass to the database as long as the actual formula field wasn't referenced in the selection criteria.  I'd hate to accept points for a solution if a simpler approach works as well.  I recommend opening the question back up and either accepting mlmcc's answer or splitting points, whichever the OP prefers.

~Kurt
0
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.