Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Using Crystal Parameter Values in Sql Call in v8.5

Posted on 2004-11-05
Medium Priority
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 a.name, a.id, (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 101

Expert Comment

ID: 12506779
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

ID: 12507668
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

ID: 12509377

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

Kurt Reinhardt earned 500 total points
ID: 12510295
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

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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Screencast - Getting to Know the Pipeline
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
Suggested Courses

580 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