Using Crystal Parameter Values in Sql Call in v8.5


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.


Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

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
sbarwoodAuthor Commented:

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


Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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.



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Agreed with rhinok. I will go for 2 though :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

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.