Archive for November, 2015



Crystal Reports formula function libraries (2015 update)

Wednesday 25 November 2015 @ 6:14 pm

It is time for my annual comparison of formula function libraries. If you aren’t familiar with User Function Libraries (or UFLs) they are DLL files that add new formula functions to your Crystal Reports formula editor. With these functions your formulas can do some pretty amazing things like:

1) Carry values from today’s report to tomorrow’s report
2) Carry values from one report to another.
3) Append lines of text to an external text file.
4) Automatically copy a value to the clipboard.
5) Check the user name of the user running the report.
6) See if a file or folder exists (on your network or on the internet).
7) Rename/copy/delete a file on your hard drive or network drive.
8) Launch an application or run a batch file.
9) Execute a SQL statement (Select/Insert/Delete).
10) Send an Email using information in the report.
11) Create a table of contents or an index for your report.
12) Calculate distances between zip codes or long./lat. coordinates.

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five UFL providers are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (CUT Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents)

The only product that has changed since last year is CUT Light, which now includes a handful of new functions:

  • Search text using regular expressions
  • Calculate the height of a growing text object
  • Calculate the number of rows in a wrapping text object
  • Trigger a web service to return a value
  • Convert HTML/RTF into text

If you need help deploying one of these functions in a project let me know.




More efficient SQL for returning the last record

Saturday 21 November 2015 @ 4:57 pm

I wrote up a method I called the “wormhole” years ago. Normally when you calculate the maximum of a column you can refer to that maximum in any formula in the report. The wormhole technique takes advantage of this behavior to transport other values from the same row as the maximum value. For instance, I can move several values from the last record of the report and show them at the top of the report. To do this I append the other fields to the original, take the maximum of the combined string and then split the maximum value back into pieces. Recently I realized that this method can also be used in SQL expressions to return multiple values, and in SQL commands to simplify certain queries.

Say you have a table of transactions by customer. You want to generate a list that shows the customer name and three fields from their last order (date, order ID and amount). This is simple to do in Crystal without resorting to SQL, but it requires that Crystal bring back ALL of the orders. You then either sort and display only the group footer, or you do a group selection to select the maximum order date for each group.

But if you only want the maximum record to come back from the database then you need to do the work in SQL. There are several methods to return just the last record. The most common involves running two queries and joining them. The first query would pull a list of ALL of the orders for each customer and would return the four fields. The second query would use a “Group By” on the customer and return only the last or Max() date, for each customer. Then you would inner join these two results on both the Customer ID and the Order Date to have all the fields from the first query but limited to the date from the second query. Here are is an example of this query that can be run as a SQL command in the Xtreme sample database:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`, `Orders`.`Order ID`, 
`Orders`.`Order Amount`, `Orders`.`Order Date`
FROM (`Customer` `Customer` 
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
INNER JOIN ( 
   SELECT `Customer`.`Customer ID`, max(`Orders`.`Order ID`) as LastOrder
   FROM `Customer` `Customer` INNER JOIN `Orders` `Orders` 
   ON `Customer`.`Customer ID`=`Orders`.`Customer ID`
   Group By `Customer`.`Customer ID`) LastOrd
ON `Orders`.`Order ID`=`LastOrd`.`LastOrder` 
and `Customer`.`Customer ID`=`LastOrd`.`Customer ID`

This works fine, but it has to do two separate queries.  This can slow things down, especially if you have to do this several different times. The alternate approach would be:

SELECT `Customer`.`Customer ID`, `Customer`.`Customer Name`, 
max ( 
Format (`Orders`.`Order Date`, 'yyyy/mm/dd') & '-' & 
format (str(`Orders`.`Order ID`) , '00000') & '-' & 
format (str(`Orders`.`Order Amount`), '000000.00') 
) as MaxStr
FROM (`Customer` `Customer` 
INNER JOIN `Orders` `Orders` ON `Customer`.`Customer ID`=`Orders`.`Customer ID`)
Group by `Customer`.`Customer ID`, `Customer`.`Customer Name`

Lines 2 to 6 calculate the maximum of a combined string for each customer. The string includes the Date, the ID and the Amount of the transaction. The string starts with the date formatted as Year-Month-Day. The maximum of that string will grab the last order, and will also include the values of the other three fields. It returns the same values as the fist query and only takes one pass. The only downside is that you get your values inside a combined string.  To split them apart will take a few extra formulas in Crystal, but you might find this worth the extra effort because the query should run faster.

This technique is especially useful when you do a subquery inside a SQL Expression field. SQL Expression fields can only ever return a single “value”, but with this method that one value can include a combination of several fields from the same row of data.




Cross-tabs calculated before/after group selection

Friday 13 November 2015 @ 10:32 pm

I just learned a neat trick for cross-tabs that allows you to decide if the cross-tab is calculated in the first pass or in the second pass. A cross-tab that uses only database fields or most formula fields will be calculated in the first pass. But if you use a specific type of formula in the cross-tab, then the cross-tab will be calculated on the second pass.

[ crickets ]

To see if this will help you I guess we have to answer the two obvious questions:

1) What difference does it make if a cross-tab happens in the first or second pass?
2) How do you create the special formula?

The answer to the first question has to do with Group Selection. Say you have 100 customers in your report but you only want to report on those who have spent more than $1000 in total. You would create a subtotal for each customer and use that subtotal in the Select Expert (Group) as a criteria. A filter that involves a subtotal has to be applied as Group Selection.

So, lets assume that using Group Selection we reduced the report form 100 customers to 30. Now you add a cross-tab to the report without using a special formula. This cross-tab will still include all 100 customers even though the report only shows the 30 customers that are over $1000. This is because a normal cross-tab is calculated before Group Selection – in the first pass. But if you use a special formula in the cross-tab, then it will be calculated after Group Selection – in the second pass. This cross-tab will be based on the 30 customers.

So how do we create a special formula? All you have to do is start the formula with “WhilePrintingRecords”. So lets say that the cross-tab mentioned above has a column for each Ship Date. You could write a formula that says:

WhilePrintingRecords;
{Orders.ShipDate}

If you use this in place of the original Ship Date field in the cross-tab, that pushes the Cross-tab to the second pass. You can do this with any field used by the cross-tab. Also, any formula that involves a grand total or subtotal will have the same effect when used as part of the cross-tab, even without the phrase WhilePrintingRecords.

This blog article includes a diagram of the passes and even shows the two places where cross-tabs can be calculated.




Another free viewer, RptView

Monday 2 November 2015 @ 9:57 am

RptView by Pursuit Technology was originally for sale. Then it was free with ads. Now it has now been released as a free viewer without ads. All that is required is that you register with the vendor. To see what it can and can’t do you should read my annual comparison of viewer applications which I updated this week to include this new information.

If you don’t have CR installed you can view any report simply by double-clicking the rpt file. Alternatively, you can launch RptView and then identify the folder that contains your reports.  Keep in mind that RptView is no longer being actively maintained. If you do need support, you can hire the vendor to fix any problems you find.




Jeff-Net

Recrystallize Pro

The Expert Series