Using an asterisk for ALL in a parameter

Monday 17 December 2018 @ 11:43 pm

I recently wrote about using ALL when working with parameter fields. After my newsletter went out I got a comment from Bruce Ferguson of Crystal Kiwi in New Zealand (thanks Bruce!). He mentioned that he prefers to use an asterisk instead of the word ALL. This sorts to the top of the list automatically, and us old-timers are used to thinking of the asterisk as a wildcard.  It also doesn’t get confused with similar values, like the state abbreviation “AL”

This reminded me of a lesson from my advanced class. We create a string parameter and write a selection formula like this:

{Customer.PostalCode} like {?Zip}

This can be used three ways by the user.

1) If they enter the full code like 20176 they will get exact matches.
2) They could also do a partial match like 601*. This would give them all postal codes that started with 601. They could enter any number of characters to get a broader or narrower group.
3) If they enter just an asterisk, they would get ALL values, with one exception. They would get records that have a blank postal code, but they would NOT get records that have a NULL postal code.

So, if you want to use LIKE and still have the asterisk return all values, including NULL values, you would need to do something like this:

if {?zip} = "*"
then True
else {Customer.PostalCode} like {?Zip}





SQL to return the last record (SQL Server)

Friday 7 December 2018 @ 10:23 pm

A few years ago I wrote a post that showed a more efficient way to find a ‘last’ record in a SQL command, while pulling several values from that record.

The example I used then was written in the SQL Syntax for MS Access, but the same method can be used in other flavors of SQL. The main difference is the syntax for converting the date to a string. Since I just had to look up that syntax for MS SQL Server I decided to post this here so I can find it easily next time.  Here is a brief recap of what it does:

In SQL it is very easy to add a GROUP BY clause to a query and then calculate the Min() or Max() date for that group. For instance the last order date for a customer. What is not so simple is to return other fields from each last order record, like the Order Number and Amount. The typical solution is to find the maximum in initial query, and then link that to another query that matches that Max() value back to the matching record as an inner join. But this requires at least to queries, and can get complicated if the GROUP BY field and the Max() field are in separate tables.

I find that it is simpler and more efficient to do the Max() on a combined expression that includes all the fields you want. This works as long as you start the expression with the date converted to a string in “yyyymmdd” format. This format ensures that the Max() of the combined expression will be the date and values from the last record.

Take a simple example. We need to write SQL to return each customer’s last order. And from that order you also want to return the customer name, order date, order number and amount. The SQL would look like this:

SELECT
Customer.ID,
max(
CONVERT(varchar(16), Order.OrdDate, 121) + '*' +
Orders.OrderNo + '*' +
Orders.Amount + '*' +
Customer.Name
) as MaxSTring
FROM Orders Orders INNER JOIN Customer Customer ON Orders.CustomerID=Customer.ID
WHERE (Orders.OrdDate>={ts '2018-11-01 00:00:00'} AND Orders.OrdDate<{ts '2018-12-01 00:00:00'})
GROUP BY Customer.ID

This returns one row per customer and the row might look something like this:

20180630*12345*250.87*Joe Smith

You can then write a formula in Crystal to split the string back into pieces. For instance the Customer Name formula would look like this:

Split ({Command.MaxString}, '*') [4]

In English this says, split the MaxString into a list (array) based on the asterisks. Take the 4th element.

Some of you might be wondering why we don’t just do this in the report.  You have a point since this is simple in Crystal:

Group by customer, sort by date, hide the Details and Group Header. Show all the fields on the Group Footer.

This works great, but is not very efficient if you have a large volume of data, because it requires Crystal to read all the details and display the last one. The SQL version above would only return one record per customer to the report.





Crystal Reports formula function libraries (2018)

Tuesday 27 November 2018 @ 10:38 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) Generate bar codes without having to install any fonts

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 can now generate 3 types of charts, crop images and process text through Google’s “sentiment analysis” engine.

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





Selecting ALL with a parameter field

Sunday 25 November 2018 @ 9:52 am

The most common use for parameter fields is in the selection formula, where they are used to filter the records. So if I have a parameter called {?State}, I can use it in the selection formula like this:

{Customer.State} = {?State}

But what if the user wants to be able to select ALL states? If the user enters the word “ALL” in the State parameter with the selection formula above, the query would look for records with a state value of “ALL”, and likely wouldn’t find any. But you can modify the formula to read:

( if {?State} = “ALL”
then True
else {Customer.State} = {?State} )

With this selection formula, the user can enter the word “ALL” and will get ALL states (including those that are blank and null).
Note that this rule is contained in a pair of parens. Those are only strictly necessary when there are other rules in the selection formula but it is safer to add them so they aren’t forgotten. Without the parens a rule that follows the logic above will be considered part of the else and won’t be applied all the time.

