PeachTree Accounting, now known as Sage Accounting, has a long history of using CR as a reporting engine for custom reports.  Sage currently comes with some standard Crystal Reports already written for you, and makes them available directly from the Sage menu.  But according to an Email forwarded to me by one of my customers, this is about to change.  Sage 50 2015 will no longer come with a version of Crystal reports. Instead Sage is providing an Excel based reporting tool called Sage 50 Business Intelligence.  You will still be able to use Crystal if you purchase it yourself, or if you already have a copy from previous version of Sage.  You can read the details in this CBA Blog post.

One concern was that Sage would stop supporting their custom functions.  These are necessary for things like Quantity on Hand and Cost of Goods. I have written about some of these in the past.  But Dave Smith at Phase One Computing Services was able to confirm that Sage is going to continue to provide an option to install these functions for users who still want to use Crystal.  See the screenshot below from the 2015 version of Sage.

This screen and password process was added in 2010 according to Goldstar Software.

One of my customers had adapted a report template that they found online to generate a calendar report.  But they weren’t happy with the reports performance.  When I looked at it I found that the template ran a subreport for every day so there wasn’t much I could do to speed it up. They allowed me to try a different approach to creating the calendar. The result was the planning calendar I wrote about earlier this month.  After giving it some thought I figured I could adapt that report to create a more traditional daily appointment style calendar, something like this:

I wanted to create it without using subreports.  I also wanted to eliminate the need for a table containing all dates, which needs to be maintained.

I was able to eliminate the “Dates” table by using a 15-record inflation table with records numbered 1 to 15.  This allows the report to project each record into the next 15 weeks.  So as long as you have at last one record every 15 weeks you get a complete calendar.  The number 15 is arbitrary and can be adjusted up or down depending on how large of a gap you expect to encounter in your data. If you have at least one event every week you don’t need the inflation table at all.

And I did find a way to eliminate the subreports by using variables.  The sample above is from the Xtreme database.  I have posted the report on line for you to download.  It uses feeder formulas instead of referring directly to the database fields. This makes it relatively simple to point the report to the tables/fields in the your own database.

If you find it helpful, drop me a line.  Or, if you need help deploying the report, call to schedule a short consult.

Hidden in the advanced formatting options for numbers are several ways of displaying zero values on your reports.  To find these options, right click on a numeric field and select “Format Field”. Then click on the “Number” tab and finally click on the “Customize” button at the bottom of that screen.

One option is to suppress the zeros completely so they show up as blanks.  To do this there is a checkmark in the upper left.

Another option is to display something specific instead of zeros and this is found in the lower right of the same screen.  You will see a drop-down under the heading “Show zero values as”.  Here you can keep the default behavior or use another value, like a dash or a zero.  I wondered what the difference was between Default and the zero and it took some testing to figure it out. The default might show decimals, like 0.00 or 0.0000 but if you set the default to zero the decimals won’t show.

But the real secret of this last setting is that it is one of the only drop-down lists in CR where you can type your own value.  In other words you can type whatever you want to show as the zero value, like “n/a”, or “Pending”.  This is similar to using a “Display String” (on the common tab) but without the need to write a formula.

Creating a calendar in Crystal Reports poses some special challenges. There may not be an event for every day or every week, so a method is needed for filling in the gaps. And there may be a long list of events on any given day which means finding a way to stretch the boxes. But a recent request form a customer forced me to find a completely different approach. They showed me a calendar for multi-day events that they were creating in a scheduling application. It looked like the (intentionally blurry) image below.

My first reaction was that this isn’t the kind of report that would work in CR.   There were 6 key challenges:

1) The colored box for each event stretches from the event start date to the event end date.
2) Events that cross weeks need a box for each week with the same text in each box.
3) The box must grow vertically so there is room for all the text of the box, which means short events will have taller boxes.
4) Events in the same week should make room for each other, although they can share the same row if they both fit.
5) The box color is defined by the type of event and can be specified by the user.
6) Part of the text in the box needs to be in bold.

But the customer was willing to invest some time and asked me to see how close I could come.  After a few hours, a couple of arrays, some looping logic, a quick review of twips, several fun date formulas and a cross-join …. I had it.  My initial POC uses the orders table from the Xtreme sample database and  I have posted the report on-line, along with two variations, for you to download.  The reports uses feeder formulas instead of referring directly to the database fields. This makes it relatively simple to point the tables/fields to your own database.

If you find them helpful, drop me a line.  Or, if you need help deploying a report, call to schedule a short consult.