Can you use a Crystal Report formula field in a SQL command datasource

Just a simple question, I hope.

Got a command datasource as the only datasource for a Crystal 2008 report.
I know how to use a parameter as a filter in the command.
I would like to use a formula in place of the parameter that is added from the command edit window.
Is there a way to do that?

Who is Participating?
Kurt ReinhardtConnect With a Mentor Sr. Business Intelligence Consultant/ArchitectCommented:
You cannot use a formula inside a SQL Command.  Instead, you'll need to create the SQL Equivalent of the formula that parses the parameter and use it directly inside the SQL Command.

An aside, should the command run quicker if I add the filter directly to the select statement in the command , or just use it in the record selection formula in Crystal?

As a matter of best practices, you shouldn't ever use Record Selection Criteria in a report that uses a SQL Command (or stored procedure) as it's data source.  That's because the command will be executed and return it's recordset before the criteria is ever processed.  It also means the criteria will be processed on the client, not on the database server.  You should build all filters inside the command.
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
What do you mean you want to use a formula instead?  Formulas and parameters do different things.

Do you mean you want to apply the parameter against a formula field instead of a database field from the command?
rogerk_fswwAuthor Commented:
I have a formula that parses and formats a report parameter.
So, I want to use the formula in the sql cmd instead of the initial parameter.
instead of
Where my.field in ({?parameter})
I want to use
Where my.field in ({@formula}).

An aside, should the command run quicker if I add the filter directly to the select statement in the command , or just use it in the record selection formula in Crystal?

Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

rogerk_fswwAuthor Commented:
That's what i thought about the formula, just needed to check for sure.
One other question about the sql command. The sql does a union on three tables in the command. Now if I want to use a filter like the 'where tbl.field = (report parameter sql)
Do i have to use that for all three tables in the union or can I just use it on one of the tables?

That advice about the command helps me understand the way the Command processes using the report selection formula. I will add that to my best practices list.
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
You should apply whichever filters are appropriate to each table.   Since I'm unfamiliar with your data, it's tough to say, because the criteria for each of them could be completely different.  If, however, they all need to process the same filter then you need to apply it to all three of them.
rogerk_fswwAuthor Commented:
Yep. That's what I have been doing.
Since I didn't create the command sql I'm kind of stuck with making it work.
It seems pretty inefficient the way it is now, but oh well I will have to make do.

Thanks for your quick answers,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.