Crystal Reports: Converting Domain Names to Full Description

Suppose you send out your Crystal Report with URLs or top-level domain names. Some of the top level domain codes may be a little cryptic. What if you want to see the description of a top-level domain (e.g., New Zealand instead of .nz).

It can be done. We did it. We found a text file that listed all the top level domains, and then (with some copy and paste) we created a long formula to display the descriptions.

If {@TLD}=”nz” then “New Zealand” else

If {@TLD}=”au” then “Australia” else

If {@TLD}=”uk” then “United Kingdom” else

If {@TLD}=”ca” then “Canada” else “Elsewhere”

However, we also think a Select Case statement might be a tidier solution:

Select {@TLD}

case ”nz”: “New Zealand

case ”au”: “Australia”

case ”uk” :“United Kingdon”

case ”ca” : “Canada”  default: “Elsewhere

That's one way of doing it. But maybe your COO complained about the fact domain names are spelled out in the report (maybe for a good reason, maybe not). Yet, you have users who really need this information. What should you do?

One solution, if you don't mind alienating people, is to tell them to go online and look it up. A slightly better solution is to include a link to a Web page that has the info (and assume the URL won't change).

But you can satisfy that COO and the other users, by including a subreport that presents a table of top-level domains with the name spelled out. Then set this up as a drill-down. While you're at it, consider including the following information in that subreport:

• List of important URLs at that domain (Websites of suppliers, customers, other offices of your company).
• Time zone(s), expressed as GMT + or - (or, to be current, use UTC instead of GMT).
• International dialing options (country code and any other relevant dialing info).
• Customer contacts on that domain (name, title, company, e-mail, phone number, company URL).
• Vendor contacts on that domain (name, title, company, e-mail, phone number, company URL).

Here's some info on drilldowns:

Drill Down Reports are among the powerful features available in Crystal Reports. If you have a simple report with one group, one series of subtotals, and some detail information, you can change it into a drill down report by setting "Hide" on Group Header 1 and the detail section.

One thing that makes the drill down work better is to put the Group Name, or some other suitable caption into the group footer. That caption and the subtotals are what appear at the top level. When you drill down with a double click you get the Group Header, Details and Group Footer.

Drill down reports can be viewed and printed at the summary level, or a specific drill down, or any combination of these. They are a useful analysis tools for users at all levels.

Now, here's a twist.

A Chelseatech client wanted a report developed that had three groups (product group, product and customer) with a drill down to the lower level groups. The challenge was that the lowest level actually combined two fields: region and customer. They didn’t want a drill down or summary by region, but at the customer level they wanted customers sorted by region and then customer.

So we used a formula to combine the two fields (Region + Customer) and grouped by that. We customized to group name to display the customer name and placed the region field in the group 3 header.

We then set the conditional suppress on the Group 3 header to

DrillDownGroupLevel <>2 and {Table.Region} = Previous({Table.region})

The top two levels displayed as expected. When the third drill down processed, we got the header section with the region description to appear. This was exactly what the customer wanted.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.