Contents for July 2012:
** Comparison of RPT management utilities-2012
** Another change to PeachTree database
** Beat the rate increase
** My library of Crystal Reports materials
** A Shared variable that refuses to work
** Slow performance caused by a "split" DSN
** Let me create your Crystal Reports
** Using 'Set Datasource Location' with a SQL command
** How CR adjusts date values for DateTime fields
** Open enrollment Crystal classes in Frederick, MD
** Compliance percentages in a cross-tab
** Old reports with 'new' features?
 

Comparison of RPT management utilities-2012

I have just updated my comparison of RPT management utilities for 2012. These are tools that allow you to scan, document, compare and update RPT files.

Most of the products have not changed much since last year.  The main exception is Find It EZ which has added many new features.   Report Miner is getting close to a major release and I will let you know when that happens.  The complete list of tools is below:

Report Miner by the Retsel Group
Find it EZ by Find it EZ Software Corp.
Report Analyzer by Cortex Systems
Crystal SpellChecker by Redeemed Software.
Search and Replace by Redeemed Software.
Mass Verify by Redeemed Software
.rpt Inspector 3 Professional Suite by Software Forces, LLC
 

Another change to PeachTree database

I create lots of reports for PeachTree accounting. This year Sage dropped the PeachTree name and is now calling their product Sage 50 Accounting. This change comes with the new (2013) release.

And as they did in their last upgrade, some fields went missing from the tables.  The first one I found is called PSQLCompletedDate and is (was) in the JrnlHdr table.  If you have been using this field you will probably need to replace it with CompletedDate, also in the JrnlHdr table.

This is a good example for the tools mentioned in the first article.  Four of those tools are able to go through a batch of reports and find all the ones that use a particular field. That allows you to fix the reports before they cause problems.
 

Beat the rate increase

My hourly consulting rate will be changing from $140 per hour to $150 per hour at the end of August.  It isn't a huge change but I wanted to give people some notice. The last change was in 2006 so it has been a while.  The change will be posted first on my web site late in August.  After that I will phase the new rate into my existing customers based on the when active projects wrap up.  So if you have a project that you have been waiting to complete, it might be good to get that started.  You can also purchase prepaid time at the current rate and that time is good for three years.  Contact me if you have questions.
 

My complete library of Crystal Reports materials:

Do you want to really understand Crystal formulas?  Do you know when to use the four different methods to add totals? 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.
 

A Shared variable that refuses to work

I had a real mystery with this one. I was trying to pass a shared variable from a subreport in Report Footer B to a formula in Report Footer C. I could see the value being stored correctly in the subreport, but the formula in RFC would not update no matter what I did. So I did some testing.

If I moved the original subreport to the RH, the variables started working.  So I knew that the formula syntax was right and the variable names were right.  I even tried renaming the variable to something unique to make sure there wasn't another formula using that variable name and interfering. The behavior didn't change.  

It was the customer that noticed some extra numbers in Report Footer A (the section above the subreport). What I found was a second copy of the display formula. There were two copies of the same formula sitting in different parts of the the Report Footer. One was in the section above the subreport and the other was in the section below the subreport. Deleting the formula above the subreport solved the problem.

This highlights a little know rule concerning formulas with variables.  They will only fire once per section, even if the section is divided into multiple subsections. So the first instance of the formula gave the variable its value before the subreport had a chance to run. The second instance of the same formula would not fire again to update the variable, even though a subreport occurred between the two formula instances.

So, just one more thing to watch out for. 
 

Slow performance caused by a "split" DSN

A report was taking a long time to get data from the database.  The report used the same table configuration as other reports that did not have performance problems.  But when going in to check the links I got a message that said the report was using two different data sources, which was strange.  So I went into "Set DataSource Location" and, sure enough, one of the tables was listed separately from the others, with a separate connection.  The funny thing was that the two connections seemed identical.  Same name, same properties, etc.

So first I took the lone table and tried to set it's location to the connection in the bottom window that had the same name.  That didn't change anything.  So then I took the connection for the other tables and set that location to the same connection.  That consolidated all of the tables into one connection.  The warning went away and performance returned to normal.

I am not sure what made CR consider the connections different.  My guess is that the user who created the report was different than the user that was logged in when the last table was added.  I have noticed that sometimes when I add a single table to an existing report that it treats it as a different connection. I have found that you can sometimes prevent this by connecting the existing report to the data source before adding the new table.  You can do this without even running the report by using "Database > Show SQL Query".  Once you have connected the report it is usually easier to identify the current connection and to bring the new table from that connection.  But the bottom line is that when you move over the table you should make sure that it is grouped with the other tables under the same connection.  If not, you can follow my steps above to bring them all back together.
 

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 why not leave it to an expert?  Let me show you how I can mix and match techniques to create the reports you need - even the ones that "can't be done".  And since I am also a teacher 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.
 

Using 'Set Datasource Location' with a SQL command

If your report uses tables, it is usually straight forward to point the report from one data source to another.  You use the menu items:

Database > Set DataSource Location.

However, this is not as simple when the report is based on a command, because with a command there is no existing table called 'command' for CR to find in the new connection.  The command only exists in CR. So here are two workarounds that you can try:

Option A:

    Highlight the command in the top half of the window
    Highlight "Add Command" under the connection in the bottom half.
    Click "Update" and an empty command box should open.
    Paste the new command into this box and click 'OK' to save the command.
    Check the command properties in the top window for the new connection.

