Crystall_ReportQuery

I use static parameters in the same way you do, with views ect in add command.
and use the wildcard *, and also 'Starts With' in rtecord selection to append to the {?parameter}

So you can type in a word, or any thing begining with 'T"

Why do you need dynamic parameters?

I don't think it is possible.  Can you add a value to the VW_STOCK_USAGE table that could be used as the ALL value?

mlmcc

With Static parameters every time they add a new stock you have to add it manually to the report unless they type it in. Basically it wont show in the dropdown menu.

I can create a lookup table for stock number and *ALL* to that and base the dynamic field on it. Not sure how the CR record selection formula will be . I guess something like this
if (p_stock_no} = "ALL" do not filter
else
(p_stock_no} = {?p_stock_number}

If you are going to build the table or add ALL to the table givve  it  a value so it collates to the top of the list like _ALL_

mlmcc

That shoud work for dynamic list. What about the wild card search. Cant i have dynamic field and let user enter his own criteria too if he desires like a static list.

Pasted from two different examples - obviously, you'd want your 'All' value in your command to be the same as you use in your record selection criteria.  In my examples above, the values are different, but the concept should be clear.

~Kurt

I am in catch 22 situation.

Dynamic parameter is nice because everytime they add a new part to database it shows up in the list.
With static list i have to refresh the report and publish it again. It can get tedious.

But static parameters are more fleixible in that they allow user to use wild card searches. based on above it does nto seem dynamic parameter support this.

Am i correct here?

Any ideas on the best thing to do. If the dynamic list is like USA States then it does not change and static would be no problem.

As stated above, Dynamic parameters are based on whatever values are in the data source the  parameter queries. So, if you want an explicit value that stands for "All" then you have to add that record to the data source.  That's what the sample command does.

~Kurt

What types of wild cards are they using?

How are you running the reports?
If it is a home-grown application you could add your own parameter screen and get the ability to do what you want.

mlmcc

yes, i can add separate data source with *All* option for dynamic parameter. But i was confirming that if  I use a dynamic parameter I wont be able to search by "TX01*" to get all parts that start with TX01. right?

This is oracle pl/sql web HTML application . The report is defined in Crystal. If i use an HTML for to capture parameters then I have to append it to the Crystal report link. It can be done but it requries redesign and some work. I am using Crystal parameter form now.

If you use a dynamic parameter then your user CANNOT enter custom field values. So, unless you want to customize your data source to have explicit values that equal "TX01*", then no.  If your actual data value is "TX012345565" then that's what would normally show up in the Dynamic parameter list.

Then again, if you're going to let your users do a wildcard search against values, why do you want a dynamic parameter anyway?

~Kurt

I am trying to give them both options. Right now, I have a static list that shows all stock numbers with another field below for any value they key in. Whenever a new stock number is added it does not show in the list unless i add it manually to the report which is tedious.

is there a standard or best practice for choosing what type of parameter to use?

What versioin of Crystal do you have?
Obviously it is either CR XI or CR2008

If it is CR2008 you have the ability to use OPTIONAL parameters.
You could add a dynamic and static to do the same thing and make them optional.  That way the user could enter the criteria using either one

The select criteria becomes
(
If HasValue({?DynamicParam}) then
   {MyField} = {?DynamicParam}
Else
    TRUE
)
AND
If HasValue({?StaticParam}) then
   {MyField} LIKE {?StaticParam}
Else
    TRUE
)

With that they can only enter one or the other.  It might be possible to handle the using of both.

mlmcc

A far as a standard, there really isn't an official one that I know about.

If I were to use the Crystal prompting window I would consider how difficult it is to enter specific values or to even just remember them.  Given your scenario where they seem to be long strings like TX012345565 and there are probably many that start with TX01... I would use dynamic if the user generally selected a few individual ones.  If the user tended to choose TX01*, XT03* then I might use static since it allows them to do what you are trying to do.

Where I have been involved in the project beyond just the report wrinting, I generally provided my own parameter screen thus I had full control over what was presented.  I had "dynamic" selection lists long before Crystal added the capability.  It does come with a price in that the application is now a bit more complex.

I have been on several projects where by definition all parameters were static because the application developers didn't want to deal with dynamic parameters and handled the parameters in the application.

mlmcc

I use CR XI Developer on the client. The server is Business objects XI.
I did not see a place to make the parameter optional unless I missed it.

On static, i see an option "Allow custom values" but on dynamic It does not show up. I see "Allow range values" though>

There are no native optional parameters in your version of Crystal Reports.  You can only make them optional in the type of example I provided with my sample reports.

~Kurt

Excellent!