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

Posted on 2012-08-16
Last Modified: 2012-08-16
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?

Question by:rogerk_fsww
    LVL 26

    Expert Comment

    by:Kurt Reinhardt
    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?

    Author Comment

    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?

    LVL 26

    Accepted Solution

    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.

    Author Closing Comment

    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.
    LVL 26

    Expert Comment

    by:Kurt Reinhardt
    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.

    Author Comment

    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,

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now