Formula tips from a reader

Tuesday 25 June 2013 @ 10:16 am

After my last newsletter I received a note from James Flowers with some formula suggestions, and thought his comments along with my own might be helpful:

1) James likes to number the formulas in sequence to show which formulas depend on other formulas (feeder formulas). So if the Tax formula relies on the Sales formula he would number them 01_Sales, 02_Tax, etc. Of course there would have to be a longer sequence to make this worthwhile.

I have done this a few times when I had a complex sequence of calculations and it helped. But usually my formula relationships look more like a family tree than a straight sequence. When I need to change a complex formula hierarchy I have on occasion resorted to making a visual map of field dependencies. We are talking about a handful of reports in my entire career, and the maps are always made after the fact, after months go by and someone finds a data combination that we didn’t take into account. Making the maps help me to spot the best place to make a change to the logic or fix an error.  You can also read my other tips on naming formulas.

2)James also suggested adding an extra formula called “Info” to provide a single place to put all of the miscellaneous background that helps you understand a report. These might include the design history, ideas for future changes or some alternate formula logic that you don’t want to completely lose. In my reports, I am more likely to put comments in individual formulas. But in rare cases I have created formulas like this that give more general information about the report.

If you do create an “Info” formula, you might want to put it on the report and suppress it. I sometimes use the “report definition” export to document all the formulas in a report. But the report definition feature will not export the text of a formula if it is not used at all in the report. So if you want these formulas to export as part of a report definition, you should place it on the report somewhere, even if you have to suppress the field or the section.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

2 Responses to 'Formula tips from a reader'

  1. Beinhere - June 29th, 2013 at 10:45 am

    this will help me and my staff learn to better organize. we’re getting more and more into formulas.
    thank you

  2. Ken Hamady - July 29th, 2013 at 12:52 pm

    Howard Hammerman has this suggestion:
    RE: Naming formulas.

    I often use running totals where the same underlying field is summed (or counted) to different group levels when a logical condition is met.

    I use the group number as part of the name. So if the report has three groups in the hierarchy and I have running totals for the field “AMT” I would name them

    AMT_3, AMT_2, AMT_1, AMT_0 (The 0 is for the Grand total)

Leave a Reply

Recrystallize Pro