Archive for July, 2017

RPT management utilities for 2017

Sunday 30 July 2017 @ 2:19 pm

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

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source 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.
Report Analyzer by Cortex Systems
.rpt Inspector 3 Professional Suite by Software Forces, LLC

A switch to turn “stealth” subreports on and off

Tuesday 25 July 2017 @ 11:28 pm

I wrote a long time ago about the “Stealth” subreport. This is an invisible subreport that runs in the background and provides a value to the main report as a shared variable. But if you have a few of these subreports in the same container report it can be difficult to do troubleshooting. To see what the subreports are returning, you have to go into each of the subreport’s key sections to unhide or unsuppress them.

But I received a suggestion from Gordon Portanier that makes it simple to activate and deactivate “stealth” mode in all of the subreports in one place. First you write a formula like this and place it in the report header of the main report:

Shared BooleanVar Stealth;
Stealth := True;

Then you go to each of the sections in the subreport(s) that you want to turn on and off when troubleshooting. Instead of hiding or suppressing those sections, you put in a suppress condition that says:

Shared BooleanVar Stealth;

As long as the first formula isn’t modified, those sections should stay suppressed and the subreport should stay invisible. When you want them to appear you comment out the bottom line in the first formula, the one that assigns the value TRUE to the variable. Because Boolean variables are FALSE by default the variable will revert to FALSE and all the sections where you used that condition will now appear.
So, thanks again to Gordon Portanier of Crystalize in Canada for sharing this suggestion.

Refresh prompt when you change pages?

Sunday 16 July 2017 @ 2:03 pm

This strange behavior has nagged at me for a while. I refresh a report that has subreports and preview the first page. When I move to the next page I get a prompt that says:

“Change in record selection formula”

and the choices are “Use Saved Data” or “Refresh Data”.

Now it is obvious that I haven’t changed the selection formula by clicking the “next page” arrow, and yet I have seen this behavior in several reports. Usually it doesn’t cause a problem and I just ignore it.

But today I worked with a customer who wanted to fix that behavior so we took the time to do some experiments. After a few tries it finally dawned on me that there was one way that a formula will change, all by itself, between one subreport and the next. When a formula references the current time it will change slightly as you page through the report. Sure enough, the selection formula in the subreport was limited to activity in the last 15 minutes:

{DateTime.Field} in DateAdd ('n', -15, CurrentDateTime) to CurrentDateTime

To fix the problem we need a single DateTime value to use in all of the subreports. So we decided to calculate a value in the main report and pass it down to the subreports as a parameter. To make sure the value didn’t change on each page we wrote a formula to combine the functions DataDate and DataTime like this:

// {@DataDateTimeEnd}
DateTime(DataDate, DataTime)

The advantage of using the DataDateTime is that once the report is refreshed that value doesn’t change from one page to the next. The CurrentDateTime function can return different values as the report goes through the pages.
I then calculate a second DateTime that is 15 minutes before the value above:

// {@DataDateTimeBegin}
DateAdd ('n', -15, {@DataDateTimeEnd})

Last, I pass these values down to the subreports as linked parameters and use them in the selection formula like this:

{DateTime.Field} in {?Pm-DataDateTimeBegin} to {?Pm-DataDateTimeEnd}

We still get the last 15 minutes of transactions, but without any prompts.

Sorting numbers that come after letters

Monday 10 July 2017 @ 7:01 pm

I recently had a customer ask if I could help him sort part numbers. The problem was that the part numbers start with one or more letters followed by 2 to 7 digit number and sometimes a dash in between. Since the field is a string, and since the numbers vary in length, the sort doesn’t work as expected:


(See this article for why dashes are sometimes ignored in sorting. )

So here are the steps to get these to sort in a meaningful way. First I wrote a formula to determine how many initial characters there are. This is based on a formula I wrote about before that was designed to strip all numbers off of the right end of a string:

//{@Initial Chars}
Local StringVar x := replace ( {ITEM.ITEMNO} & '1' , '-' , '' );
Local NumberVar y := Length( Totext( Val( strReverse( x ) ) ,0,'') );
if length(x) = y then 0 else length(x) - y

Then the remaing steps are:

1) Eliminate the dashes.
2) Strip off the initial letters and convert the remainder to a numeric using Val().
3) Use Totext () to convert the number into a zero-padded string
4) Tack the initial letters back on.

With local variables you can see the process, step by step.

Local StringVar x;
Local NumberVar y;
x := Replace ({ITEM.ITEMNO} , '-', '') ;
y := val (x [ {@Initial Chars} +1 to 99 ]);
x [ 1 to {@Initial Chars} ] & Totext (y, '0000000')

The resulting values would look like this if you placed them on the report. But these would probably only be used for sorting and not displayed.


Recrystallize Pro