• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2482
  • Last Modified:

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?

THX,
Roger
0
rogerk_fsww
Asked:
rogerk_fsww
  • 3
  • 3
1 Solution
 
Kurt ReinhardtCommented:
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?
0
 
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.
Like:
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?

Thanks
0
 
Kurt ReinhardtCommented:
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.
0
Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

 
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.
0
 
Kurt ReinhardtCommented:
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.
0
 
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,
Roger
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now