Doing a Maximum “horizontally” as opposed to “vertically”

Thursday 14 September 2017 @ 6:17 pm

Most of the time when you see the Maximum () function in a formula, it is to calculate the highest value in a column. It could look like this:

Maximum ( {Svc.LabDate} )

which would calculate the latest lab date in the entire column. Or it could look like this:

Maximum ( {Svc.LabDate} , {Patient.ID) )

which would calulate the latest lab date for each patient’s group of records.

In both cases the Maximum is looking up and down a single column. But there is also a way to use the Maximum() function (and other summary functions) looking across several values in the same row. For example, say that your table had 4 different date fields and you wanted to find the latest date of each row. You could write a formula like this:

Maximum([{Svc.LabDate},{Svc.PTDate},{Svc.OTDate},{Svc.SurgeryDate}])

This would return the date value that was the greatest (latest) out of the 4 fields. Notice that you need both square brackets around the list of dates (the ‘array’) and then you need parentheses around the square brackets for the function to work. This syntax also works for Minimum, Count, Average, Sum, and several other summary functions. To see the complete list of functions that can be used this way, go into the formula editor and open the function list node labeled ARRAY.

One other note. When you write a formula that use a vertical summary function you can no longer total that field with a vertical summary operation. But if your formula is doing a horizontal summary like the last example above, that formula can still be used in a summary operation.





Free software for the Houston area

Tuesday 5 September 2017 @ 11:14 pm

From the R-Tag web site:

R-Tag is providing free licenses to companies in the Houston area. The offer is valid until the end of September 2017.

The eligible products are: R-Tag Documentation and Search and R-Tag Crystal Reports Data Source updater.

We already have a free Crystal reports viewer and scheduler (R-Tag Community edition), which might be useful too.

R-Tag Documentation and Search and R-Tag Crystal Reports Data Source updater are useful with Crystal reports migration, backup processing and report development and we hope they might be helpful to companies in the Houston area with on-site servers.

If you are in the Houston area and want to take advantage of this offer, please see the R-Tag contact page.





Crystal Reports Language for Notepad++

Sunday 27 August 2017 @ 9:59 pm

Last week I wrote about using Notepad++ for working with SQL Commands and formulas. I loved the numbered lines and the way it finds matching parens. But I missed the green comments and blue functions. Notepad++ has about 80 languages that it can format correctly but Crystal formula syntax wasn’t one of them. So I decided to see if I could create a custom language for Notepad++ that would format Crystal formulas the way they appear in the formula editor.

After several hours of experiments I have something that I like. It will format a Crystal formula with functions in blue and comments in green. I even added the literals in red like you see in SQL. And you still get numbered lines and the ability to find matching parens and brackets. I even added the ability to fold (temporarily hide) sections between a pair of parens.

So, if you are using Notepad++ and would like to try my Crystal language you can right-click on the link below and save the xml file onto your hard drive. If you just click the link normally your browser will likely open the XML in a new window.

CrystalReports Language UDL for Notepadd++

Then to deploy the file in Notepad++:
1) From the Notepad++ menu go to [Language > Define your language ]
2) At the top of the window click “Import” and open the CrystalReports.xml file you downloaded.
3) Close and Reopen NotePad++

Then when you click “Language” from the Notepad++ menu you should see Crystal Reports at the bottom of the list.  When you select Crystal Reports, the current window should display Crystal formulas in the correct format.  If you get any strange behavior, please let me know.





Using NotePad++ for formulas or SQL Commands

Thursday 24 August 2017 @ 5:50 pm

If you have to write SQL commands or complicated Crystal formulas, it helps to have a good text editor. My favorite is NotePad++. Not only is it free, but it can do many helpful things beyond what Notepad can do:

  • Number each line.
  • Highlight the corresponding paren/bracket whenever you select a paren/bracket
  • Highlight all the instances of any word that you select
  • Record keystroke macros to automatically repeat a series of commands
  • Select a vertical strip of characters from within of a larger block of text (called “column mode”). (e.g. highlight the 5th and 6th characters in all rows at once)
  • Find all of the differences between two text blocks or two lists, using the “compare” plugin.
  • “Fold” (collapse and expand) sections between parentheses or keywords

It can even format the code for 80 different programming languages, including SQL, showing reserved words and comments in different colors. Within a week I will have a UDL (User Defined Language) for Crystal Reports formula syntax. That will allow NotePadd++ to format a Crystal formula to look the same as it would in Crystal, with the comments in green and the functions in blue.

If you want to try out NotePad++ the best place to get it is through Ninite.com.  I have written about Ninite before. The advantage of Ninite is that it lets you select multiple items from a list of 80+ freeware apps. Then it gives you a tiny exe that installs all the applications you chose (and nothing else) with one click. No questions, no toolbars no ‘bonus’ apps.  It is one of the few places left where you can get freeware without junk.





Set Location in a Progress database

Friday 18 August 2017 @ 10:08 pm

I have written before about database connections that require the table alias to be different than the table name. Often this is accomplished by adding a 1 to the original table name. This came up again recently with a customer using a Progress database.

We were doing a “Set Location” for a connection that covered a handful of tables. Most of the tables were moved to the new connection but one table didn’t move. We got the warning “some tables could not be found” in the new connection. When I have seen this before it usually means setting the location of that one table directly to the corresponding table in the new connection. But for some reason, even though we could see the table in the new connection the update would fail as if the table did not exist. We could even add the table to the report from the new connection, but not set the location to it.

However, I noticed that when I added that table to the report the alias had a 1 added on the end. So as a long shot I changed the alias of the problem table, adding the 1, and then tried the update. This time the update went through. I couldn’t see any reason why this one table would behave differently but apparently this table needed the 1 added before the set location would work. One of the quirks of Progress.





Additional parameter values are needed ….

Sunday 13 August 2017 @ 11:42 pm

If you refresh certain reports and then try to save the report with data you may get the following error:

“Additional parameter values are needed before this report can be saved or viewed with data….”

The error seems to occur only in certain versions of Crystal.  If you see this message and want to save the report with data Crystal will make you choose parameter values again, which means running the report an additional time. With some reports this is a significant waste of time.

I saw this recently with a customer who said it was worth a bit of research if we could prevent the error. The answer I found did solve the problem for that specific report so I thought it was worth sharing. It was caused by a date parameter set to “allow range values”. When we replaced the single “range” parameter with two single-value date parameters (“Begin Date” and “End Date”) the problem went a way. To confirm this I changed the parameter back to a range and the problem reappeared.

This might not work for every situation where this error appears, but it is certainly worth a try if you see this message.





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:

WhilePrintingRecords;
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:

WhilePrintingRecords;
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:

APD009
B-1025
B326
B-52

(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.

APD0000009
B0000052
B0000326
B0001025





«« Previous Posts
Jeff-Net
Versa Reports

remiCrystal reporting solution