Contents for November 2012:

** Annual review of Crystal Reports formula UFLs
** Accurate totals in a Many-to-Many situation
** My library of Crystal Reports materials
** The third way to split a section
** Doing a UNION across databases
** Crystal Reports and QuickBooks
** Let me create your Crystal Reports
** New Features in Find It EZ
** DataLink Viewer adds a Visualizer
** Open enrollment Crystal classes in Frederick, MD
** A new way to Tweet report data
** Another free PDF generating printer driver

Gems from the Archives:
How to print "continued on NEXT page" (April 2002)

 Annual review of Crystal Reports formula UFLs (2012)

Time for my annual review and comparison of User Function Libraries(or UFLs), and this year I have added a new one, which is actually a very old one. It has been hiding at the bottom of my LINKS page for a decade and you can read about it here. These are DLL files that add new formula functions to Crystal Reports. With these new functions your formulas can do some pretty amazing things like:

1. Carry values from today's report to tomorrow's, or from one report to another.
2. Append lines to an external text file.
3. Automatically copy a value to the clipboard.
4. Check the user name of the user running the report.
5. See if a file or folder exists – either on your network or on the Internet.
6. Rename/copy/delete a file on your hard drive or network drive.
7. Launch an application or run a batch file.
8. Execute a SQL statement (Select/Insert/Delete).
9. Send an Email using information in the report.
10. Create a table of contents or an index for your report.
11. Calculate distances between zip codes or long./lat. coordinates.

If this sounds interesting you can read my complete comparison including a list of all the functions provided by each DLL. The five sources are:

Bjarke Viksoe (U2lwin32)
Maginus Software (CRUFLMAG)
Millet Software (Cut Light)
Chelsea Tech (File Mgt, Text, Share and others)
CrystalKiwi (Export, Table of Contents) 

Accurate totals in a Many-to-Many situation

While working with a customer I found a new way of totaling records when there is a Many-to-Many relationship. This method doesn't use variables or subreports so it is quicker and simpler than other methods.

Example using the Xtreme data:

A customer linked to orders creates a One-to-Many relationship (one customer to many orders). A customer with orders who also gets credits has two different One-to-Many relationships. Combine those 3 tables in one report and you get a Many-to-Many relationship. This is because there is no connection between an order and a credit.

If I only needed to total one of these (orders or credits) it would not be difficult. If you have taken my Advanced class, or if you have read my Expert's Guide to Totals, you might recognize this first method.

1) Group the duplicates together (group by customer ID then order ID).
2) Create a Running Total field that totals the order amount.
3) Set the 'Evaluate' of the running total to 'on change of group 2'.

But this only works when the duplicates are grouped together, which means you can only use this to solve one side of the problem, not both at the same time.

But I recently realized that when you group by customer and then by order, each order group of duplicates will contain exactly one set of credits. So, if you take the total credits from the first (or last) order of each customer you can also get a total of credits without duplicates.

So I created a summary field that calculates the first (minimum) order number of each customer. Then I created a running total of the credit amount and set the evaluate to be a condition formula:

{Orders.Order ID} = Minimum ({Orders.Order ID},{Customer.Customer ID})

Now the only Credits that get included in the credit running total are from the first order group within each customer group. This eliminates all the duplicate credits from the total without disrupting the original running total for the orders.

If you have a similar Many-to-Many situation and need help deploying a method like this, give me a call.

My complete library of Crystal Reports materials:

Do you know how to use SQL to speed up your reports? Do you know how to use cross-tabs to quickly recap the data in a report? Why not let me explain these Crystal Topics to you with one of my Expert's Guides. Each guide comes with clear explanations and sample files to illustrate the concepts.

Expert's Guide to Formulas ($36)
Expert's Guide to Subreports, Parameters and Alerts ($28)
Expert's Guide to SQL Expressions, Options and Commands ($26)
Expert's Guide to Totals ($24)
Expert's Guide to Cross-Tabs ($22)
Expert Techniques Vol. 1 - 4 ($19 each)
Quick Reference to Crystal Reports in Visual Basic ($16)
Quick Reference to Crystal Reports in .NET ($14)

You will find these on the LIBRARY page of my site.

The third way to split a section:

