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

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
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
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

dexterhomeAuthor Commented:
rhinok:

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

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

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

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

~Kurt
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
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
dexterhomeAuthor Commented:
confusion on my part:
That has fixed the issue and all works as required

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

 James
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
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.