sbarwood
asked on
Using Crystal Parameter Values in Sql Call in v8.5
Hi,
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.
Thanks
Steve
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.
Thanks
Steve
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
Regards
Emre
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
Regards
Emre
ASKER
mlmcc,
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
Steve
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
Steve
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Agreed with rhinok. I will go for 2 though :)
Create the parameters in Crystal
Click INSERT --> PARAMETER
Right Click and select NEW
After adding the parameters use the SELECT EXPERT
Click REPORT --> SELECT EXPERT
Add filter (where clause) using the parameters
mlmcc