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



4 comments:

Viora care said...

Positive thing that there are you blog that intend fake rolex within very economical rate. When you're a wrist watch supporter, gathering up distinctive brandnames in fake rolex for sale can be a great idea. As a consequence, you should have not even dedicate plenty purchasing a single recognized sit back and watch. An important replica breitling watches fails to primarily work as necessary, it might be implemented being a supplement which will finishes the hublot replica. Typically, you won't be able to go out while not having on you. The application is actually an important reflex to examine precious time as a result of examining much of our arm. Whilst you go to the blog, you can understand opportunities and even rate reductions at fake prada bags.

Jack George said...

Crystal Reports Crystal Reports Training "
Crystal Reports Online Training
Send ur Enquiry to contact@21cssindia.com
Description
This course covers the core functionality of Crystal Reports XI reporting tool and relational database concepts." more… Online Training- Corporate Training- IT Support U Can Reach Us On +917386622889 - +919000444287 http://www.21cssindia.com/courses/crystal-reports-online-training-28.html

hotmail sign in said...

good article and nice urging commented here, I am in fact enjoying by these.

aliya seen said...

Thanks for sharing this interesting blog with us. My pleasure to being here on your blog. It is brilliant blog. I like the way you express information to us. do my programming assignment