• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 827
  • Last Modified:

FM - How to color every other row in a Summary Report with Totals Only

I can change the color of the background for every other row in a complete list or in a summary report with a Body easily enough, but how would you change the row background color for a summary report with a Trailing Summary only and reflects a found set, not the entire set of records?  

Since it is a found set, would I have to use a script to number the records and then do a conditonal formatting formula on a field that is behind the entire row of fields in the Trailing Summary?  Thanks.
0
rvfowler2
Asked:
rvfowler2
  • 7
  • 5
2 Solutions
 
Will LovingPresidentCommented:
Conditional formatting is probably simplest. You just use a simple calculation that checks to see if the record number is odd:

mod( Get ( RecordNumber ) ; 2 ) = 1
0
 
Will LovingPresidentCommented:
Just to be clear, the Mod function returns the remainder after number is divided by divisor.

   Mod( number ; divisor )

In this case, the divisor is simply 2, so if the record number is even the result is zero, if odd, then it's 1.
0
 
rvfowler2Author Commented:
Great idea, unfortumately, the report is not sorted in the same order as record numbers.  Since we only add properties a few times a year, I'm going to write a script that renumbers them in order of PropCode in a field called "BackgroundColor".
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Will LovingPresidentCommented:
Record number is independent of any sort order. It's simply the number of the record in current found set regardless of how it's sorted.
0
 
rvfowler2Author Commented:
Didn't see the obvious.  Didn't work because it is a found set and doesn't include all the prop records.  Going to add a script that numbers fields off the the side of the report.
0
 
rvfowler2Author Commented:
The above didn't work either because I'm doing totals, and how do I enter a new rec# for each total row?
0
 
Will LovingPresidentCommented:
Randy - can you clarify a bit more? It sound like you've got a report that has sub-summaries and you're saying the alternate background is somehow not working because of the sub-summaries?
0
 
rvfowler2Author Commented:
Yes, should have used the more precise term, Subsummaries.  It is a report without a Body; so it only shows totals/rows for each Prop Code and thus of course it is sorted by PropCode.  My problem is that FM does not allow a setting for alternate background color for the Subsummary Part, only the Body Part.
0
 
Will LovingPresidentCommented:
So, the clarification is this instance is that you are counting sub-summaries, not records, and need a way to determine whether a sub-summary is odd or even. One way would be a looping scripts. Sort all records by Property, run a loop that captures the first Property value and marks the first record in that sub-group with 1, the first record in the next sub-group with 2, etc. Then reference the flag field in the conditional formula. Since calculations in a sub-summary always reference the first record in the sub-summary group, it should work.

Alternately, if ALL Properties will always appear in the report and you have the Properties table that we talked about earlier, you could just number the properties in that Properties table and then reference that for the Conditional formatting. However, if Properties may or may not appear then you need to loop through. There may be a way to calculate it but I can't think if off the top of my head...
0
 
rvfowler2Author Commented:
Will, almost worked, but I found that the totals did not seem to reference the first record consistently, thus I didn't have consistent coloring of every other record.  However, an easy modification is to number every record in the found set as you see in the script below, AND IT WORKED!  Thanks so much.
0
 
rvfowler2Author Commented:
Though just to clarify, I numbered the records on my Detail report which did have a Body, and, of course, the script below numbers the records for the same property with the same number.  Also, for ease, I created two fields:  1) BackColorNum where the numbers get filled in, and 2) BackColor which is the field I set the conditional formatting on and place it behind all the other fields on the row.  This way the number in the BackColorNum doesn't show up on your row, though I'm sure there is some way to not show it via conditional formatting, but I took the easy route using the empty field, BackColor.
--RowCount.JPG
0
 
rvfowler2Author Commented:
Need to number every row and probably less room for error that way anyway.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 7
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now