Build stmt based on parameter selection

[JanS]

Hi,

I have a requirement to change an existing report to use a parameter in the following way:

Add two default values to a parameter -
* (for all records) or
"Head Offices Only" for a predefined set of records

By selecting * the report should return details for all offices or for "Head Offices Only" only details for those offices (say there are 4) should be returned.

Ive created a formula to determine which value is selected and create a string for the sql stmt.

ie * - office_id like '*'
'Head Office Only' - office_id in (101,102,103,104)

Is there a way I can dymanically create the sql stmt like this???? If I create the string I cant seem to get the selection formula to add it to the sql stmt being run.

I could make the user select the office_id from a multi select parameter list but they prefer not to do this as it take too much time and they'd have to remember which offices are the ones that fit this category!

Any help would be most appreciated. Please let me know if further explination is required.

jan

 

[vidru]

I'm a little confused by, 'Is there a way I can dynamically create the sql stmt like this????'

I understand what you're looking for, but not what you're asking. Crystal would generate it's own sql based on the table joins and record selection formula.

If your report is based on tables in a database, you want your record selection formula to look like the following:

if {?Office} <> '*' then
{Table.office_id} in [101,102,103,104]

where {?Office} is the name of your parameter field. If the {?Office} parameter is anything other than '*', then it should return all offices.

Hope this helps you out...

-dave

 

[JanS]

Thanks for your response.

Where should the IF stmt be added? If I open the Select Expert and expand to get to the "Record Selection Formula Editor" it wont accept an IF stmt being added to the existing stmt (there are other parameters and selection criteria). Am I looking in the right place?

many thanks

 

[vidru]

Jan,

If you're in the 'Record Selection Formula' you're in the right place. If you've got other parameters, then the selection formula would look like this:

if {?Office} = '*' then
//your original report parameters here
// e.g. {table.field1} = {?Param1} and {table.field2} = {?Param2}

else
//limit records to Head Offices, as well as use original parameters
{Table.office_id} in [101,102,103,104]
[code][b]and[/b]

//your original report parameters here
[/code]
If you still have problems, post what's in your record selection formula, and we'll try to sort it out.

-dave

 

[JanS]

Thank you so much for all your help. Your last message showed me where I was going wrong - simple formatting error :/( silly me!