One other note.  This works for string fields.  If your field is a numeric or date your options are to:

  1. Make the parameter a string so you can enter “ALL” and then convert entered values in the selection formula to the correct data type.
  2. Use a unique number (e.g. 99999)  or a unique date (e.g. 1/1/2001) as your “All” value.
  3. Make the parameter an optional parameter and then have the blank value represent “All”.




SQL Server dates show up as strings

Wednesday 14 November 2018 @ 4:57 pm

Before SQL server 2008 all date values were stored as DateTime values, even if you didn’t need the time portion. Starting with SQL Server 2008 you could a column either as a Date (with no time) or a DateTime. But I have noticed, recently, that anytime I create a field with a “Date” type, Crystal sees the field as a string instead of a date. So even though I usually don’t need time values, I typically create my table fields and calculations as DateTimes. That way Crystal can format the fields with date options and do date calculations in the report.

But one of my customers recently asked me about this. She found that this only happens if you use the SQL Server ODBC driver. Apparently, the SQL Server Native Client doesn’t convert date fields to strings. So I did a test by creating two DSN’s to a test database and a test table. One DSN uses the SQL Server ODBC driver (10.00.17134.01) from 2018. The other uses the SQL Server Native Client 11 (2011.1102100.60) from 2011. Sure enough, a report using the Native Client maintained the date value as a date, while the ODBC driver converted it to a string.

Then I read this page where Microsoft now recommends using OLEDB:

When I first tried OLEDB I saw two providers. They gave me the same two results as above, which told me that these providers were using the same two drivers I had just tested with ODBC. That is when I realized that the article was talking about a newer OLEDB driver. I downloaded and installed this driver but if you aren’t careful it is easy to miss it in the list of providers. It looks very much like the old one. The only difference between the new one and the old one is that the new one uses the word “driver” while the old one uses the word “provider”.

Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)
Microsoft OLE DB Provider for SQL Server (SQLOLEDB)

The name in parens is what you see under connection “properties” in Crystal’s “Set Datasource Location” window. When I used the new MSOLEDBSQL driver I got date values.

And, thanks to Laurie Weaver, a developer at Wyse Solutions, for letting me know this behavior was driver related.





Setting default export options for a report

Wednesday 7 November 2018 @ 6:10 pm

All versions of Crystal Reports since 2005 allow you to save default export options for each report. These options are stored in each RPT file so they only apply to the report that is open when they are saved. Once these options are saved the user only has to remember which export format to use. They no longer have to remember all of the specific export options.  The saved options will be shown by default.

Here are the steps to save export options for a report:

1) Open the report
2) Go to File > Export > Report Export Options
3) Select a format and click “OK” (note – only formats that have options are listed)
4) Set the desired options and click OK
5) Save the report

The next time you export that report into that format, the options screen will already be filled in for you with the defaults that you saved. You are allowed to overwrite them at runtime, but overwriting them does not change the stored defaults. To change the stored defaults you repeat the steps above.

Also, one report can store export options for several different export formats at the same time. So if you have a report that is exported several different ways, you could store default settings for all 10 of the export formats that have an options dialogue.





Improving report performance with a subreport?

Friday 26 October 2018 @ 9:54 am

In most cases, subreports are a last resort. Typically they slow things down by adding an extra query to the process. But this week I found that moving some tables to a subreport actually sped things up.

The data came from the fundraising software Raisers’ Edge, which uses data exported to an MDB. The customer had designed a new report and found that it ran for over an hour without completing the query. Nothing looked wrong in the structure so I did some troubleshooting. I started with one table and then added the other tables a few at a time to see which table was the problem. All was fine until I reached the last 17 tables which were all linked back to a single table. We only needed one record from each of the 17 tables and they all had about 500 records.

I was able to add the first three tables without issue, but beyond that the report would slow down more with each table added. It only took a few more tables to realize that we couldn’t add all 17 tables to the report and expect it to complete. I double checked the links, confirmed the indexes were in place and still couldn’t find any cause for the slowdown.

Finally, I removed those tables from the report and created a subreport that included just those tables. I also included the table that linked them all together. The subreport ran instantly both on it’s own and when inserted in the main report. My guess is that the MS Access engine was struggling with the number of joins, so splitting them into two separate queries made it more manageable.





Flaw in XML Exports

Monday 22 October 2018 @ 11:06 am

