Diving into Crystal Reports

SAP Crystal Reports 2016 Support Pack 3

We acquired some new software and are working on integrating it.  Our old software interfaced with a program called bartender to print labels, but the new program is using Crystal Reports.

To further complicate matters, our company has a large number (~160) of customer specific labels.  Customers like to see their own logos, or maybe they want to hide certain fields… and we’ve been very accommodating.  Some customers use computerized systems to read the labels, meaning that the positioning of certain fields needs to be exact.  Making 160 labels isn’t my job.  That’s actually my boss’s job.  My job is to make the standard label, and make it as easy and fast as possible to take that label and modify it for a customer.

Getting the barcodes to show up correctly has been a bit of a headache.  The default 128 font in Crystal Reports looks nice, but can’t be read by our scanners.  Eventually I found a font on the internet called barcodewiz.  It took me a while to get that to work.  For a while it would only work for one of my test values, but I finally discovered that I needed to change my font size from 24 to 23.  Seriously, who uses font size 23?

Our labels, regardless of customer, all come from the same ~25 fields.  So what I did was create a formula for each field.  About half of the fields are standalone fields in the database, the others are actually stuffed into a single database column that can be parsed as xml, ie:

<AdditionalFields>

      <AdditionalFieldsRow>

            <CustItem>P51C Sea Salt CanadaCA</CustItem>

            <CustPO>2395</CustPO>…

And so I have crystal report parse them out.

Today I started working on a new formula to replicate the quantity string.  You see, based on the sales unit of measure, we might want Feet, Units, or Lbs show up on the label, or any combination of the three.  And so I learned about nested ifs, using formulas in formulas, and a variety of other things.  Here’s my formula, for your reference:

local stringVar SubFieldStart;
local stringVar SubFieldEnd;
local StringVar RightSubstring;
local StringVar SubField;
local StringVar SalesUOM;
local StringVar InventUOM;
local StringVar Combined;

SubField := “SalesUOM”;

SubFieldStart := “<” + SubField + “>”;
SubFieldEnd := “</” + SubField + “>”;

if InStrRev ({Command.MisData}, SubFieldStart) >0 and InStrRev ({Command.MisData}, SubFieldEnd) >0
then
RightSubstring := Right ({Command.MisData}, Length ({Command.MisData}) – InStrRev ({Command.MisData}, SubFieldStart) – length (SubFieldStart) + 1)
else
RightSubstring := “”;

if (RightSubstring <> “”)
then
SalesUOM := Left(RightSubstring, InStrRev(RightSubstring, SubFieldEnd) – 1);

Combined := “Qty: “;
if length(SalesUOM) > 0 then
(
InventUoM := right(SalesUOM, length(SalesUOM) – instrrev(SalesUOM, ‘_’));
SalesUOM := left(SalesUOM, instrrev(SalesUOM, ‘_’) – 1);

//This next part is adapted from Ibis_ShopFlorLabels.getQuantityStr
if (SalesUOM = “”) or (SalesUOM = “lb”) or (SalesUOM = “lbs”) then
(
if (inventUOM = “lbs”) then
Combined := Combined & “LBS: ” & {@lbs} & ” Feet: ” & {@feet}
else
Combined := Combined & “Each: ” & {@each};
);

if (SalesUOM = ‘thou’) or (salesUom = ‘each’) then
Combined := Combined & “Each: ” & {@each};

if (SalesUOM = ‘mbag’) or (salesUom = ‘bag’) then
Combined := Combined & “Bag: ” & {@each};

//mft and msi seem obsolete, but original code displayed these
if (SalesUOM = ‘mft’) or (SalesUOM = ‘msi’) or (salesUom = ‘feet’) then
Combined := Combined & “Feet: ” & {@feet} & ” LBS: ” & {@lbs};

if (SalesUOM = ‘mimp’) then
Combined := Combined & “Imp: ” & {@each} & ” Feet: ” & {@feet};

//Original code displayed roll, but seems silly if it’s always 1
if (SalesUOM = “Roll”) then
(
if (inventUOM = “lbs”) then
Combined := Combined & “LBS: ” & {@lbs} & ” Feet: ” & {@feet}
else
Combined := Combined & “Feet: ” & {@feet} & ” LBS: ” & {@lbs};
);

if (SalesUOM = ‘mtr’) then
Combined := Combined & “Meters: ” & {@feet} * 0.3048 & ” LBS: ” & {@lbs};

//kit, Hour, box, case, packet not supported at this time.
if (Combined = “Qty: “) then
Combined := Combined & “Feet: ” & {@feet} & ” Each: ” & {@Each} & ” LBS: ” & {@lbs};

);
Combined;

I sat back and looked at it.  Job well done, I thought… but that green text, my own comments, were glaring me in the face.  I didn’t have the foggiest how I was going to do some of these units, but it’s almost certain that in the future, this formula would need to change.  I don’t look forward to making the same change to 160 labels!  What was I going to do?

After a lengthy web search gave me nothing, I finally read the application’s help documentation and came across something called a custom function.  Unlike Formulas which query the database, Functions have parameters.  The  first thing I did was create a function to extract a data value from the MISData field (the field with all the xml nodes).  After using this to simplify the standard report, I went into another report and couldn’t find my custom function for it.  Turns out I had to add the custom function to the Repository (which provides source management).

But when I tried to log into the repository, I couldn’t no matter what I tried.  Looks like there is in fact nothing to log into.  You see, the repository is stored in a database and we don’t have Crystal Reports Server installed.  Crystal Reports Server is a separate product, and it costs twice as much as Crystal Reports.  I’m going to have to pitch this to my boss and see what he thinks of it.