create a parameter that references a formula in crystal 11

dexterhome
dexterhome used Ask the Experts™
on

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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Sr. Business Intelligence Consultant/Architect
Commented:
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
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/Architect

Commented:
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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
rhinok:

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

Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
Sounds like you created a number parameter, not a string parameter.

~Kurt

Author

Commented:
deffo a string parameter - see screen shot
par-string.jpg
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
How did you use it?

mlmcc
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
Please paste your current record selection - did you accidentally swap the % and ? fields?

~Kurt

Author

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
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

Author

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

Author

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

 James
Kurt ReinhardtSr. Business Intelligence Consultant/Architect

Commented:
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
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial