In the ideal world, customers write perfect specs up front, and requirements never change. In my work reports tend to evolve through several iterations. Some customers don’t realize what would work best until they start to see drafts.

Last week a customer presented me with what is usually a fairly simple request: print only the first 40 widgets. So I sort the records into the correct order (in this case date and item number) and then suppress all rows with a record number greater than 40.

Then the customer asks if the chosen 40 records can be sorted by location. Well they can, but changing the sort to location would change the chosen 40 records. So we go to plan B.

I created a formula field called {@Rank} that combines the Date and the Item Number into one string. The combined value needs to sort by date and then by Item Number so I used this:

Totext ({Date} , 'yyyyMMdd' ) & '-' & Totext ({ItemNumber} , '00000')

This converts the date and item number into a value that still sorts in the correct order. But I didn’t actually sort on this formula. Instead, I calculated the 40th value using the NthSmallest() function. It is like the Minimum function but allows you to specify not just the very smallest value (minimum) but any number, like the second smallest value in the column. You use the function like this to get the 40th value:

NthSmallest (40, {@Rank})

There is also an NthLargest() which is like Maximum(), and Continue Reading »
Using the NthSmallest() Function

A Crystal Reports web development team has posted in the SAP forums about some security weaknesses in the .NET web deployment model. They are trying to get the attention of SAP, without success so far.

It appears that this team deployed a .NET web application using the Crytal Reports runtime engine. During a security audit they uncovered some serious vulnerabilities. It appears that they weren’t able to get the SAP support team to look at the problem because they do not have a support subscription, so they posted it to the forum as three question. From what I have read they are trying to walk the line between highlighting the seriousness of specific vulnerabilities, while not making these same vulnerabilities easier to exploit.

Those of you who do .NET web development might want to check out the following links (some of which appear to have been deleted as of 3/6).

Link 1 (131436) (deleted)
Link 2 (130250) (deleted)
Link 3 (133449)

And thanks to Ido Millet of Millet Software for pointing these posts out to me.

I have written before about the advantage of using CurrentFieldValue when applying a formatting condition. This is especially true when applying a similar format to multiple fields, because it allows you to ‘paint’ the format properties from one field to another using the format painter.  Related to this method is a way to make it easy to update all of these formulas at once, without having to change them individually.

For instance, say you have 12 columns and the font color formula for all of them is:

if CurrentFieldValue > 90
then CrGreen
else CrYellow

When someone wants to change the value from 90 to 95 or tweak one or both of the colors it would require updating one and repainting all the others. Instead you could create some feeder formulas for the different literal values. I would create three formula fields:

{@target} which contains the number 90.
{@LowColor} which contains the function CRYellow
{@HighColor} which contains the function CRGreen

If you have those three formulas, your formatting formula would be:

If current field value > {@target}
then {@HighColor}
else {@LowColor}

You can apply this formula to all 12 fields.  Then when someone needs to change yellow to orange you just change the {@LowColor} formula from CrYellow to Color(255 , 165 , 0). This way the change affects all the formatting formulas in one stroke.

Visual Cut has always been a solid desktop scheduler. I have used it for years to send out my invoices.

But Ido Millet of Millet software has recently added some new features that allow you to scan multiple reports. The scan reads in the database fields, text objects, SQL expressions and all formulas (including selection formulas and property expressions). You can group, sort, search, and export the results. You can also find & replace text and expressions and save the updated versions of these reports. Ido has posted a video demo of this.

Another video demo shows how you can deploy an entire series of new formulas in Excel and import these new formulas into multiple reports. With these new features, Visual Cut will now be included in my annual comparison of RPT management utilities.