Using Crystal Parameter Values in Sql Call in v8.5

Posted on 2004-11-05
Last Modified: 2008-02-01

Im trying to write a bit of sql to bring back some client information along with some project information. What i need to do is pass some date values to the database to select certain projects but the query to pull back project information is a subquery. i.e.

Select,, (select id from project p where p.foreignkey = a.primarykey and rownum = 1 and (p.startdate <= {startdate} and p.enddate >= {enddate})
from account a

{startdate} and {enddate} being the parameters selected by the user when running the report. Is there is any way of doing this in crystal 8.5. I need to bring back the accout information whether there is a project or not.


Question by:sbarwood
    LVL 100

    Expert Comment

    Yes.  You can use parameters.

    Create the parameters in Crystal
    Right Click and select NEW

    After adding the parameters use the SELECT EXPERT
    Add filter (where clause) using the parameters

    LVL 10

    Expert Comment

    You can also write that query as a stored procedure in db. That way the parameters are declared in the proc and everything is done in db. Then you set the proc created as your report;s datasource in a usual way like doing a table.

    Whe you look at the field expert, you see the parameters are automatically populated in the expert. Crystal verifies the db and if there are any parameters it asks the user to enter those when the the report runs. I always do it this way instead of using crystal functionality.

    Both will work for you. Decide which want you want to use

    Author Comment


    I know how to use the select expert butnnot sure how this would work as it is in a sub query. Surely to use the selecrt expert on the start and end date of the project i would need to return these in the embedded select statement and this would return more than the one field allowed in a subquery.  

    Sorry ebolek, due to database limitations (ie annoying dba's) i cant create a stored proceedure which would be ideal for this kind of thing.

    Does this make it any clearer? Im a bit baffled by this problem

    Many thanks


    LVL 26

    Accepted Solution

    You can't use the select expert, based on the SQL you showed above.  The select expert will add the code to the WHERE clause.  You're asking to parameterize a subquery in the SELECT clause.  Unfortunately, you can't modify the SELECT clause in Show SQL Query in CR 8.5.  As such, your best options are:

    1)  Create a parameterized Crystal Query using the Crystal SQL Designer and then create your report based off the query.  This is similar to creating a Stored Procedure, except that it resides outside the database and, therefore, is not controlled by the annoying dba's ;)   I generally don't recommend this approach since Crystal Queries have been phased out in later versions due to the implementation of SQL Command Objects (CR9 +).  This might be your best option.  

    2)  Create a Stored Procedure - which you've already stated you can't.  This is a fairly common problem.  Many organizations have defined barriers between report writers and dba's.

    3)  Upgrade to CR9 or 10 and create a parameterized SQL Command Object.  This is the approach I'd recommend if you can do it.


    LVL 10

    Expert Comment

    Agreed with rhinok. I will go for 2 though :)

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    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 …
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    754 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

    21 Experts available now in Live!

    Get 1:1 Help Now