I have a customer who had me create report with a complex layout and lots of optional sections. In addition to using the report itself, he wanted to export the report values into a format where they could be read by another program. We decided to use XML so that the program could search for specific field name tags and extract the matching values. When we started testing the program we noticed some discrepancies between the report values and the XML export values. It mainly had to do with variables that were accumulated in the details and then displayed in the group or report footers.

It took over an hour to identify the root of the problem. Even after fixing the problem I couldn’t explain it, so I created a very simple report to test it. It had one group, and the the following two formulas:

//Accum
WhilePrintingRecords;
NumberVar Accum;
Accum := {Customer.Customer ID}

//Display
WhilePrintingRecords;
NumberVar Accum;

I placed the Accum on the details band and hid that section. I placed the Display formula on a group footer. This exported to XML and showed all the Group Footer values correctly.

Then I split the detail band into A and B subsections. Both subsections were still hidden. The preview of the report looked the same, but when I exported to XML all the group footer values in the XML were zero. It didn’t matter if I put the Accum formula in Details A or Details B. Whenever the Details section was both SPLIT and HIDDEN, the Accum formula would increment correctly in preview but NOT for the XML.  If I used “suppress” instead of “hide” the Group Footer values exported correctly to XML. But even though the details did not appear in preview, they would now be included in the XML.

This behavior looks like a bug to me.  That means there may be other situations where variables don’t behave correctly in XML. So, if you are going to use XML exports, and you are using variables in your report, you need to test the output carefully to confirm that the variables export correctly.





New functions in Cut Light

Sunday 14 October 2018 @ 9:35 pm

Ido Millet at Millet Software has recently added some unique new functions to the Cut Light UFL. Cut Light now adds 130 new formula functions to Crystal Reports. Here are the ones most recently added.

1) Creating charts from formulas.
This includes advanced gauges, sparkline charts (bar and line) and bullet charts. These charts are generated in real time by a formula function. The functions do not rely on Crystal’s integrated chart engine. They are generated as image files which are automatically read into the report. These charts are simple and have no customizable features. There is also no drill-down capability. But these charts can be generated on any series of numbers, including numbers that come from print time variables or even shared variables . Ido has posted some image examples: And you can read the details in the user manual.

2) Image cropping.
Allows you to read an external image file and crop it before displaying it in the report. This can be used with the chart images above and also with existing Cut Light functions that allow you to read the image properties and resize an image. Here is the relevant section of the Cut Light user manual:

3) Google sentiment analysis.
This allows a formula to process a block of text through Google’s “sentiment analysis” engine. The result will be 2 numbers which represent the score (positive, negative or neutral) and the “emotional magnitude”. This can be used to evaluate any comments, Email messages or product reviews you have in your database. It does require that you have an ID to use the Google API, but there is no charge for the first 5,000 calls per month.  You can read more details in the user manual.





Date formulas in Accpac without pwFormatDate()

Wednesday 3 October 2018 @ 11:40 am

If you write Crystal Reports against Accpac, now called Sage 300 ERP, your reports have probably seen or used the function pwFormatDate(). This is a custom function installed by Accpac that converts the numeric dates in Accpac into true date values. The raw data is an 8 digit numeric like 20161231 which is converted into a date by a formula like this:

pwFormatDate ({BKTRANH.TRANSDATE})

Sometimes my Accpac customers will try to send me a report saved with data. But because I don’t have Accpac installed, I don’t have the dll that provides this custom function (u2lcapw.dll). Crystal can’t load the saved data because the formulas that use this function generate errors. In researching this topic I have found that even some Accpac users get this error, despite the fact that the dll is installed.

Fortunately, you don’t need the dll or the custom function to convert these numbers into dates. I have had formulas on my web site for years that convert string and numeric dates into true dates. I just recently updated the formula that can replace pwFormatDate(). The formula above could be replaced by the following formula. It is longer, but it doesn’t require installing or troubleshooting any dlls:

Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
Local StringVar DateString :=
If input < 19500101 then '19500101' else Totext (Input, 0, '');

Date (
Val (DateString [1 to 4]),
Val (DateString [5 to 6]),
Val (DateString [7 to 8]) )

Also, using this formula instead of using pwFormatDate() allows your reports to be opened with saved data on any PC that has Crystal Reports. So, if you are getting errors that say this dll can’t be found you can convert to using the formula above and be done with the dll.

Update 10/12/208:

Ido Millet of Millet Software suggested an alternate approach:

Local NumberVar input := {BKTRANH.TRANSDATE}; // use your field name
// This line checks for a minimum value, any value will work.
If input < 19500101
then Date (0,0,0) else
Date(Picture(ToText(input,0,""),"xxxx/xx/xx"))





«« Previous Posts
Jeff-Net
Recrystallize Pro

The Expert Series