Syntax for entering a parameter (that has a range) in a formula

[jizzycrystal]

I am trying to get the total amount for all three values in my parameter range. My parameter range is period say 08 (start of range) to say period 10 (end of range). My parameters are fields from a table {nltran11.trans_period}
My attempted formula is
If {?period} = (['08' '09’ '10']) then +{bud11.base_amount08}+ {bud11.base_amount09} + {bud11.base_amount10}
but this is not pulling the correct total, it is just pulling the value of bud11.base_amount08.
I think my syntax is incorrect. Any ideas? From a non tech finance person!

 

[IanWaterman]

Unless I have misunderstood your post, it looks like you have a horrible database design to contend with.
Ideally you would want a record set for each period thus data would only have two columns

Period and BudAmount

your formula would be much more simple

If PeriodField = {?Period) then BudAmount else 0

you could then sum formula and not need to worry about separate budget columns. As it is you will need to build lots of formulae to calculate totals for your report period.

YOu can specify and element of a range paramenter but treating it as an array.

if 08 will always be the first element then you can use

If {?period}[1] = '08' then {bud11.base_amount08} else 0

But if you want to run report for periods 7,8 & 9 you will need another formula to determine period 8 value.

Trying to build a flexible report with your current design is going to be a nightmare.

Ian

 

[jizzycrystal]

Thanks Ian...yes a nightmare!
How about:
If {?period}[1] = '08' then {bud11.base_amount08} AND If {?period}[2] = '09' then {bud11.base_amount09} AND If {?period}[3] = '10' then {bud11.base_amount10}
am struggling!
I want to total all three periods in one formula

 

[IanWaterman]

If {?period}[1] = '08' then {bud11.base_amount08} AND If {?period}[2] = '09' then {bud11.base_amount09} AND If {?period}[3] = '10' then {bud11.base_amount10}

will not work you will need 3 formula
@bud08
If {?period}[1] = '08' then {bud11.base_amount08} else
If {?period}[2] = '08' then {bud11.base_amount08} else
If {?period}[3] = '08' then {bud11.base_amount08} else
0

@bud09
If {?period}[1] = '09' then {bud11.base_amount09} else
If {?period}[2] = '09' then {bud11.base_amount09} else
If {?period}[3] = '09' then {bud11.base_amount09} else
0

etc

Then
W@qtr8-10
@bud08 + @bud09+ @bud10

YOu will need new formula for each qtr

Ian

 

[jizzycrystal]

Ok thx Ian I will try that. Really appreciate your help.

 

[jizzycrystal]

Thanks Ian but it won't accept any of these formulaes as it says a string or an array of values is required here (it highlights {?period})I did put in a range of 08 to 10 in the parameters.
Thanks
Justine

 

[IanWaterman]

What type of parameter is {?period}? You were using 08 and 09 so assumed it was a string. From what you are saying it could be a number?

Ian

 

[jizzycrystal]

period is a field from a table nltran and the field is called trans_period & has 12 values or data elements being 01 to 12
yes the parameter is set up as a string...not sure what this means..sorry I didn't develop these reports so trying to figure crystal out.
I have selected true to allow ranges.
I'm just unsure what syntax to use in a formula when a parameter has more that one value , i.e. a range of values.
Thx