Archive for July, 2018

RPT management utilities for 2018

Friday 27 July 2018 @ 12:57 pm

I have just updated my comparison of RPT management utilities for 2018. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list includes 9 tools:

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source Updater by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
.rpt Inspector 3 Professional Suite by Software Forces, LLC
.rpt Inspector Online by Software Forces, LLC (new)

Cloaking Groups

Tuesday 24 July 2018 @ 7:49 pm

There are times when you want a group level to be optional. Maybe you want to give the user a choice between having 1 group level or 2 group levels in a report. While it is easy to suppress Group Headers and Group Footers for group 2, this doesn’t make the group level go away. Group 2 will still put the records in order, and will take priority over any sorting you have specified. A group can’t be removed based on a parameter, but you can have the same effect by ‘cloaking’ the group. Even though it is still there, it has no effect on the report.

To cloak a group you first create a parameter with 2 choices. For example, our user has a parameter called {?Group choice} that allows them to select the number of group levels:

Group by State only
Group by State and City

You set Group 1 to use the field “state”. Group 2 is set to use a formula that says:

If {?Group choice} = “Group by State only”
then “All”
else {table.City}

So, if the user picks “Group by State and City”, then the second group will be the field “city”. But, if the user picks “Group by State only”, then Group 2 will be one big group. Since all the records in Group 2 have the same value, the details will sort as if there were only one group.

So what do you do with the Group 2 Header and Footer? You suppress these sections with a suppress condition that says:

{?Group choice} = “Group by State only”

Now the Group 2 has no effect on the sort, and it is invisible.

A more complex example is when the user wants to see the Top 10 customers but wants to choose if the Top 10 is based on the Average or the Sum of the amount. There is no option for this in the Group Sort Expert. The workaround is to create a parameter so the user can choose either “Sum” or “Average”. Then create two cloaking formulas like the one above, using the Customer field and opposite IF conditions. Group on both fields and set the Group Sort for one group to the average and the other to the sum. Add suppression logic to the group headers and footers to completely cloak one group or the other.

First day of the calendar quarter

Wednesday 18 July 2018 @ 9:53 pm

Finding the the beginning date for most date periods is simple. For example:
First day of this Year: Minimum (YearToDate)
First day of this month: Minimum (MonthToDate)
First day of Last month: Minimum (LastFullMonth)
First day of this week: Minimum (WeekToDate)

But there isn’t a comparably simple way to get the first day of the current calendar quarter. So if you ever need that calculation you can use the following formula:

Local numberVar x := Month (CurrentDate);
Date (
Year(CurrentDate), //Year
if x > 9 then 10 else
if x > 6 then 7 else
if x > 3 then 4 else 1, //Month
1 //Day

If you want the beginning of the quarter related to a field other than today’s date you can substitute a database field, formula or parameter in the two places that say “CurrentDate”.

Recrystallize Pro