Can't sum up a range identified by conditional format 1

[AZdesertdog]

I've written a financial report that summarizes payments and charges grouped by areas and financial period (month). I have then suppressed the details so that it only shows my totals in the group footer. That way on one page I can look at just the summary lines (group footer) for 24 periods ( from the parameter fields). I then wanted to conditionally highlight a 6 month range in the columns- e.g. Wanted to then make the background of the @Charges summaries to be blue for months 8-13 only.. Accomplished this by creating a running total in the detail that counts each month and using that number to identify month 8-13, etc. so I could use conditional format with highlighting expert. The thing I CAN NOT do is to summarize those 6 months groups that I have highlighted. I've tried creating a formula that if-thens for the month number (trying just one month at first) and then sums the charges field and it will show the correct amount in the details but when I try to summarize the formula, I'm unable to "cannot summarize this field". I've tried messing with "whileprintingrecords" but think I'm missing something easy to get this number. Any help would be MUCH appreciated. Thanks! -JJP

 

[Ngolem]

words, words, words....show us an example of what you want accomplished....much easier to understand.

As far as whilePrintingRecords goes....you cannot use this with summary functions. But the old 3 formula sum should work nicely here....but show us an example

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[AZdesertdog]

Ok- I'll give it a try- here's a 6 month version.

Month Charges Payments Receivables
1 $10 $2.50 25%
2 $500 $100 20%
3 $500 $100 20%
4 $100 $100 100%
5 $300 $100 33%
6 $10 $5 50% <-footer2
Totals $1420 $407.50 29% <-footer1

Each line is a summary line (footer2)totaling all the individual charges/pmts. I have the 'detail' section suppressed. &quot;Month&quot; is a running total number (1-24). Total line is footer1 and summarizes the months. Imagine the charges in months 2 and 3 are highlighted by the highlight expert (if month = 2 or month =3 then navy background - white text, etc) This all works fine. What I want to do is add one more total to the footer1 line that adds the highlighted charges (2 and 3) to get the total of $1000. Does that help? Thanks much!

 

[AZdesertdog]

Please tell me more about the &quot;old 3 formula sum&quot;. Thanks!

 

[lbass]

Create three formulas:

{@reset} for the Group 1 (area) header:
whileprintingrecords;
numbervar highlights := 0;

{@highlights} for the details section:
whileprintingrecords;
numbervar highlights;

if {table.month} in [2,3] then highlights := highlights + {table.charges} else highlights := highlights;

{@display} for the group 1 (area) footer:
whileprintingrecords;
numbervar highlights;

-LB

 

[AZdesertdog]

In creating the @highlights formula, I get &quot;a number is required here&quot; and the formula is highlighted between the *'s. Any thoughts? Thanks in advance!!


whileprintingrecords;
numbervar highlights;

if {#Rtotal0} in [2,3] then highlights := **highlights + {@Charges}** else highlights := highlights;

 

[synapsevampire]

Try a Running Total that has the field to sum in it, and in the evaluate use a formula place something like:

{table.month} in [2,3]

-k

 

[AZdesertdog]

So disregard the 3 formula option? I am using a running total to identify the month periods. Do you mean to create a new running total? I tried that but wasn't able to add a formula with the original running total in it. I think I'm lost..

 

[synapsevampire]

Why use a RT to identify a month period?

A formula with month(date) works fine.

Now base the RT on the month formula.

-k

 

[lbass]

Actually, the reason for the three-formula approach is that the jpoulos' display is already based on summaries in the group footer. Sorry, I should have said that {@highlights} should be placed in the group 2 footer and should have indicated a summary, not a field, for charges. Where I used {table.charges}, you should plug in your summary for charges.

If you are getting a message that a number is required here, then your {@charges} is not of a number datatype. Please show us the content of your formula and the datatypes of formula elements.

-LB

 

[AZdesertdog]

SUCCESS!! My problem was that my @charges formula was of a money type. I just wrapped a conversion around it and we're getting the correct sums on the correct field. Thanks so much, all of you! -JJP