Crystal Reports 2013, Oracle, using Command, dynamic parameter with wildcard

[hmax]

Crystal Reports 2013
Oracle - using Command in Crystal to retrieve dataset

1. In a dynamic parameter, when we choose to filter on the available values, is there a way to use a wildcard?

I've tested in the Record Selection Criteria where
table.field = {?parameter}
table.field like {?parameter} + "*"
table.field startswith {?parameter}

Should I, instead, use a wildcard at the level of the dynamic parameter filter, and if so, how is this done?

2. Results are always the same, whether I use "=", "like [...] + '*'", or "startswith". Therefore, is the dynamic parameter itself and/or its filter processing the results during a different "Pass" of the data?

I have not found any documentation online about this.

Thank you.

 

[BettyJ]

I believe you are looking for
{?parameter} in table.field

 

[BettyJ]

I am not sure, if you got what you were looking for. If not, try the following and see if it works.
Add a command like the one given below and modify it to match your database table and fields.

[tt]select name from db.employee where name like '%{?keyword}%'[/tt]

Then in the record selection formula,add
[tt]{Command.name}in [{EMPLOYEE.NAME}][/tt]

 

[hilfy]

BettyJ, when you combine multiple commands in a report, you can significantly slow it down. This happens because Crystal cannot push the join between the commands and the filter from the Select Expert to do the database for processing. Instead, it pulls all of the data into memory and joins/filters it there. (See my blog post for more info about using commands in Crystal:

http://scn.sap.com/community/crysta...ces-when-using-commands-with-crystal-reports)

Instead, what I would do is update the where clause in the original command to include something like this:

table.field like '%{?parameter}%'

hmax, in order for this to work, take all of the selection criteria out of the Select Expert and put them in the Where clause of the command. You'll also create the parameter in the Command Editor as commands can't see parameters that have been created in the main report. My blog post link above contains info about how to then make this a dynamic parameter.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[PeterDimitrov]

As far as I understand you want to have a parameter , which will filter the list of values (LOV) for another parameter. This is called Cascading parameters. Your scenario is not supported by Crystal , so you need to use a workaround.
1. Create parameter1
2. Create a command which will retrieve the values for parameter2 and use parameter1 to filter the result.
SELECT ValueColumn, DescriptionColumn FROM YourTable WHERE FilterColumn like '%' + {?parameter} +'%' (the syntax will depend on you database type)
Use this command as a data source for parameter2
3. Use the value of parameter2 in Record selection formula

When you run the report you will see a dialog box to insert value for parameter1. After you set it, there will be one more dialog box, to insert value for parameter 2. The LOV for parameter 2 will be filtered using parameter 1 value. So, in general the report will work like any other Crystal report , but you will see 2 separate parameters dialogs. My guess is that this will be acceptable. For more levels, for example if you have parameter3 , which depends on parameter2, it might become a little bit confusing, because each level will show a separate dialog.

If you can use 3rd party tools the user experience might be better. Here is an example of Cascading parameters with a 3rd party viewer:

http://www.r-tag.com/Pages/Preview_CascadingParameters.aspx

I am posting the example because you do not need to purchase the product. A free, Community version is available here:

http://www.r-tag.com/Pages/CommunityEdition.aspx

So, there are options. You can handle this using Crystal and have some strange dialogs popping up multiple times. However, this option will not require changing your environment. The other option ( with 3rd party viewer) will be probably better for the users , but you need to implement a specific viewer.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[hilfy]

Actually, Peter, you CAN do cascading prompts in Crystal - even from a command. You have to create the prompt in the Command Editor, which can't "see" prompts created in the Field Explorer. Once the prompt is created in the Command Editor, you can modify it in the Field Explorer to make it dynamic and cascading. If the command used for the parameter contains both the field for filtering the prompt and the field for filtering the data, it's easy to make it dynamic from there.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[PeterDimitrov]

Dell, of course cascading parameters are supported by Crystal, this is what I said too. However, scenario described by hmax is not supported because the filtering in Crystal reports cascading parameters is possible just by exact values. In other words, if the values are:
FileType FileName
doc----------file1
html3--------file2
html4--------fiel3
html5--------file4

and you would like to get all html files by using like 'html*', you can't.

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.