Archive for the 'Method' Category



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.




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



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.




Selection based on two different records in the same group

Sunday 16 September 2018 @ 8:38 am

I was visiting Tek-Tips.com today and found an interesting question to answer. A user was having trouble counting sales orders that had items with a certain comment. He wanted to group and count the occurrences by the item ordered. It sounded simple, but the item number was in an “item” record while the comment was on a separate “comment” record. When he filtered by the comment there would be no item numbers and if he grouped by item number the comments would be in a different group. He needed to get the two records into one record.

To solve a problem like this you need to add the sales order table to the report two separate times to create what is called a “self-referencing join”. The second time you add the table, Crystal will give the table a slightly different name (an alias) usually by adding _1 to the end of the table name. Then you treat the two instances as if they were two separate tables.  In this case you could think of the first table as the “item” instance and the second as the “comment” instance.  You link them based on the Sales Order and then add two filters, something like this:

{SalesOrder.LineType} = "Item" and
{SalesOrder_1.NComment} = "Your specific comment"

Note that the two rules above reference one field from each of the two table instances, bringing the two separate records together as one. From there you can group by item number (from the first table) and count the number of records within each group.  Here is a link to the tek-tips question which has a screen shot of the data.

Update 10/3/2018:

Doug Weiner at Beacon Legal Software reminded me to mention that you can always rename (change the alias) of any table used in a report.  Just go to the database expert and click on the table name, then right-click to get ‘rename’ or hit F2 on the keyboard.  You will be allowed to type any alias you want for that table.  This is especially helpful when you use the same table twice, so you can assign names that help you remember which instance is which.  These names are just for the report and don’t affect anything in the database.




Crystal Reports FAQ on the SAP website

Thursday 30 August 2018 @ 5:41 pm

I just stumbled across a FAQ on the SAP website that has some useful information. It was written in 2016 but the information still seems to apply. Many of the answers are links to other pages, like the link to the trial versions or the links to the service packs. I already had most of this information, but learned at least one new trick from the FAQ page.

Retrieving your License Key from the registry:

With older versions of Crystal you could go into Help > About and grab your complete license key.  This was helpful if you were changing hardware or installing on a second PC. With more recent versions you have to go into the license manager and you can only see a portion of the key. I recently had to track down a license key and wished I knew how to extract it from the PC.  Today I saw that 0ne of the tricks in the FAQ is how to find a full license key by searching the registry, using the portion of the key you can see in the license manager.  I’ll be ready next time.




Cloaking Groups

Tuesday 24 July 2018 @ 7:49 pm

There are times when you want a group level to be optional. Maybe you want to give the user a choice between having 1 group level or 2 group levels in a report. While it is easy to suppress Group Headers and Group Footers for group 2, this doesn’t make the group level go away. Group 2 will still put the records in order, and will take priority over any sorting you have specified. A group can’t be removed based on a parameter, but you can have the same effect by ‘cloaking’ the group. Even though it is still there, it has no effect on the report.

To cloak a group you first create a parameter with 2 choices. For example, our user has a parameter called {?Group choice} that allows them to select the number of group levels:

Group by State only
Group by State and City

You set Group 1 to use the field “state”. Group 2 is set to use a formula that says:

If {?Group choice} = “Group by State only”
then “All”
else {table.City}

So, if the user picks “Group by State and City”, then the second group will be the field “city”. But, if the user picks “Group by State only”, then Group 2 will be one big group. Since all the records in Group 2 have the same value, the details will sort as if there were only one group.

So what do you do with the Group 2 Header and Footer? You suppress these sections with a suppress condition that says:

{?Group choice} = “Group by State only”

Now the Group 2 has no effect on the sort, and it is invisible.

A more complex example is when the user wants to see the Top 10 customers but wants to choose if the Top 10 is based on the Average or the Sum of the amount. There is no option for this in the Group Sort Expert. The workaround is to create a parameter so the user can choose either “Sum” or “Average”. Then create two cloaking formulas like the one above, using the Customer field and opposite IF conditions. Group on both fields and set the Group Sort for one group to the average and the other to the sum. Add suppression logic to the group headers and footers to completely cloak one group or the other.




