Contents for September 2009:

** Do-It-Yourself license audit from SAP
** Master topic index for my guides
** My library of Crystal Reports materials
** Rundown on 3rd party products
** Save 25% on CR Chart
** PeachTree finally allows SQL (OLEDB)
** Learn Crystal Reports (your place or mine)
** Cross-Tabs with multiple initial columns
** Date parameters in a Command with DateTime fields
** Other recent blog articles:
    The 8-minute Crystal Reports program startup
    Using Business Objects Query Builder
    VB Express editions don't support CR integration

Do-It-Yourself license audit from SAP

A customer of mine received an Email from SAP telling him that:

"… many of our customers have requested assistance on verifying that their license
quantities of our products are correct."

It then instructed him to go to a  "license confirmation site" and submit a license confirmation form before a specified date.  The site asks you to enter the number of CAL and NUL licenses you have for each SAP server product you own.  Then you are asked to enter how many cores you have in your processors (each core is licensed separately).  This particular customer simply ignored the message because his SAP implementation was canceled, but it still left a bad taste in his mouth. If this is in response to people requesting assistance, why is there a deadline?

The message is vague about consequences for missing the deadline or ignoring the message.  My guess is that there aren't any direct consequences.  BO may decide to do their own software audit, but SAP is already entitled to audit your software at any time (see your EULA). The difference is that those audit's are at SAP's expense, which means SAP would want some indication of abuse before they send someone out.  Maybe this message is a way to generate leads.

If you have experience or opinion on this DIY audit, I would like to hear from you.


Master topic index for my guides

I recently published Volume 4 of my Expert Techniques for Crystal Reports, adding another 30 articles to my growing collection of tips and tricks.  But giving a title to an individual articles is always a challenge, because each technique can be used to solve several different problems. My title may not sound applicable to the user who is searching for a specific solution. 

So say a user needs to know how to make a dotted line that automatically fits between two columns on a page – like you might see in a table of contents between the chapter and the page number.  He might search for dotted lines, or maybe "table of contents", or maybe TOC, etc.  Up to now I couldn't think of an easy way to list ALL of the options for an article's subject.  Then one of my students asked me if I had an index for my course materials – and the light bulb went on.  So I have started to compile a master index for all of my guides.  The beauty of the index is that I can list the same article under any number of headings or descriptions, making it more likely that a user will find what they search for.

The current version of this index includes all four volumes of my Expert Techniques, and my Expert's Guide to Formulas.  Over the next few months I will try to add more guides until the index covers all of my materials.  The index is available as an HTML page and also as a PDF that you can download to use off-line.  Your feedback is welcome.


My complete library of Crystal Reports materials:

Let a master teacher help you understand these Crystal Topics.  Each guide comes with clear explanations and sample files to illustrate each concept.

    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.


Rundown on 3rd party products

When I started my newsletter in 2001 one of my first issues focused on Crystal Reports viewer programs.  And because I think this topic is important, I launched my blog 3 years ago with post #1 about viewers.  I write about them regularly and yet I still find people who don't know about viewers, or who think that all viewers are as useless as the 'official' viewer put out by SAP.  Some of these viewers do really cool things and most are inexpensive (or free). So if you have users who need to preview, refresh, print or export reports without having CR installed, you should check out the "viewers" section of my LINKS page.

This LINKS page also contains other Crystal-related utilities, 50 products in all.  It is one of the most popular pages on my site.  Here are some of the other things you can do using these tools:

Email each page of a report to a different person.
Automatically run reports at 2am every morning and Email the output.
Run reports from a DOS batch file.
Hold a report in preview mode while it refreshes itself ever 30 seconds.
Set a report to repeatedly test for exceptions and automatically generate alerts.
Create a web portal where users can launch reports from a browser.

Filter the data in the report based on who is running it
View the differences between 2 reports
List all reports that use a specific table/field/formula/parameter
Encrypt a report so it can't be opened in CR, but can still be run by the user.

Calculate a value in one report and use that value in a separate report, tomorrow.
Update a database value with a value calculated by a report.
Include system info in the report (User, PC Name, INI or registry values, etc)

Export each page of a report to a separate tab in a spreadsheet.
Export each page of a report to a separate PDF file.
Export to PDFs with a bookmark tree.
Export to ODBC and append the records to an existing table

If you have use for any of these features, and you want to know which tools are best for a task, then give me a call.

Save 25% on CR Chart

If the chart feature in Crystal Reports don't quite do what you want, you might want to upgrade your charting engine to CR Chart. CR Chart is sold by Three D Graphics. And since Three D Graphics makes the current Crystal Reports charting engine, you know that the upgrade is fully compatible. You just replace one DLL with an upgraded DLL.

