Say you have several columns of numbers that get added together like Price, Tax and Shipping. At the end of a customer group you would have three subtotals. You might decide to write a formula that adds those three subtotals together to get the combined total for the customer. It would work fine but there is usually a more efficient way to do this. Instead of writing a formula that combines the subtotals I would write a formula that combines the fields at the detail level. It would look like this: Continue Reading »
A total of a formula instead of a formula of totals.

This week I had a customer who was having trouble getting some version 8.5 reports to run on another another computer using CR v11.  The report wouldn’t connect to a new data source consistently.  So I gave him the following steps that seemed to clear up the problem. Here they are in case someone else has a similar situation.

Any time you need to run a v8.5 report in a later version of CR the first step is to change the version of the RPT.  To do this open the old report Continue Reading »
Running v8.5 reports in later versions of CR

Last night I had a customer with a strange problem.  Their report ran fine and looked great in preview and when printed.  But if it was exported to PDF it had a long line down the page.  This didn’t happen if it was printed to a PDF driver so the only time it was visible was when using the CR export function.

I opened the PDF and there was a vertical line on every page, even on blank pages, starting near the top.   I opened the RPT and there was no visible object anywhere near the sections at the top where the object should be sitting.  I was getting ready to start deleting objects one at a time to see if it was tied to a specific object, but first I decided to Continue Reading »
Mystery line in PDFs

I have had several instances recently where a change to a report caused Crystal to generate strange SQL.  Sometimes you can see the problem under “Database > Set Location” where you will notice two different instances of the same database in the upper window.  Or, you may notice that your SQL Query gets separated into two or three independent queries when it should all be one query.  In a few cases the SQL gets so bad that it generates an error from the database, or it generates a “Cartesian product” result, trying to return all combinations of records between the tables.

If you run into a situation like this here are the steps that I have found that help:

1) Switch to design mode so that you don’t launch a query with every change.

2) Go into “Database >Set Location” Continue Reading »
Reseting SQL Errors

Have you ever wondered why Crystal allows an export to “RPT” format?   Since the file is already in RPT format it might seem like a redundant option.  But last week I did a consult and was told that the option of exporting to RPT format was the most valuable information in the entire consult.

So what do you get when you export to RPT format?  You get an RPT file that is saved with the preview data.  While most of you won’t have any trouble creating an RPT saved with data, some users aren’t so lucky.  These are users who can only preview a report within an application like a web or server based app.  For these folks, troubleshooting a report is an endless loop of making a change in design mode, uploading the modified report, running the report through the app to see if the change worked,  then going back to design mode.  I have had Continue Reading »
Exporting to RPT format

The Distinct Count summary function in Crystal Reports is pretty handy.  It allows you to count the number of unique values in a column.  It can eliminate duplicates from the count, even when the duplicates are NOT consecutive.

One of my customers wanted to do the same type of summary in an Excel spreadsheet.  We were both surprised that there was no function like this in Excel.  But after a few Google searches I came up with a workaround that lets an Excel formula do the same thing as the Distinct Count summary function in Crystal:

=SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))

You replace the three sample ranges “A1: A99″ with  whatever your data range is.  I am not sure I could explain why it works, but it does.

Ido Millet of Millet Software has added another powerful feature to an already feature rich product called Visual Cut.  Users now have the ability to automatically generate an Excel Pivot Table based on an exported (Data Only) excel worksheet.  If you use pivot tables then you might want to see this feature in action.  Ido has produced a video that shows how it works.

If you want to compare the features of Visual Cut to similar products you can read my comparison.

Or, you can try Visual Cut for free by sending an Email request.  Millet Software will respond with a download link and instructions.  Please let them know that you have read about Visual Cut in my Blog.

I recently found a way to mix several concepts and solve a problem that others might be facing. The challenge came from a company who stores several hundred thousand log transactions each day. The way the system is set up, each day’s transactions are stored in a separate SQL table. The table name is the date of the transaction.  What they wanted to do was to run reports that summarize these transactions for an entire month, with subtotals by category. This would require combining a month’s worth of daily tables on the fly, and then getting subtotals by category.  At first I recommended a stored procedure because I didn’t think we could calculate the table names on the fly in a SQL command.  But we aren’t allowed to add SPs to the database.  Then I realized Continue Reading »
Case study for UNION of daily files

This month an unusual number of customers have brought me errors to troubleshoot.  Most of them involve errors outside of Crystal with things like missing DLL files or installs that seem the same but that behave differently.  So I was pleasantly surprised to find a recent blog post on the SAP web site that provides a list of free tools for helping to troubleshoot things like this.  There are 5 tools listed but I think two would apply most to the problems I faced this month:

1) Modules is a free utility from the SAP web site.  It allows you to run a report on two different systems (ie one that works and one that doesn’t) and find all the files that are different in those two environments.  It makes a list of the files used in each environment and then compares to see which are different. It is an old tool but worked fine in my Win7 64 environment.

2) Depends.exe (Dependency walker) is another free tool that takes an EXE or DLL file and lists all of the other files that it relies on.  This is perfect for when you have a DLL that won’t work or register correctly.  Often a missing dependent file is part of the problem.

And if you need to troubleshoot hangs and crashes, monitor HTTP / HTTPS traffic or monitor local file system and registry activity, the other tools should help.

Last week I wrote a post to complain that Crystal didn’t give you a way to directly control the format of dates along the bottom of a monthly bar chart. So, of course, this week I find something in Crystal Reports that I had never seen before, and which shows that I was only half correct.

A customer sent me a chart to troubleshoot and he had used a chart type called “Numeric Axis”.  This type of chart is down the list pretty far, below Radar charts and Bubble charts so I have never experimented with them.   But when I changed the chart to a normal bar chart it became obvious why this type was used.  The number series along the bottom of the chart had Continue Reading »
Formatting chart dates (part 2)