Formula Selection issue

[BHScripter]

Hi:

I have a report that is really a letter template for sending to apt. renters that are up for renewal. My SQL script pulls in all the possible lease proposals months 1 - 12 ranked by price lowest to highest (some of the rankings can be the same if the same price exists for more than one lease term option. I am grouping on property, then unit, then rankbyrent.

so I get proposedrent, leaseterm, renter, rank

I am presenting only some of the options, they should look like this:
Option 1: lowest rent at x-months (which is rank 1 with the maximum lease term within rank 1)
Option 2: next lowest rent at x-months (which is rank 2 with the maximum lease term within rank 2)
Option 3: next lowest rent at x-months (etc)
Option 4: Price for shortest term (which is 2 months)
Option 5: price for next shortest term (which is 3 months)
Option 6: price for next shortest term (which is 4 months)
Option 7: price for month to month or the 1 month lease term

I was able to successfully get the formulas for the first three options by creating sections for the rankbyrent group, as an example: for option 1 I created the following formula:

if {Command.rankbyrent} = 1 AND {Command.ProposedTerm} = maximum({Command.ProposedTerm},{Command.rankbyrent}) then {Command.ProposedRent}
else 0
Then I suppressed the group section unless it was rankbyrent = 1 and it did not equal 0


then I created another section below this for rankbyrent 2 and did the same thing. This worked great for the first 3 options which play strictly from ranking.

Now I need to create the section for Option 5 the shortest term or the 2 month proposed term. I created a formula:
if {Command.ProposedTerm} then {Command.ProposedRent} else 0

I then put it in another section in the rankedbyrent group, even with no suppression all the values returned 0 rather than 1 option showing the 2 month rent amount.


Thanks so much in advance

 

[xxyyzz49]

I suspect your formula in option 5 in your post is not the one in the report
if {Command.ProposedTerm} then {Command.ProposedRent} else 0
should return an error message stating a Boolean is required

 

[BHScripter]

Sorry missed part of it, was doing it from memory, here is what I had:

if {Command.ProposedTerm} = 2 then {Command.ProposedRent} else 0

 

[lbass]

I think you should show some sample data and then show what results you would expect based on that data.

-LB

 

[xxyyzz49]

Random thought while sample data is gathered. How are you handling NULL values? May not be any but had to ask.