Archive for March, 2016

Comparison of desktop-based schedulers (2016 update)

Friday 25 March 2016 @ 7:04 pm

How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.

There is one new product this year for a total of 12 products. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.

Find the first/last date that meets a condition

Sunday 20 March 2016 @ 11:17 pm

A customer had a number of different appointment types in their data. They wanted Crystal Reports to calculate, for each client, the most recent date for several different appointment types. They asked why this formula didn’t work:

If {APP.TYPE} like "Assessment"
 then Maximum( {APP.DATE} , {CLIENTS.CLIENT_ID} )

The expected this syntax would give them the date of the last Assessment, and they were close.  But in Crystal you have to create the conditional column (formula) first  and then you apply the maximum function to that column.  The conditional formula would look like this:

//Formula called {@AssessmentDateMax}
 If {APP.TYPE} = "Assessment"
 then {APP.DATE}

This creates a column of dates where only rows that have the type of “Assessment” will show a date. You can then create a summary (Insert > Summary) that calculates the maximum value of this column for each client group. Or you can write the following formula to calculate the same summary inside a formula field:

Maximum( {@AssessmentDateMax} , {CLIENTS.CLIENT_ID} )

If you want to find the most recent date for a second appointment type, like “Intake”, you would need a separate formula and a second summary.

So that is how you calculate the last date.  To find the first date you need to change the formula. If you calculated the minimum of the {@AssessmentDateMax} formula shown above you would get a blank. That is because all of the records that are not assessments would have a blank value (date zero), and that would be the lowest date value in the column. The formula below will work correctly with the minimum function, but will not work if you want to do a maximum:

//Formula called {@AssessmentDateMin}
 If {APP.TYPE} = "Assessment"
 then {APP.DATE}
 else Date (2050,1,1)

If you are willing to do a bit more work there is a way to create a single formula that you can use with both the minimum and maximum functions.  It requires that you create a separate {@NullDate} formula, which has the data type of “Date” but that contains a null value.  Null values are skipped over by the Crystal summary engine so they don’t count toward the minimum or the maximum. Here are the three steps to create a {@NullDate} formula:

1) Create a new formula, name it {@NullDate} and save it with nothing but the following function:


2) Use it in the ELSE line of a formula like this and save that formula:

//Formula called {@AssessmentDate}
 If {APP.TYPE} = "Assessment"
 then {APP.DATE}
 else {@NullDate}

3) Now go back into the {@NullDate} formula, delete the function and save it empty.

This generates a Null date.  You can now use {@AssessmentDate} with both Minimum and Maximum functions and in both cases the summary function will ignore records that are not assessments. You can use the same {@NullDate} in the ELSE of several different date formulas if needed.

Shared array variable vs recurring subreport

Wednesday 16 March 2016 @ 11:33 pm

Sometimes a subreport has to be run for each group. For instance you might need to run a subreport once for each employee in a report. If you have a large number of employees the subreport is slow, it can make the report crawl. Customers often ask if we can run the subreport once for ALL employees and then pass all of those value to the main report.

In theory you can. You could write a subreport to read all employees at once, and then use an array in the subreport to store a value for each employee. You could then pass the entire array back to the main report by using a shared array variable. The challenge comes when you try to use those values in the main report. There is no simple way for the main report to match up the elements in the array to individual employee records in the main report.

The complicated solution is to create two arrays with the same number of elements. One stores the employee IDs, while the other stores the value for each ID. Each employee ID in the first array should line up with the corresponding value in the second array. To retrieve an employee’s value in the main report you have to write a ‘looping’ formula. This formula will loop through the first array and count how many elements it has to check to find the matching employee ID. By using that count Crystal knows where to find the corresponding value in the second array. This is an example what the formula looks like, courtesy of Gordon Portanier of Crystalize in Toronto:

