Moving Params Out of a Command Object

[jakecolman]

I have a command object with the following query:

select FP_FUNDINGID as FundingId,
max(FP_DATE) as FundingDate
from FG_POSITION
where FP_DATE <= TO_DATE('{?As Of Date}', 'DD-MON-YY')
group by FP_FUNDINGID
having max(FP_DATE) <= TO_DATE('{?As Of Date}', 'DD-MON-YY')

As you can see, it requires an 'As Of Date' parameter. I need to modify this so that the command object does not this parameter and that Crystal does all the work in a selection formula. Is this possible?

Thanks!

...Jake

 

[lbass]

It's unclear whether you are trying to eliminate the command object altogether, or whether you just want to create the parameter in the main report. In the first case, you would insert a group on {FG_POSITION.FP_FUNDINGID}, and then go to report->selection formula->GROUP and enter:

maximum({FG_POSITION.FP_DATE},{FG_POSITION.FP_FUNDINGID}) <= {?Date} //where {?Date} is of date datatype

If you want to retain the command, then just remove the where clause and the having clause, and link the command to the main report on funding ID. Then in the selection formula->RECORD, enter:

{command.FundingDate} <= {?Date}

-LB

 

[jakecolman]

LB,

Thanks for your reply. The two solutions you presented produce different results. Completely replacing the command object as you suggested works fine. Since I have another report with a similar, yet somewhat differenrt requirements, I'd prefer implenting your second solution and keep the command object in modified form. Unfortunately, that solution drops some of the data. I can see how you have moved the 'where' clause out of the query and into Crystal. But how did you deal with the 'having' clause? Could this be why I am losing data?

...Jake

 

[lbass]

Do you have an equal join between the command and the main report table? Make sure the link is set to "enforce both". Then as long as {command.FundingDate} is still the maximum date per funding ID, I think it should work correctly. You SHOULD be losing data compared to the first approach, where all data is still in the report, it's just that some data has been selected for display. In the second approach, only those ID's that have a maximum date less than the parameter value will be displayed. But maybe I'm missing something about what you are trying to do.

-LB

 

[jakecolman]

LB,

I decided to go with your first suggestion since its better to reduce complexity and remove the command objects if possible.

But how would I extend this technique if I am grouping on multiple columns?

My other, similar, report uses the following command object:

SELECT c_name AS vehiclename, d_name AS dealname, d_id AS dealid,
d_currency AS dealcurrency, dp_type AS postype,
MAX (dp_date) AS posdate
FROM deal_position, company, deal
WHERE d_companyid = c_id
AND dp_dealid = d_id
AND dp_date <= TO_DATE ('{?As Of Date}', 'DD-MON-YY')
GROUP BY c_name, d_name, d_id, d_currency, dp_dealid, dp_type
HAVING MAX (dp_date) <= TO_DATE ('{?As Of Date}', 'DD-MON-YY')

Again, I want to eliminate the 'As Of Date' parameter or, ideally, simply eliminate the whole command object. But I don't see how to extend your technique to this case where I am grouping on multiple columns.

By the way, this report is a crosstab and does not use any group headers.

Thanks for all your help.

...Jake