Most users know that you can split a section in Crystal to get subsections like Report Header a and Report Header b. The standard ways to create subsections are to:

A) Use the section expert and hit the INSERT button at the top.
B) Right-click on a section name and select "Insert Section Below".

But not everyone knows the third way, which is the one that I find the most useful. It is especially useful when you need to put some more space in the middle of a large section.

Say you are working on a full page form and the section has lots of objects in it. The section contains fields and labels, lines and boxes, etc. Now say you need to make some new space in the middle of the form. Using the standard ways mentioned above you would have to create a new subsection (or expand the existing section) and then move half of the objects to their new positions. If you have ever had to do this you know how easy it is to mess up the alignment when you move a large number of objects.

So try this instead. Go into design mode and put your cursor along the right edge of the left side ruler. You should see a hash mark appear along the edge of the ruler with arrows pointing up and down. If you click at that point and drag the marker up and down just a bit you will see a line appear across the section. Place that line where you want to split the section and let go of the mouse. Your section will split in two along that line. Any fields that are above the line will stay in the top half. Fields that are bisected by the line will also stay in the top half. All of the other fields will go in the bottom section. Once you split the section in the middle you can add space by moving the new bottom border down as much as needed.

And if you don't want the split to be permanent, you can right click on name of the new upper section and select "merge section below". This puts the sections back together but maintains any space that you added while they were split.

Some users discover this by accident when they are trying to change the size of a section. This happens because people who want to change the size of a section often think the best place to do this is along the edge of the ruler. This is actually the worst place to try and size the section because the sizing indicator looks very much like the divide indicator described above. It is very easy to be off a bit when you try to expand a section, and end up dividing it in two. Instead, I recommend that you grab the bottom of a section somewhere away from the ruler, and also away from any fields. That way if you are not squarely on the border it will be obvious from your cursor.

Doing a UNION across databases:

I was working with a customer today who does an annual archive of their database. They backup the database and then delete all transactions that are over a year old form the current database. So, to do a multi-year report they need to combine data from different instances. They restore a prior year instance whenever they want to look at old transactions. They asked me if there was an easy way to combine transactions from two separate instances into one report.

Crystal's linking window is great for linking tables but not so good for appending. So this is one of the main reasons I end up using SQL commands – to use UNION ALL to append the records of one table to another. But I wasn't sure if I could do a UNION with tables from two different database instances. Fortunately they were on the same server which made things easier. Linking separate servers takes a bit more work.

So I wrote a simple query for one instance, pasted it in twice and put UNION ALL between them. Then I tweaked the second query to use the name of a different instance:

SELECT CAL.ONDATE, CAL.RECTYPE, CAL.DURATION
FROM GM6.dbo.CAL CAL

UNION ALL

SELECT CAL.ONDATE, CAL.RECTYPE, CAL.DURATION
FROM GM62011.dbo.CAL CAL

This worked fine. Then the customer asked how they could change the names of the backup instances when they wanted a different year. Rather than make theme go into the SQL to change the names, I made it into a parameter. I added two parameters to the command and put the parameters in place of the database instance names in the SQL. Now the user is prompted for the instances at runtime. I never had occasion to deploy a parameter like this, but I wrote about a similar idea for selecting tables in an older blog post. As you can see, you can replace ANY word in a SQL Command with a command parameter. This includes table names, join types, and fields as well as the literal values used for filtering.

If you have a project like this and you need some help, give me a call.

Let me create your Crystal Reports:

There aren't many people who know Crystal Reports better than I do. It is what I do all day, every day. So if you need a tough report created you can always call in an expert. Let me show you how I can combine the techniques I write about to create the reports you need - even the ones that "can't be done". And since I am a teacher by nature, I am happy to explain to you how the techniques work together.

I can also review existing reports that break, or run slowly, or seem overly complex. Let me have a look at them and see if there is a more elegant solution.

New Features in Find It EZ:

The developers at Find it EZ have been busy recently – adding new features to their code searching software. Find it EZ already had a solid search function that lets you search a folder of reports and find virtually anything in any part of the report. Recently they have been refining their Report Definition document so that it is more complete and flexible. A Report Definition document summarizes all of the key objects and features in an RPT file. In addition to all the basic features it now includes the Tables and Joins, SQL Commands and SQL Expressions, Images, Charts, Cross-tabs and even lines and boxes. So it is about as thorough as it can be. And by setting search filters you can focus the output on the features you need to see.

