The Crystal Reports Underground News - Volume 2015.05
an independent source for Crystal Reports Information
by Ken Hamady, MS
Contents for May 2015:
** Server-based scheduler comparison (2015)
** Comparing reporting tools
** My library of Crystal Reports materials
** Chart labels that include subtotals
** Testing a report that has to be run from application
** Let me create your Crystal Reports
** Harnessing the index, from 20 minutes to 8 Seconds
** Merging instances, another performance boost
** Open enrollment Crystal classes in Frederick, MD
** How to change the order of the links in Crystal
** Missing Sage custom functions
Other articles:
SAP systems vulnerable to cyber attacks?
Gems from the Archives
When Crystal tries to be "helpful" (and how to avoid it) (April 2009)
Alternate Method for Splitting a Section (April 2009)
Server-based scheduler comparison (2015)
I have just updated my
comparison of server-based scheduling tools for 2015. These tools are similar to the desktop-based scheduling tools I write about every March, but these are designed to be run on server. This allows multiple people to schedule reports for automated delivery by Email, FTP or network folder.
There are 9 products on the list this year with one new release. There are also a few feature updates and price changes since last year. The blog page provides a brief overview of each product. It also has a link to the feature matrix that compares roughly 70 features of these tools. There is even a feature glossary that defines all the terms. So if you need a short course in automating Crystal Reports delivery, this is a pretty good place to start.
Comparing reporting tools
In February I started an ambitious project. I expect it to hit critical mass in 6-12 months. I want to compare Crystal Reports to the other leading BI tools. I plan to include SSRS, MS Access, Tablaeu, QlikView, Indicee, Logi Ad Hoc, List and Label and a few others. The goal will be to help users understand how these tools are different and therefore which tool is best for a specific set of requirements.
One challenge is that these tools are very different in both purpose and approach. So my plan is to create a detailed feature matrix showing what each tool can do and also how it is different. The process and the end result will resemble the comparisons I do for third party products.
Another challenge is that I am not an expert in most of these tools. So, like I do in my other comparisons, I will rely on the people who know the tools best. Ideally the vendors will provide the information directly. One vendor already has. Vendors who want their software represented accurately have some incentive to participate. And when the vendor doesn't participate I will recruit competent users to review the feature list and mark the features supported by each product.
My job will be to tease out the features that best highlight the differences between products. I will also have to write up the feature definitions so that they are objective and meaningful.
Would you like to help? There are several ways to get involved:
1) Tell me the tools you think I should include, especially ones I didn't mention. That will help me prioritize products.
2) If you have expertise in any of these tools you can volunteer to review the feature list for that product.
3) Even if you have only limited experience with one of these tools, your impressions would be welcome.
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.
Chart labels that include subtotals
I learned a nifty trick from a customer this week. They had a bar chart with names underneath each bar. After each name was a number in parentheses, which was the quantity for that bar.
I was surprised that CR allowed a formula with a subtotal in it to be used as the 'on change of' in a chart. Then I tried it myself and confirmed that it works. So lets take a simple example from the Xtreme sample data that comes with CR. I created a simple report that was grouped by customer. I then added a subtotal that showed the number of orders for each customer.
Before I added the chart I created a formula called 'Chart Label' like this:
{Customer.Customer Name} & ' ('
& Totext (Count ({Orders.Order ID}, {Customer.Customer Name}),0) & ')'
Then I added a bar chart. I used the 'advanced' option so I could choose the 'Chart Label' formula as my 'on change of ' field. For the 'show values' field used Order ID and set the summary to 'count'. The chart then showed 6 customer bars and the label under each bar was the customer name, followed by the subtotal in parentheses.
The trick is that Crystal won't let us group on a formula that references a subtotal. And if you try to change a chart's 'on change of' field to a formula that uses a subtotal, you may not see it in the list. But the steps above gets the chart to work as described.
And thanks to Kendra Patterson at WRB Communications for sharing this technique.
Testing a report that has to be run from application
There are environments where the only way to test a report is to run it from within an application. The steps to deploy a modified report vary, but they usually involve placing the modified report into a specific folder and/or publishing the report into the application. Sometimes the users aren't clear on the steps. So when a user reports that a modified report returns the exact same result as the original, I have to wonder if they are actually still running the original. It may be that they missed a step when deploying the new report. Or it may be that the application still has a cached copy of the original report in memory and needs to be restarted to see the modified report.
The most reliable way to confirm that the report being run is the latest version is to mark the report with something obvious. For instance I often take a text object from the page header and underline it. If they run the report from the app and don't see the underlined object then they know that they are not deploying the updated version correctly. Most people start out thinking this test is a waste of time. But more often than not we find that there is some key step that they forgot. This simple step has saved hours of troubleshooting time.
And if you have to work regularly on reports like this, you should read my previous article on exporting to RPT format. That might allow you to bring data from the application back to the Crystal Reports designer so that you can immediately see the results of your design changes.
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.
Harnessing the index, from 20 minutes to 8 Seconds
My first call today had to do with a report that was working, but took 20 minutes to complete. Other similar reports ran in a few seconds so I was asked to find the difference. I noticed several added tables and a different table configuration which were likely places to start. But the one difference that looked most promising was the number of join lines between the tables. In the reports that ran quickly, every pair of tables had one additional join line between them. And in all cases the field being joined was called SystemID.
Apparently, this software package allows you to maintain the data for several completely independent 'systems' in the same database. This SystemID identifies the system of each record. The customer explained that since they only use one 'system' they didn't think the link on SystemID was essential. And the report did seem to generate accurate data without the SystemID link, if you didn't mind the wait.
I explained that the indexes that facilitate the joins between tables were all probably created with the SystemID as the primary key. So any join without a SystemId value couldn't tap into the index. Without the help of an index, the database would have to do a 'serial read' (record by record) to find the matching records. Think of finding a topic in a book without an index.
So we added the extra line for SystemID between every pair of tables and tested the report. The report ran in about 8 seconds. The lesson here is that even links that seem redundant can make a significant difference in the performance.
Merging instances, another performance boost
In the article above I wrote about report that took 20 minutes to run, and how using the right indexes brought the run time to under a minute. Yesterday I was able to get another 20-minute report to run in under a minute by fixing a different issue.
At first I wasn't sure if the run time could be significantly reduced. The report had to pull tables from two different databases, and that is usually a performance killer. So I checked the SQL being generated by the report to see how the two queries were being divided. Instead of two separate queries there were four. One of the two connections was showing up as 3 separate queries in the SQL – as if it were three different connections. So we went into the menu at "Database >> Set DataSource Location" and found that the report was using three different instances of same connection. Once we set all three instances to the same instance, the report ran in under a minute.
So why would tables that all come from one database connection end up under different instances of that connection? Usually I see this happen when the report is designed in stages. A few tables are added, then the user logs out and then more tables are added at a later time. Each new login can be treated as a separate instance of the database. And when that happens Crystal will make a separate query for each instance and combine the data in local memory. This is very inefficient when compared to a single query that is handled entirely by the database.
Having two different instances of the same DB causes the same performance problem as connecting two different databases. But while it is very difficult to improve performance with two different databases, merging multiple instances on one database is usually pretty simple.
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 July 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.
How to change the order of the links in Crystal
Crystal added a feature 7 years ago, and this week I had to use it for the first time. I was working with a Pervasive database and testing how different join configurations would perform. After one change the connection started throwing an error that the SQL was invalid. The configuration in the LINKS window looked identical to another report that ran fine. But when I compared the SQL of the two reports I found that although the tables were linked the same, the SQL statements were adding the tables/joins in a different order. I needed a way to tell CR to do the same joins, but in a different order.
That is when I remembered a feature introduced in CR 2008. Whenever you have more than one join (3+ tables) you can right click in the background of the linking window and select "Order Links". This allows you to specify the order in which the tables are incorporated into the "FROM" clause of the SQL statement. Normally the link order takes care of itself, based on the direction you drag the link lines. But in this case it appeared that the only way to get the SQL to work was to have the one Outer Join we needed as the very last join in the FROM clause. Sure enough, when I moved that link to the bottom of the order, the SQL ran without error.
Missing Sage custom functions
A customer upgraded to Sage (PeachTree) v2015 last week. Everything seemed to work fine, but their most important Crystal Report wouldn't run because it uses Sage custom functions, and those functions were not showing up in Crystal. The Sage software has a button to install these custom functions, but clicking this button didn't change anything. Sage support couldn't solve the problem so the customer called me.
I know that all Crystal custom functions come from DLLs and I assumed that this Sage DLL was either missing or in the wrong place. The customer asked Sage support for the name of the DLL so we could search for it. They sent him lots of information, but not the file name.
So the customer did a fresh install of Sage v2015 on a local PC, and everything worked correctly. So I opened up Crystal and could see the Sage custom functions listed together – along with the name of the DLL. We searched for that file (U2Lpeach.dll) and found it in:
C:\Windows\Crystal.
We then checked that same folder on the server and found that the DLL was in that same folder. But yet when we opened Crystal on that same server the Sage functions were not visible. Crystal had several other custom functions that were working, so I searched for those DLLs and found them in a different folder on the server:
C:\Program Files (x86)\Business Objects\BusinessObjects Enterprise 12.0\win32_x86
Since we knew that Crystal could read the DLLs in that folder, we copied the Sage DLL and pasted it in with the others. Once we restarted Crystal the Sage functions appeared. The difference probably has to do with a windows environment variable somewhere, but rather than mess with the server settings we decided that a duplicate DLL was workable solution.
Other Articles
SAP systems vulnerable to cyber attacks?
Gems from the Archives
When Crystal tries to be "helpful" (and how to avoid it) (April 2009)
Alternate Method for Splitting a Section (April 2009)
Removal instructions:
I have sent you my newsletter because you or your company are one of my consulting or training customers. If you don't wish to receive the newsletter you can use the link below.
Contact Information
Ken Hamady, MS
525K East Market St.
PMB 299
Leesburg, VA 20176
(540) 338-0194
ken@kenhamady.com
http://www.kenhamady.com
Copyright 2015 by Ken Hamady
All rights reserved - Republishing this material requires written permission