Monday, February 2, 2015

Crystal Reports : How hard should Crystal work?

If you're stuck with a report, if you can't seem to claim that last inch towards the finish line, you might be working Crystal Too Hard. This happened a lot to me when I was first learning Crystal. I had a strong programming background and all I wanted to do was get to where I wrote the IF THEN statements. But if you find yourself writing too many, chances are there is an easier way.

Let us consider a bunch of Customer Type Codes;

C - Client
P - Prospect
V - Vendor
R - Personal
O - Other

So, the idea is to convert these to text in Crystal. Here is the formula;

IF {TABLE.FIELDNAME} = "C" THEN "Client" ELSE
IF {TABLE.FIELDNAME} = "P" THEN "Prospect" ELSE
IF {TABLE.FIELDNAME} = "V" THEN "Vendor" ELSE
IF {TABLE.FIELDNAME} = "R" THEN "Personal" ELSE
IF {TABLE.FIELDNAME} = "O" THEN "Other" ELSE "Unknown"

That would work, but suppose your list of IF THEN tests needed to be, say, 50 elements large? Maybe you need to convert a numerical code into a State Name. Or convert a Product Code. The last place you want to be is evaluating hundreds of IF THEN statements.

A very powerful option is to create what I call a "Helper Table". Instead of using IF THENs, create a table that holds the values. The table needs two columns, one for the Code and the other for Description.

Table CustType
Code     Description
C           Client
P            Prospect
V           Vendor
R           Personal
O           Other

Now add that table to the Crystal Report. JOIN your source data to CustType via CODE. Now, just call CUSTTYPE.DESCRIPTION within your report surface to automatically render the plaintext description.

Not only is this faster, but it's a much easier way to manage lists of transformations. It also leaves more "room" in Crystal to do what it does best (i.e. Summaries, Grouping).



Crystal Reports : Using Drill-Down in the Groups Tree

Here is a thing that always drives me nuts; seeing actual Crystal Reports printed out, like on paper. For a meeting. A copy for everyone. Stapled. STAPLED. It's enough to make me swoon. Why? Because the moment the report leaves Crystal, it's dumb. Immutable. Un-searchable. Un-refreshable. And someone at that meeting is going to want to Change the Report. Use different dates, maybe add some fields.

I guess the moral of the story could be to just use a projector. And while you're at it, don't forget;

You can Drill Down Groups, even within the Crystal Viewer.

1. Right-click within your Groups Tree on the left hand side and select "Drill Down".

2. This "breaks out" the right-clicked Group into a separate tab within the viewer.

3. From here, you can choose just to print the currently open Tab.

Keep in mind that Refreshing the report will close all tabs.

Crystal Reports : What you need to know about SQL Expression Fields

I'll be honest, I rarely use these. But they can be really powerful on a MSSQL back-end. The first thing you need to know is that they allow you to submit TSQL directly to the server.

So, if I was after the Year value of a date field, the Crystal Formula would look like this;

YEAR({TABLE.DATEFIELD})

...where the SQL Expression would use native Transact SQL, like this;

DATEPART(yyyy, TABLE.DATEFIELD)

The big difference is how these two recordsets are generated. If I plug this into the Select Expert using Crystal Formulas, ALL records are downloaded from the server, and THEN the formula is processed. If you use the SQL Expression, it becomes a part of the Crystal SQL Statement, and only SELECTs the appropriate rows.

But it wouldn't work with, say, Access, or Interbase. The SQL Expressions are server-specific. What works with MSSQL may need a different syntax for Oracle or mySQL. But if you're reporting against millions of rows, this could dramatically reduce refresh times.

I've also seen it used in hyper-specialized environments to call Custom SQL Functions, as the SQL Expression is just a code-delivery system. Any exposed SQL object, including three-part qualifiers, should be allowed. I.e. DATABASE.DBO.TABLE.

GoldMine : Knowing What's Going On

This happens; a user defined-field will someday get "screwed up". And it will be an important one. Like one day, Customer Type will magically switch from "Customer" to "Vendor". Or a complete street address will go missing. It is inevitable, certain as the fact that no one will fess up to it. 

If this happens a lot, it might be worth enabling Logging Changes to History. This is done on a per-field basis and is done in Field Properties;

1. Right-click the field in question, select "Properties".

2. Click on the "Security" tab. 

3. Check the box labeled "Log Changes in History".

4. OK your way back.

Every time this fields' value changes, a History item will be written against the Contact Record. It actually tracks a lot of detail about the previous values, etc. You may find that it "Junks Up" the History tab a bit, and may only wish to keep this enabled until you figure out what's going on with your field updates.

Have fun!

GoldMine : Filtering Last Contact Date

The Summary tab, is there nothing finer? At a glance, you can see;

Creation Date / User
Last Update Date / User
Last Contact : Last SUCCESSFUL call
Last Attempt : Last UNSUCCESSFUL call
Prev Result : Reference from the most recent Completed History
Next Step : Reference from the closest upcoming Pending Activity
Comments : Free form text field to record a thoughtful note about the Client

That Last Contact Date is especially juicy. Just think, if you had been recording all of your calls in the Notes tab, this wouldn't be possible. So, let's show off a little and generate a list of everyone who hasn't been Contacted This Year.

1. Open your Search Center. Yes, really.

2. Select "Lastconton" from the Search By list.

3. User "Lesser or Equal" (the idea is to return anyone with a Last Contact Date before the New Year. Past Dates are "Less Than" Future Dates).

4. Enter in 01/01/2015 as the search value.

Easy, right? If you feel like Getting Fancy;

1. Feel free to right-click and Output to Excel.

2. Don't forget you can add Columns via the "Columns" button.

3. Use the Filters/Groups button to save this as a re-usable Filter/Group.

Try it!



GoldMine : Setting Up AutoFill

When is the last time you were typing something in a box and DIDN'T see it start to automatically fill itself? I'll tell you when; the last time you used GoldMine.

Until now! Rejoice, for Auto-Fill can be enabled on a per-field basis.

1. Click into the field in question, and bring up the lookup window by hitting F2 or clicking the grey arrow to the right. This is why sometimes you will see these referred to as "F2 Lookups".

2. Click the "Setup" button.

3. Check the box labeled "Auto Fill", then OK you way back.


You'll notice that now as you type within the field, it will try and predict which picklist item you are trying to select. Keep in mind the Auto Fill is only as smart as your picklist; it can only predict against items within that list.

Fun Fact : Back "in the day", you could also invoke F2 lookups by right-clicking within the field. Anyone running GoldMine BEFORE version 8 should be able to do this.