How to determine a "dynamic" line in a summary report

[AZdesertdog]

I have an accts. receivable report that shows group summary values (details suppressed) of 24 months of data. I have a running total printing the months numbered in a column (month 24 down to 1). I want to make calculations of other columns based on a dynamic &quot;line&quot; where 10% is the value below and any value <10% is the value above it. Here's a short example (I've left out rows for months 24-12 just to shorten it up):

Month Outstanding AR
11 7%
10 9%
9 9.5%
-------------------
8 11%
7 14%
6 18%
5 24%
4 29%
3 34%
2 45%
1 49%

Note the &quot;line&quot; between month 8 and 9. Right now the line is created with highlighting expert and is ALWAYS between month 8 and month 9. I have the line here because this is USUALLY where we can say that we have 10% or less outstanding AR. THe problem is, for most months this works but sometimes the line should be between, say month 6 & 7, or 9&10. I need a formula to determine where the break is for 10% in this column. Any help appreciated! -JJP

 

[synapsevampire]

Right click the group and select insert section below.

Draw the line that section.

Right click the section and select format section->X 2 next to suppress.

Add a formula such as:

not(previous({summaryfield%})<10
and
{summaryfield%} > 10)

This assumes that you have this percentage already worked out and that you can get it using previous.

If not, try using a variable to store the previous % in and use this same methodology to suppress the line.

-k

 

[AZdesertdog]

When I put that formula in to suppress the section it says: The field has no previous or next value. I suspect that's because the field I want to evaluate {@ARoutstanding%} is a summary in the group footer. Can you give an example of the second suggestion you mentioned about creating a variable? Thanks! -JJP

 

[synapsevampire]

Sure, the quick and dirty solution is in a section after the highlighting is performed (you can right click whatever section and select insert section below and then hide it)drop in a formula

@lastvalue
whileprintingrecords;
numbervar LastValue:={@ARoutstanding%};

Now this value will be available at the next iteration of the highlighting.

not({@lastvalue}<10
and
{summaryfield%} > 10))

-k

 

[AZdesertdog]

Hmm.. when I tried to create:
whileprintingrecords;
numbervar LastValue:={@ARoutstanding%};

It complained about my formula and said it (@ARoutstanding%) &quot;must be a number&quot;. I thought it was. Below is @ARoutstanding%


(if Sum ({@Charges}, {Header.InitialCreatePeriod})<>0
then Sum ({InvoiceDetail.InvDExtendedAmount},
{Header.InitialCreatePeriod})/Sum ({@Charges},
{Header.InitialCreatePeriod})else 0)*100

 

[synapsevampire]

Not sure why you're getting that, but change this:

@lastvalue
whileprintingrecords;
numbervar LastValue:={@ARoutstanding%};
LastValue

 

[lbass]

I fooled around with this, and the only way I could get the line in the right position was by using the following formula in GH1a (and then I suppressed this section):

whileprintingrecords;
numbervar sum1;
numbervar sum2;

sum1 := if {@ARoutstanding%} < 10% then 0 else 1;
sum2 := if {@ARoutstanding%} >= 10% then sum2 + 1 else 0;

I placed the line in GH1b and the GroupName and {@ARoutstanding%} in GH1c.

Then I formatted GH1b with the following formula:

whileprintingrecords;
numbervar sum1;
numbervar sum2;

sum2 + sum1 <> 2

I couldn't get the &quot;lastvalue&quot; approach to work--the line always appeared one group later than it should, but maybe I wasn't understanding something.

-LB

 

[AZdesertdog]

Hey LB, that worked! One issue though- I'm finding for some of the results I have more than one line that is between the 10% and therefore I get more than 1 line on a page occasionally. Is there any way to only line (section) to print for just the first occurance? Thanks!

 

[lbass]

If you had more than one value = 10% in a row, you would still only get one line, if you used my formulas exactly, so I'm assuming that the percentages are not always in descending order. If this is the case, then alter the first formula to:

whileprintingrecords;
numbervar sum1;
numbervar sum2;
numbervar counter;

sum1 := if {@ARoutstanding%} < 10% then 0 else 1;
sum2 := if {@ARoutstanding%} >= 10% then sum2 + 1 else 0;
if sum2 + sum1 = 2 then counter := counter + 1;

And change the suppression formula to:

whileprintingrecords;
numbervar sum1;
numbervar sum2;
numbervar counter;

sum2 + sum1 <> 2 or
counter <> 1

-LB

 

[synapsevampire]

LB's placement area and theory is sound, however simplify and try using Booleans:

whileprintingrecords;
booleanvar LineTime;
if {@ARoutstanding%} > 10% then
LineTime := true;

Base the suppression on:
whileprintingrecords;
booleanvar LineTime;
booleanvar NoMoreLine;
(
not(Linetime)
and
not(nomoreline)
)
or
NoMoreLine

Then in the {@ARoutstanding%} area set the NoMoreLine variable in a formula or in the {@ARoutstanding%} if it allows for it:

whileprintingrecords;
booleanvar NoMoreLine;
if {@ARoutstanding%} > 10% then
NoMoreLine := true;

Now I think you'll get one line prior to the first number greater than 10%, fixing the potential for multiple lines if numbers repeat, and the whole process is a tad more elegant.

-k