The developers have also been working on a way to let users see the SQL query that is automatically generated by the Crystal Reports Engine. Up to now the only way to generate the SQL for a report has been to log the report into the database. Find it EZ has developed a way to generate equivalent SQL for any RPT without requiring a database connection. While the SQL may not be exactly the same as that generated by the report, it is equivalent ANSI standard SQL.

So if you are looking for a tool to help you manage a quantity of reports, your first step should be this article. It compares the available features for all of the tools in this category and gives a capsule description of each. But these new features are not included in the matrix yet. If you think these features are useful, then you should download the free trial of Find it EZ.

DataLink Viewer adds a Visualizer:

For those of you who have a large amount of data to analyze, Millet Software may have something worth trying. They have added an integrated data visualization tool into the DataLink Viewer. This allows you to explore the data by creating advanced charts – going beyond what Crystal normally provides. The tool will automatically read through your detail report data (including formulas) and loads each column into an appropriate category. It extracts the numbers as "measures", the dates as a possible grouping hierarchy and all other columns as potential analysis attributes. From there you can drag and drop the fields to see where patterns emerge.

Because it is easier to show these features than to describe them, Ido has published a video demo showing just what this tool can do. Try it out and let me know what you think.

Open enrollment Crystal classes in Frederick, MD:

Stop struggling with Crystal Reports and learn how to use it fully. Come join me in one of my January classes and learn how to make Crystal work for you. My Intro class makes sure you know all of the basics. We even include material on cross-tabs, charts and formulas. The Advanced class shows you how to solve reporting problems with running totals, subreports, parameters and commands. The material is good for any version. See my web site for course outlines and dates.

So what makes my classes different? I have written my own course materials and have used them to teach over 2,500 satisfied students. And, I give you a toll-free number so you can call me after class with questions at no charge.

Or, if you want to schedule a class at your office, using your data, that is my specialty. I have found some stellar instructors in the the US, Canada, the UK and Australia that deliver my class for a very competitive price. They are based near Spokane/Seattle, LA, Omaha, Detroit, Tampa, Philadelphia, New York City, Boston, Vancouver, Montreal, Durham(UK) and Adelaide (AU). Call for details.

A new way to Tweet report data:

I recently wrote about a DLL that can post a tweet directly to your twitter account. There are also some desktop scheduling tools and server-based scheduling tools that can post messages directly to twitter. These are all in the $1000 and up price range. Now there is a third option to consider.

Ido Millet of Millet software recently wrote up a case study (see page 41 of the Visual Cut user manual). It shows how the Santa Monica Fire Department uses Visual CUT to tweet calls for service as they entered into their database. A report is scheduled to run automatically every minute and look for any newly-created records. Visual CUT sends an email for each record found to a free "email to twitter" service provided by Twitter Counter. Visual CUT also exports these records to another table which is used to track the incidents that have already been tweeted. This prevents duplicates and is also used to determine if an incident has been cleared so that an all clear Tweet can be sent.

Any tool that can generate Emails from Crystal can be used in the same way. However, Ido suggests that it is important to be able to eliminate duplicate Tweets. Selecting by time ranges, like the last 5 minutes, is not a 100% reliable approach. A slight delay in launch time or a temporary outage will mean missed records or duplicates. Visual CUT has several features that are specifically designed to avoid duplicate processing.

So, if you have any questions about your options for deploying a report-to-Twitter process, drop me a line.

Another free PDF generating printer driver:

I have written before about 2 free tools that allow you to generate PDFs simply by printing your report – or virtually any printable document. I recently learned about a third free tool offered by the folks at pdfconverter.com. They offer a completely free version of their commercial PDF creation software. It does everything that the other 2 tools do and it runs on 8 operating systems. The most surprising part is that it also comes with free support. I kept looking for a catch but I haven't found one yet. It puts no watermarks or advertising into the PDF and has no time limit. The only advertising I saw was an upgrade panel that appears next to your print options.

So if you are looking for a way to print out PDF documents, it is worth a try.

Gems from the archives:

How to print "continued on NEXT page" (April 2002)

Contact Information: