use variables w/db fields in formula for select range? 1

[Hueby]

Hi all,

Using CR 10, MS SQL DB:

I have a report that adds billings together from different periods for Jobs, and then grouped by State (in which the job was done).

I want to be able to select the periods as a selection range. So instead of all 13 periods adding, I want to just do period 3-7, or so on.

Group Header 1: groups by STATE code.
details: each Job # has it's own row.
A @total formula adds {JC_BILLING_BALANCE_MC.Billed_Period1} + {JC_BILLING_BALANCE_MC.Billed_Period2} + ... {JC_BILLING_BALANCE_MC.Billed_Period13}

So it adds 1 through 13 in the formula, ran on the detail line.

Group Footer 1: sums @total formula.

Now...... how can I do this? Is it possible to use some variable with the .billed_period# area... ? At the moment, I just comment out the billed_periods I do not want to add up in the formula.

 

[synapsevampire]

Create a numeric range parameter.

Then create 13 formulas:

//formula for 1
if 1 in minimum({?MyParm}) to maximum({?MyParm}) then
{JC_BILLING_BALANCE_MC.Billed_Period1}
else
0

//formula for 2
if 2 in minimum({?MyParm}) to maximum({?MyParm}) then
{JC_BILLING_BALANCE_MC.Billed_Period2}
else
0

etc...

Then you can just use your @total formula which sums the 13 formulas, and you'll only get the ones of interest.

-k

 

[Hueby]

Thank you, that works great!

Is there a way to display the range parameter values? So the user can see on the report period: 3 to 7...

 

[lbass]

Try:

totext(minimum({?range}),0,"") + " to " + totext(maximum({?range}),0,"")

Synapsevampire has an FAQ on how to display parameter values that you might want to look at for future use.

-LB

 

[synapsevampire]

Here's my FAQ on displaying parameter values (and arrays in general):

faq767-5684

-k