Use Parameter based on results of a formula or to populate a formula

[jazbar]

Hi All

I'm using CRXI and I'm unsure if this is possible but hopefully someone can give me some advice.

I have a report that queries a table containing products, the dates they were ordered and the quantity received.

My report uses a formula called {@Last Recd} which is Maximum(Date Received) to extract the last time the product was received. What I want to do is have the ability to filter by the last date received in a range. i.e. Products last received 30-60 days ago, Products last received 60-90 days ago, and Products received 90 + days ago. (I've created formulas that use
{@Last Recd} <= currentdate -30 and
{@Last Recd} > currentdate -60
for the 30 - 60 days range and done the same for the other two).

I want to be able to use a parameter for the user to be able to select the range but I have a problem. I cannot bind the parameter to the {@Last Recd} formula or {@30 - 60}as it isn't available for me to select as it hasn't been formulated.

Is there a way around this at all?

I wondered if I could supress those records not in the each range using Section Expert, but how would I use a paramter and pass the value of the parameter to the formula against Supress in the Section Expert?

Thanks for your help.

 

[Turkbear]

Hi,
try using the parameter valuse as flags to test in your selection formula, sometning like:

If [?rangeparam} = '30 to 60' then
(
{@Last Recd} <= currentdate -30 and
{@Last Recd} > currentdate -60 
)
Else...

However if the {@Last Recd} formula is not populated then that will also fail..

What database are you using?
Can to create a Command the gets the last date from each product and create your report from that and your existing query, like

@Command
Select product, Max(DateRecieved)as LastDate order by product;

Then link that to your existing table by the product
and use the LastDate field in the formula above.









To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I would try writing a SQL expression {%maxdate}:

(
select max(`date`)
from table A
where A.`Product` = table.`Product`
)

Then use the {%maxdate} in your record selection formula and in a formula to group by the ranges.

-LB

 

[jazbar]

Thanks Turkbear and lbass for your responses. Much appreciated.

I'll try the solutions that you've posted. Problem I have currently is that the report is based on a Business View at present over DB2 so, as far as I'm aware, SQL Expressions aren't supported.

Is this the case?

Thanks again

 

[lbass]

If you don't see SQL Expression as an option in the field explorer, then it is not supported. It also would not appear in the field explorer if you were already using a command.

If you are using a view, why not build the summary field into the view? (I'm not familiar with Business Views, so maybe I'm offbase here.)

-LB