I have written before (several times in fact) about the challenges of exporting to Excel when you have subreports. It is difficult to avoid merge cells unless you get the alignment just right. Recently one of my readers told me that he exports to TTX format in this situation and finds it works better. I did a quick test using a report with a subreport on GF1. The subreport was just a bit below the other fields. When exported to:

1) Excel there were extra merge rows between the data rows
2) Excel (data only) the subreport was on a separate row
3) Tab Separated (TTX) the file would open cleanly in Excel

I am sure there are other variables but if you need to export to Excel format and there is a subreport involved, this is one more option to try.

I write lots of formulas and that means spending lots of time in the formula editor, staring at text. So I appreciate the fact that we can change the font, size and colors used in the editor to make things easier to read. By default Crystal uses Courier 10 but I prefer Lucida and will often bump the size up to 12. I started doing this when I was teaching so that students could read formulas on the screen. Then I found it helpful to increase the size when I remote into another PC which reduces  the size of the screen. Now I find myself making these changes in my own designer, making formula work easier on the eyes.

To get to these settings go to File>Options and click on the “Formula Editor” tab. If you do some experiments and want to go back to the default settings you can use the “Reset All” button at the bottom of the window.

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.

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.

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.

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 Continue Reading »
Chart labels that include subtotals

I recently worked with a customer who needed to incorporate some Excel data into a report. Excel tables are not my first choice, but sometimes it is the only practical solution for the client. Since these were XLSX files we setup up an ODBC connection. But when we connected from Crystal we could not see any tables under that ODBC connection. We were thinking about re-saving it as an XLS when I did a quick online search and found the answer we needed in a forum post.

We had to go into File > Options > Database tab. Then under Tables and Views we had to add a check mark for ‘system tables’. Then the spreadsheet table showed up. I was surprised, because I have created many reports that read spreadsheets and I don’t ever remember having to do that. It is possible that there was something unique in that environment that required this change, but I never argue with success. And, if you ever run into something similar you have one more thing to check.

This week we have a clever trick that was shared by Larry McCulloch of Lares Research as part of a Linked in discussion. The question was how to have the report automatically show the database being read. Apparently someone had run a report in a training/testing database and it had gotten mixed up with reports coming from the live database. If Crystal knew which database it was reading it could flag reports that were coming from someplace other than ‘live’. Larry’s suggestion was to add the following SQL expression in SQL Server environments):

{fn DATABASE()}

Then people suggested various was of doing the same thing in Oracle environments:

(SELECT ora_database_name FROM dual)

(SELECT sys_context ( 'userenv' , 'db_name' ) FROM dual )

(SELECT global_name FROM global_name )

I tested the SQL Server example but I don’t have an Oracle environment to test the others. If you try them or have some feedback, let me know.

update 3/25/2014 – I heard from an Oracle user that all three examples work.

update 10/12/2015 – in SQL Server you can also use:  ( SELECT DB_NAME()  )

A customer had a large cross-tab and he wanted to add some extra headings above the first two columns (The ‘row’ fields).  The row fields don’t normally get headings in a cross-tab but it isn’t difficult to put a couple of text objects above those columns in the empty “notch” above the cross-tabs row names.  The first he had was that the cross-tab was in the report header and spilled over to the next page.  Text objects in the report header would not repeat on subsequent pages.

Now, the simplest way to get something on every page is to put it in a page header.  But page headers don’t start printing until the report header is done, so that wouldn’t help us.  So I told the user to move the cross-tab to the report footer.  This does the same thing as the report header, and when the report footer spills onto multiple pages it includes page headers.

So now we had the text headings on every page, but they were higher then they should be. He wanted them lined up with the other headers above the cross-tab summary fields.  So the last step was to set the page header to “underlay”.  This means that instead of printing above whatever section comes next, it would print superimposed over whatever section comes next.  This made it easy to align things the way he wanted.

All this took less than 15 minutes.  So if you find that you can’t get Crystal Reports to do what want, it might be worth giving me a call.

One of my favorite sayings:

Good judgement comes from experience.
And experience comes from bad judgement.

I was reminded of the value of AutoSave this week when one of my reports froze up on me before I had a chance to save a large number of changes. I waited for it to come back and even went to lunch. But an hour later the report was still frozen. Normally I am pretty good about saving my work often, but I had been concentrating on a problem and lost track of time.

The machine I was working on did not have the CR AutoSave feature activated. But somehow the report that froze did not freeze the rest of  Crystal. I was able to use the menu and save my other reports, but I could not save the report I had been working on.

Since the menu was still working I took a long shot and activated the AutoSave feature. After a few minutes I noticed a new RPT file in the temp directory and it had all of my most recent changes. So even though it would not let me save the “frozen” report, CR was able to AutoSave it for me.

And as if that wasn’t a strong enough message, I had three client workstations crash on me this past week. None of them had AutoSave activated. So allow me to make a public service announcement:  Read my prior post on how to use the AutoSave feature and then activate it in your environment.