Set Datasource Location from tables to an SP

Saturday 30 June 2018 @ 11:14 pm

A customer had a report that read a handful of tables. He decided to write a stored procedure to return the same data as the tables. He planned to use “Set Datasource Location” to point the tables to the stored procedure, but soon realized that it wouldn’t work.

He could set the location of any ONE of the tables to the SP, but not all of them. If he tried to set the location of the second table to the same SP, Crystal would create a second instance of the SP and link it to the first, matching the links used in the original tables. I have never found a way to to set the location of several tables to a single query/view/SP.

So what is the best solution if you have to do this? Identify the table that provides the most fields to the report, and set the location of that table to the SP. That will map the largest group of fields automatically. The other tables will have to be removed and their fields added manually.

Another thing to keep in mind is that it is usually easier to change formulas than it is to delete and replace database fields on the report. So if one table is used primarily in formulas while another table provides raw fields for display, you might want to set the location of the table that provides the raw fields. Jamie Wiseman wrote an article years ago about creating reports that could be easily set to a new datasource. His method was to create a simple formula field for every database field used, then use those formulas for everything else in the report.  I sometimes do this, and I call these “feeder” formulas.  With Jamie’s method you can delete and add tables and then simply update the feeder formulas as needed.




Adding static and dynamic images to Crystal Reports.

Wednesday 23 May 2018 @ 11:30 pm

There are two ways to put images into reports.

1) A static image stored in the RPT.
2) A dynamic image read from a path [formula] at runtime.

If the report always uses the same image, like a company logo, then option 1 is the simplest. Use the menu options
“Insert > Picture ” and select any of the supported file types. Once the file is placed on the report, the image can be resized as needed.

If the image periodically changes then you need option 2. For example, if different users want to be able to assign a different logo image file to the report without changing the report.  Or if the image is based on a a file name name stored in the database like the image of a specific part.  This method was added over 10 years ago with the release of CR XI (v11) so it will be available in most modern versions of CR.

To use option 2 you start by insert any image as a placeholder in the appropriate spot. Then you use the menu options:
“Format > Graphic > [picture tab]” and click the formula button to the right of the word “Graphic”.  Here you can enter a file path or a URL to any of the Crystal supported image file types. The best part is that this path/URL can be a calculated string. This is how you can change the path or the file name based on values coming from the database.  For instance, if the you have a folder called “PartsPix” and each part has a JPG image named as the part number, your ‘graphic’ formula could be something like this:

“z:\partspix\” & {Parts.Partno} & “.jpg”

The path will be different on each record, and so the image shown will change accordingly. Note if the image files are different sizes then the images shown will also vary in size.




Listing all formula changes with Notepad++

Monday 14 May 2018 @ 12:09 pm

I have written several articles about the usefulness of Notepad++. I often use it to write or test complex formulas and SQL commands. I have also used it to see the affect of a logic change, by comparing the output (text) of the report before and after a change. I have even created a custom language interpreter so that Crystal formulas written in Notepad++ look better than they do in Crystal’s formula editor.

This week I found a new use that I should have seen before. A customer sent me two different versions of a complex report and we weren’t sure which one to use. We needed to see the differences between the formulas in the two reports. So I exported both reports to the format “Report Definition”.  This export creates a text file that lists all of the major settings of the report, lists the objects in each section, and includes the text of all the formula fields that are actively being used by the report.

Once I had the two Report Definitions, I opened them up in Notepad++ and used the “Compare” add-in. This took me straight to the handful of formula differences. It was easy to show these differences to the customer to see which version he wanted to use.

If you want to try out the Notepad++ with the Compare plug-in you will need to start by downloading Notepad++ version 7.4.2.  This is the latest version that includes the plug-in manager.  Once you have Notepadd++ installed you can use the plug-in manager to install the Compare plug-in.  After that you can update to the latest version of Notepad++.




«« Previous Posts
Jeff-Net

Recrystallize Pro

The Expert Series