So what does CR Chart allow you to do? You get to use new chart types that aren't currently in CR, like BoxPlots, Pareto Charts and Waterfall Charts. You can change chart properties dynamically at runtime and even add programmable reference lines and labels to your charts.

And as a special offer to my readers, you can save 25% on your purchase if you buy before October 31st. Just use the promo code CK4EWV to get the discount.  Or, if you would like to try it first, you can use the the trial version which is just like the original version but with a visible watermark over the chart images.

The cost of CR Chart is $499 (before the discount) which includes one developers license and a royalty free runtime and deployment license.  See my LINKS page for a direct link to CR Chart information.


PeachTree finally allows SQL (OLEDB)

Creating Crystal Reports from PeachTree accounting data has become a specialty of mine.  But getting data out of PeachTree has always been a challenge because PeachTree has always used a proprietary Btrieve query engine.  But I just helped a customer upgrade their reports for PeachTree 2010 and was pleased to see that this latest version of PeachTree now uses OLEDB for Crystal Reports connections.

Since OLEDB is SQL based we should now be able to use Commands and SQL Expressions with PeachTree data. If you are doing this same upgrade there are some steps you need to take to get things working in OLEDB.  I also found that older versions of CR didn't generate the correct SQL syntax in some cases (like date ranges).  But CR 2008, which is sold with PeachTree 2010, seems to work just fine.

So if you need help creating or updating your Crystal Reports for PeachTree, (or any other accounting system) give me a call.


Learn Crystal Reports (without needing a bailout)

Even with budgets tight there is no need to miss out on Crystal Reports training. You still need information to do your job, so stop struggling with Crystal Reports and learn what it can do. The most cost-effective way to be taught Crystal Reports is in my individual training program.  It is ideal for people who:

   Can't take 2 days off for a regular class.
   Want to learn in their own database.
   Need to cover only a few topics
   Want to learn from someone who has taught 2,500 satisfied students.

Remember, the cost of a typical 2-day Crystal class is enough to buy 7 hours of individual instruction time with me.  You can start with a purchase of only 2 hours and get my course materials with exercises for free.  Do as much work as you like on your own and use your prepaid time to work with me by phone and remote connection when needed. We can review lessons, discuss questions or even troubleshoot existing reports.  For more details see the "Individual Training" page on my web site.

Or, if you want to schedule a class at your office, using your data, I can save you money there as well.  I have discovered some top-notch instructors all over the US, UK and Canada and use them to deliver my class at a very competitive price.  Call for more details.


Cross-Tabs with multiple initial columns

Here is a simple solution to a challenge that came up today from one of my customers.  It has been kicking around in my head for a while but I finally had an excuse to play with it and test it.  I figured some of you might also find it helpful.

If you are like me, you find many uses for cross-tabs.  Say you a have a cross-tab by Customer (row) and by Month (column).  But you want each Customer row to start with three columns of identifying information rather than just the normal one column.  You could combine the three fields in a formula but then they don't always align well as columns.  Today I realized that there is a simple way to get true columns, and it works in all versions at least back to v8.5.

So if in the cross-tab I described above I want the row to start with the Customer ID, the Customer Name and the State.  I want each field in separate cells.  All I do is add these three fields as row fields in the Cross-Tab Expert.  Then I go to the Customize Style tab and highlight the top two fields and check off the box that says "Suppress Subtotal".  That puts all three fields on one row to the left of the total columns.

Just make sure that each Customer ID has only one Customer Name and one State in the data.  If a Customer ID can have multiple values for these other fields you will get a separate row for each value.  But assuming the Customer ID is tied to one Customer Name and one State, then there will only be one row for the customer and it will have three cells before it starts the cross-tab analysis.

And remember, if you like to learn techniques like these, there are 120 similar articles in my Expert Techniques series, mentioned above.

 

Date parameters in a Command with DateTime fields

I found some clever SQL that allows you to strip the Time off of a DateTime in a SQL Server Command.  This has several uses but it makes it simple to use normal Date parameters with DateTime fields and not having to worry about losing the last day due to the times being after midnight.    The SQL would look like this:

DateAdd( day, datediff( day , 0 , YourDateTimeValue) , 0)

One link that describes this method also adds some common date calculations in SQL using similar calculations.

If you need some guidance when it comes to using SQL expressions or commands in your reports, you should get my Expert's Guide to SQL Expressions, Options and Commands.

Other recent blog articles:

    The 8-minute Crystal Reports program startup
    Using Business Objects Query Builder
    VB Express editions don't support CR integration