How to use 'Select Expert' to filter data from SQL 'Command Object'?

[Former Member]

I am using Crystal Reports 11.

I created a SQL Command Object under Database Fields, and am wondering:

Can I use 'Select Expert' to filter the fields/data coming from the SQL Command Object?

Or the only way I can do to filter the fields/data is to use SQL queris within Command Object (such as: ... where region="region1" and prodcut="product1" ...)?

Thank you in advance.

Good weekend.

[JWiseman · Accepted solution]

you can use the Select Expert or Record Selection filter but that does not get processed by the server...the full record set from the Command comes back and is then filtered by crystal.

in the Command you do have the option of parameterizing your WHERE clause and have this filter processed by the database server.

cheers,

jamie

[Former Member · Accepted solution]

Thank you Jamie,

If I am not wrong, IBM Cognos Report Studio has a strong capability for filtering data.

[JWiseman · Accepted solution]

think of the Command object as just a free form sql editor...whatever you put in here is what gets run on the database...very no frills. you can still use the Select Expert against a Command, but if you're already versed in sql and writing a command you may as well create the filter in your coded sql to optimize the data.

if you need the power of the Select Expert, then create your report off of tables instead of using a Command. you will also have the addition of sql expression fields should you need to add aggregates etc. to your filter or report canvas.

also, if you format the record selection in certain ways then the filter will be done at the database as well. there are lots of sap notes on record selection and passing the selection to the server.

[Former Member · Accepted solution]

Thank you Jamie,

I test the filter in CR 11, and it works.

[Former Member]

You should be able to add parameters to the command. Can you post your Sql command, as well as what you want

to select?

[Former Member]

Thnak you V361-V361,

I know how to add parameters to the command.