If both old and new connections use ODBC you can try Option B:

    Make sure CR is closed and that the report is not in use by someone else.
    Open the ODBC Administrator
    Rename the current connection by adding an 'X' to the end of the name.
    Exit the ODBC Administrator and open the report.
    Click the menu items:  'Database > Show SQL Query'
    You should see a window listing all available ODBC connections.
    Select the new connection.
    Click 'Next' to log into the connection, then click 'Finish'.
    Check the command properties in the top window for the new connection.

If some of you have an environment where these methods don't work please send me the specifics of your connection and I will post a follow-up article.
 

How CR adjusts date values for DateTime fields

When people enter date criteria they expect to enter just the date without times.  But most modern databases use a DateTime data type rather than a date.  If your database stores dates with times you have to be sure that you enter your date ranges correctly or you take chance of losing the data for the last day of the range.  The good news is that in most situations CR will make the adjustment automatically, but I found one situation where the adjustment doesn't happen.  

Say you have a transaction date that is a DateTime field, and you are trying to select the transactions for January.  You might go into the select expert and  add a rule that says  TransactionDate / is between /

And then at this point you type in the two dates as  1/1/2012 and  1/31/2012.  Once you have run the report go into the record selection formula and look at what it says:

{Trans.Date}

in DateTime (2012, 01, 01, 00, 00, 00) 

to DateTime (2012, 01, 31, 00, 00, 00)

The last three zeros in each value are hour, minute and second.  Since you didn't specify times for the date time value it will assume that they are all zeros which means midnight.  This criteria will miss all of the records on 1/31 that have a time value that is after midnight, which is essentially the entire last day.  Now some databases don't store any time values, so all the dates are entered as midnight and there you would not have a problem.  But if you apply the above criteria to a database field that has times in it you will miss the last day of the range.   Go to the Database Menu and select Show SQL Query and you will see that it is using the same dates.

But now go back into the selection formula and change DateTime to Date in both places.  Then remove the last 3 zeros from each value so that the formula looks like this:

{Trans.Date} in Date(2012, 01, 01) to Date(2012, 01, 31)

Save the formula, and now look at the SQL Query.  The times are still zeros but you will notice that the second date in the query is one day greater than the date in your formula.  And if you look carefully the comparison used with that date is just the '<' and not the '<=' comparison.  In other words get all records that are before midnight on the day after the date you specify.

The same adjustment occurs if you use Date parameters, or a Date Range parameter in your selection formula.  When Crystal generates the WHERE clause it will add one day to the end date of the range, and use the '<' comparison.

But if you are using SQL Commands with date parameters you will need to make an adjustment to the SQL. I wrote about one option here where you remove the time value from the database field before you apply the criteria.   The alternative would be to add one day to the parameter in the SQL, and then use the '<' comparison the way CR does it.
 

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 August 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 and Canada 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 and Montreal.  Call for details. 
 

Compliance percentages in a cross-tab

I was asked to create a cross-tab that showed the percentage of assignments completed on time.  The row was a technician name and the column was the month.  After thinking through several more complex ways to do this, I found a simpler method.  I wrote a formula that said:

If {data.Ontime} = 'Y' then 100 else 0

I then set up the cross-tab with the date ("for each month") as the column and the tech as the row.  I used my formula as the summary field and used"average".  This gave me a quick cross-tab showing each technician's monthly on-time percentage.

There was only one complaint.  The cross-tab did not distinguish between a month with no assignments for that tech, and a month with assignments that were all done late by that tech.  Both would show up as 0%.  So I tweaked the on-time formula to be:

If {data.Ontime} = 'Y' then 100 else 0.0000000001

With this formula, a bunch of late records would average out to a very small number that would still display as a zero.  But, a month with no assignments at all would have a cell that was truly zero.  I then formatted the cells of the cross-tab to "suppress if zero".  Now if the value showed a zero it means records exists but none were on time.  However if there were no calls at all in that month for that tech, the cell would show empty.
 

Old reports with 'new' features?

Two different customers have contacted me recently because they were surprised to find a 'new' Crystal Reports feature in specific reports. They are usually puzzled as to why there is no way to make these features appear in ALL of their reports. Here are two examples:

   1) You go into File > Report Options and see an extra setting at the bottom that says "Convert DateTime Field:" and there are three choices: To Date, To Date-Time, To String.

   2) You go into the LINKS tab of the Database Expert and look at the properties of a join. There you see an extra section at the bottom with a check mark that says "Return all rows before joining" then below that you see:

When linking to two files from this file:
    Look up both at the same time
    Look up all of one and then all of the others    
    Look up all the combinations of the two files

Rather than being new features in CR, these are actually very old features from version 8.5.  These features were 'deprecated' long ago which means that they are no longer available in newer versions CR. But if you have an old v8.5 report and it uses one of these features you CR still knows what to do with those features and will show the features in the menu.  

However if you change the first feature setting to "convert to Date Time" and exit the menu, then the feature will be dropped from the menu.  The only way to get it back would be to close the report without saving it.  The same thing happens with the second setting if you take out the check mark for "Return all rows…".  In both cases there is no way to activate or reactivate that feature once you turn it off and save the report.

Losing the first feature isn't much of a problem because you can write an equivalent formula. The second feature was available only in reports that used a non-SQL connection. The setting helped you manage many-to-many joins.  This would be tougher to replicate in SQL.
 

Gems from the archives (November 2001 newsletter):

Forcing "table inflation" to create duplicate records
How to see the active DLLS used by CR

Contact Information: