Selection Formula Pass-through not working with SQL Command Object

I'm using Crystal Reports XI. We use Crystal Reports embedded into a share point site written in C#/ASP.net. Our web developers instatiate a report in code and pass the selection criteria by appending to the Select Formula object.

The problem I'm running in to is that when I use a SQL Command Object, the selection formula is not being passed to the sql server as a where clause. This causes the entire result set to be queried and then limited on the server. For very large tables, this is causing a significant performance hit.

Here's an example: SELECT foo.id, foo.code, foo.name FROM foo foo

Select criteria is shown as: {Command.name} like "someName"

If I then run the report the entire foo table is queried and then crystal reports limits from the entire set.

If instead I do it based on the table without a command object, the WHERE clause is appended to the sql query and all the limiting is done at the db level.

I'm assuming this occurs because a SQL command could potentiall开发者_运维问答y be very complex and adding a WHERE clause to the bottom isn't always possible. My question then, is there any good way to force some sort of pass through. Should I be talking to the C# devs and asking them to rework the way we pass parameters?

Thanks for any help and discussions.

A record-selection formula is never added to the Command-object's query.

I'm not certain if the CR SDK supports Command object modification (I know it supports read-only access, however).

The Command object does support parameters (in 2008, they can be multi-valued parameters) which can be accessed programmatically (they are converted to Parameter Fields in the report).

I'm assuming that you are using a Command object because of the query's complexity. If you can push the complexity to a SQL view, then use the 'visual linking' expert to create your query, you'll have more flexibility.