//PURPOSE OF FORMULA:           Parse through the shared arrays to pick out the information
    shared stringVar array CustomerId;
    stringVar array OrderId;
    stringVar array OrderAmount;
    stringVar array OrderDate;
    numberVar PositionOfOrderId; 
    numberVar PositionOfOrderAmount; 
    numberVar PositionOfOrderDate;
    numberVar i;
    redim OrderId[1]; 
    redim OrderAmount[1];
    redim OrderDate [1];
    //Loop through first array            
    for i := 1 to Count(CustomerId) do
        if instr(CustomerId[i],"|CustomerId:" & ToText({Customer.Customer ID},0,"")&"|")>0 then
            PositionOfOrderId := Instr(CustomerId[i], "OrderId:"); 
            redim preserve OrderId [if Len(OrderId[1])>0 then Count(OrderId)+1 else 1];
            OrderId [Count(OrderId)] := Mid (CustomerId[i], PositionOfOrderId +8 , Instr(Mid(CustomerId[i], PositionOfOrderId + 8) ,"|") - 1);
            numbervar PositionOfOrderAmount := Instr(CustomerId[i], "OrderAmount:"); 
            redim preserve OrderAmount [if Len(OrderAmount[1])>0 then Count(OrderAmount)+1 else 1];
            OrderAmount [Count(OrderAmount)] := Mid (CustomerId[i], PositionOfOrderAmount + 12 , Instr(Mid(CustomerId[i], PositionOfOrderAmount + 12) ,"|") - 1);
            //Order Date
            PositionOfOrderDate := Instr(CustomerId[i], "OrderDate:"); 
            redim preserve OrderDate [if Len(OrderDate[1])>0 then Count(OrderDate)+1 else 1];
            OrderDate [Count(OrderDate)] := Mid (CustomerId[i], PositionOfOrderDate + 10 , Instr(Mid(CustomerId[i], PositionOfOrderDate + 10) ,"|") - 1)

Not only is it complex to write, but it takes the report a long time to process, especially if you have hundreds of employees.
But, sometimes you get lucky and you can use a simpler method. I recently had a customer with a recurring subreport and that had to run over a thousand times, once per employee. The report took hours to run. As we talked about our options I realized that the employee ID was a number. This meant that I didn’t need to create two arrays in the subreport and I didn’t have to write a looping formula in the main report. Here’s why:

Say the first employee in the subreport has an ID of 78. I could create an array of 1000 elements and put his value in element 78, based on his ID. If the next ID is 95 I can put that value into element 95, and so on. I use the ID as the position in the array. In the main report when I get to employee 78 I don’t have to loop to find the right element, I just retrieve element 78.

The one challenge that we had to work around was that the numeric ID went up to 9000. In Crystal an array can only hold 1000 values. So I created 9 different arrays, each with 1000 elements. When I went to put a value into the array I took off the first digit. I used that digits to determine which array to use. I used the other 3 digits to specify the correct element to store or retrieve the value.

So if you have numbers under 10,000 as your ID (or if you can create an ID like this) you can use the simpler method for passing a list of values from the subreport to the main report.

Linking tables without dragging

Wednesday 9 March 2016 @ 11:46 pm

Unless you are using a command, you will need to link your tables in the “Links” tab of the Database Expert. And typically you create the link by dragging a field from one table and dropping it on a field from another table. This works fine in most cases, but can be awkward when:

  • The tables are far apart and not visible at the same time, like when you add a new table
  • The field you are linking to is near the top or bottom edge of the table
  • You are working on a slow connection

The first item usually requires dragging tables around the window, which is slow.  In the second case the table has a tendency to scroll up or down quickly when you drag a field near the top or bottom edge.  In the last case a slow connection or a screen delay can interrupt the signal, causing the field to drop in the wrong spot.  All three of these can be fixed by using a nifty feature added to recent versions of CR.

When you want to drag Field A and drop it on Field B you can right click on Field A and in the fly-out menu you will see the words “Start Link”.  You select that and then scroll around until you see Field B.  Then you right-click on Field B and select “End Link”.  This will create a link between those two tables, wherever they are on the screen.  You can then hit “Auto Arrange” to bring the newly joined tables closer together.

And here are some other tips that you can use on the LINKS tab.

Recrystallize Pro