Archive for March, 2018
How would you like your reports to be automatically run, exported to a PDF and delivered to your Email InBox every Monday morning at 6am? The Crystal Reports designer doesn’t provide a way to do this (unless you upgrade to CR Server or BO Enterprise). But if you look at third party products like those on my LINKS page you will find several reasonably priced or free tools that do this. Some do even more. So every March I go through the list and publish a feature comparison on my blog.
There are 10 active products in the list this year. The page linked above provides a brief description of each product and lists the features that set it apart. Then there is a detailed feature matrix that shows the key specifics for comparison including prices. To clarify the matrix terminology I have written a feature glossary to explain what each feature means. Finally there are links to the vendor websites so that you can get more information on each product. In May I will be updating a separate article that compares server based scheduling tools. If you think one person can manage all of your scheduling you are probably fine with one of the desktop tools, regardless of the number of people receiving the scheduled output. But if you plan to have multiple people scheduling reports then you may want to consider a server based tool.
I have written once before about using the “Order Links” feature of the database expert. In that article I used “Order Links” to help me prevent a SQL error in the pervasive database engine. This past week I used the same feature to improve the performance of a report.
This report had many tables but there was one, the Dept table, that provide the primary filter for the report. The user would select a specific department each time they refreshed. But when I looked at the SQL generated by CR, I noticed that the Dept table was the last table joined into the data set. In my mind, that meant that Crystal was bringing in thousands of linked records that would eventually be discarded because they were linked to the wrong department. If the SQL could apply the filter up front we would greatly reduce the number of linked records we would generate, and that would speeds things up.
So I suggested to the user that they go into the “Links” tab of the Database Expert and right-click to find the “Order Links” option. I had them move the Dept join to the top of the joins list and they immediately saw a dramatic improvement in performance. It may not work in every report or with every database (this report was Oracle based), but when there are lots of tables and the report is slow, it is another option to try.
Many Crystal course books will have a lesson on printing a “Continued” message whenever a group takes up more than one page. This message usually prints at the top of each page in the group, except the first. But below is a different approach. It prints “Continued on the next page” at the bottom of pages when a group is going to go beyond the current page. There are actually several ways to do this, but the one that works in almost all situations is below. It uses 3 formulas, and these can be cut and pasted into your report:
The first formula goes on the Group Header (and can be suppressed):
BooleanVar Continued:= True
The second formula goes on the Group Footer (and can be suppressed):
BooleanVar Continued:= False
The third formula goes on the page footer:
then "Group continues on next page"
I had a bit of a mystery this past week. A customer wanted to suppress a series of vertical lines in the page header, based on a condition (a field being null). SAP just added a suppress condition button for lines and boxes in the latest version of CR, CR 2016, so I figured it would be simple.
But when I put the condition formula in for one of the lines, it did not suppress when it should. I applied the same condition to the next vertical line and it worked as expected, but the first line did not. If I deleted and redrew the line it worked fine, but nothing I could do would get the original line to work.
So I spent a few minutes trying to figure out what was different about the line that didn’t work. I found that while the top end of this line looked like it was in the page header, it was actually a hair higher in the report header. The report header was suppressed.
Normally, suppressing or hiding a section has no affect on formulas. It does affect some other things like subreports and cross-tabs. A hidden/suppressed section will prevent subreports in that section from running. To get a shared variable from a subreport without seeing the subreport requires that we use a ‘stealth’ subreport. Also, a hidden/suppressed section will prevent a cross-tab from doing any of it’s calculations. But formulas are often put in suppressed sections and they are not normally affected. The suppress formula for a line or a box is an exception, apparently.
So, if you enter a condition formula to suppress a line or a box, and if that line/box has it’s top in a suppressed or hidden section, then the suppress condition will be ignored. This isn’t true at the bottom of the line/box, only at the top.
One more mystery solved.