A few years ago I wrote about a limit on the number of tables you can use when connecting to MS Access. The limit is 31.  If you add table #32 to a report it will crash with no error message.  Even using 32 tables in an MS Access query will generate an error message. I see this occasionally when dealing with reports for Raiser’s Edge.

Today we needed table #32 and I found a way to get it. I made a duplicate copy of the MDB and connected to the first instance to get 30 tables. Then I connected to the duplicate MDB to get the other two tables. I selected those two because their joins made up a separate branch from the other 30.

This worked because Crystal does two separate queries, one for each MDB, and then merges the two result sets locally. Crossing connections a last resort, and is never very efficient.  The report was a bit slower but at least it would run.

Last summer I posted a draft of an improved formula for calculating business hours between two dates. It was meant to replace formula #13 on my formulas page, but I was waiting to do some additional real-world testing. Yesterday I got a good test case when a customer asked me to tweak the original formula to deal with start and end times outside the business day. I cleaned up a few typos and updated my formulas page.

This new version lets you separately specify a business start time and business end time for each of the 7 days of the week. It also includes additional logic to deal with events that start and/or end outside the business day (e.g. on a weekend, a holiday or after hours). The formula is now about 70 lines long. Fortunately, you only need to make changes in the first 20 lines or so. Here you specify:

• The DateTime fields to use for the Begin and End of each event
• The business start and end times assigned to each day of the week
• The list of holiday dates, which you can enter for multiple years

The output is a numeric value in hours as a decimal. If you want to show the value in “HH:MM” format you can use the “Elapsed Time String” formula on my site to convert this value into that format. Remember to multiply this formula’s result by 3600 since the input for the “Elapsed Time String” formula is seconds.

If you need help implementing this formula or any of my formulas you can always call to schedule a short consult.

I work with many different CR users. It seems that whenever I open the Report Explorer view in Crystal Reports, the user is a bit surprised. I get the impression that not many people use or know about this feature. I wrote about it once before (a decade ago) but since then I have found two more uses that I tap into regularly.

1) Selecting one of several superimposed objects.
One report I created for an educational assessment company had 4 superimposed picture objects in different colors. They were all in the same spot, but had suppress conditions so that only one would appear at a time. Trying to select a specific one of these objects is a challenge. But when you open the Report Explorer for that section, the objects are listed separately. You can select the object in the list of the Report Explorer and it behaves the same as when it is selected in design mode. You can also right-click on the object in the list and get all the same options you would get if you right-clicked the object in design mode.

2) Locating subreports
I recently had a very crowded report and was having trouble with a shared variable, that came from a subreport. The trouble was that the subreport was small and I was having trouble finding it. One of the features of the report explorer is that you can decide which of three object categories to have it show (Grids and Subreports / Fields / Graphic Objects). By turning off Fields and Graphic Objects the list showed only Grids (cross-tabs) and Subreports. This made the one lone subreport simple to find.

So if you haven’t ever used the Report Explorer, go into the View menu to activate it. You might find it useful.

I have written several times about using inflation tables to force duplicate data. I even posted some SQL queries that generate inflation tables in Microsoft SQL Server. Today I needed an inflation table for an Oracle based report and didn’t have one in my library. So I did a bit of research and found this one mentioned frequently. It worked well for my needs today:

SELECT ROWNUM FROM DUAL
CONNECT BY ROWNUM <= 100;

The “100” value can be replaced with whatever number you need.

So when do we use inflation tables? I use them whenever I need to turn a single record into multiple records. Here are the common uses I have seen:

• Print multiple labels for a single row based on the quantity value in that row.
• Splitting some orders into multiple records for shared commissions.
• Creating a series of dates from a single date record.
• Separating a multi-value field into separate single value records.
• To repeat all records several times, grouped differently each time.

If you have a task like this and want some help, give me a call.

I found a limit that exists in Crystal that I didn’t know about before. Even if I had known about this limit I wouldn’t have ever expected to exceed it.  But it just popped up in one of my reports.

I created a report related to donors for a large organization. One part of the report needed to show the top 10 donors from a very large pool of people. I decided to use a cross-tab with a “Group Sort” so we wouldn’t need another subreport.  It tested just fine on the sample data set.

But, when they ran the report on the full table they received an error saying a cross-tab couldn’t have more than 65K rows or 65K columns. Apparently, the data set included over 100K donors.  To find the top 10 donors, the cross-tab would have to initially create a row for every donor. Even though I only needed to see the top 10, the cross-tab needed to see ALL of them, and that exceeded the limit.

Instead I created a subreport and grouped by donor. Then used the “Group Sort” on the actual groups, rather than a cross-tab. Fortunately this limit doesn’t apply to groups in the report, only to groups (rows or columns) in cross-tabs.

One of my readers recently stumbled onto a trick and decided to share it with me. He wanted to display how long his reports took to run. He knew he could tap into the DataDate and DataTime to get the time the report started. His recent realization was that he could put a subreport in the report footer and get a second DataDate/Time at the end of the process. By comparing the two he would know the time it took for the report to run. By using DataDate and DataTime the values wouldn’t change, even if he saved the report with data and opened it a few days later.

The subreport can be completely blank other than this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT

Then in a separate report footer (below the section containing the subreport) we add this formula:

WhilePrintingRecords;
Shared DateTimeVar EndDT;

NumberVar TotalSec := DateDiff ('s', DataDate + DataTime, EndDT);

NumberVar H := Truncate(Remainder(TotalSec, 86400)/3600);
NumberVar M := Truncate(Remainder(TotalSec, 3600)/60);
NumberVar S := Remainder(TotalSec , 60) ;

Totext ( H, '0' ) + ':' +
Totext ( M,'00' ) + ':' +
Totext ( S,'00' )

The end result uses one of my web formulas to generate a string formatted as h:mm:ss, but there are other format options.

And thanks to Mark Edwards at DataReport Consulting for sharing his discovery.

I was recently trying to create a report and was having trouble finding a specific table. I knew the field name, but it wasn’t in any of the tables where I expected it to exist. Lets just say the table names in this database are cryptic.

Fortunately, most of the mainstream databases allow you to query the system tables to list all the tables and fields. I found some great SQL online and created a report to read the table structures and search for the field I needed. The query allowed me to create a searchable data dictionary report, or schema, for this database.

The SQL example I found is on a site that lists similar SQL for other databases as well:

This example is for MS SQL Server:
https://dataedo.com/kb/query/sql-server/list-table-columns-in-database

This example is for Oracle:
https://dataedo.com/kb/query/oracle/list-table-columns-in-database

This example is for MySQL:
https://dataedo.com/kb/query/mysql/list-table-columns-in-database

This example is for Pervasive:
https://communities.actian.com/s/question/0D53300003xbnTrCAI/how-to-query-schema

So if you want to generate some quick searchable documentation for databases in any of these formats you can use the links above.

Whenever you add a group to a Crystal Report, Crystal creates and inserts a field called the “Group Name” as a heading for each group.  These values are also what you see in the group tree along the left of the screen. In most cases the group name simply shows the values of the group field, but there exceptions. For instance, when you group on a date field you can choose to group “for each month” or “for each year”. The group name would display only the month or year from the field. If you do a TopN or use Specified Order you may get a final group called “Others”.

Crystal also gives you the ability to customize the group name. You can either pick a different database field to display as the group name, or you can write a formula to be the group name. For instance you might group by a Customer ID, but choose the Customer Name field as the group name. Or you might group by Employee ID, but create a formula that combines First Name and Last Name to be the Group Name.

To make this happen you go into Group Options (Report > Group Expert, Options button then Options tab) and check “Customize Group Name Field”.  You will be given the choice of selecting another database field or creating a formula expression using the X+2 condition button.

Here’s a tip – when I want to use a formula expression for a group name, I typically write the formula as a separate formula field. Once that is saved I will go into the group options and put my formula field into the condition formula. If the logic ever needs to be changed it is simpler to modify a formula field than it is to get into the expression editor within the Group Options.

Note that you can also customize group names in cross-tabs, using the “Group Options” button for either the row or column fields. The interface is the same.

There are several ways to create totals in Crystal. This week I solved problems for two different customers by changing the type of total they were using. I will give a short explanation here, but if you want to really understand this topic you should download the Expert’s Guide to Totals in Crystal Reports from my website ($12).  It comes with example reports and exercises.

The primary method for creating totals in Crystal is to add summary fields. In the menu use Insert > Summary or find the Sigma symbol on the tool bar. Some users default to using running totals because they see them listed in the Field Explorer. But summary fields have several advantages over running totals. Summary fields can be:

1) placed in the Group Header as well as the Group Footer
2) used in the Group Selection formula as a group level filter
3) used in Group Sorting to rank the groups in order based on a subtotal value
4) Copied to other group/report sections to create additional summaries

You can’t do these with running totals, so my first choice for creating any total is to use a summary field.

But there are specific situations when a running total will solve a problem that you can’t solve with a regular summary field:

1) If you want to watch the value change, row by row, like the balance in a checkbook
2) If the column you are totaling has duplicates and you need to skip the duplicates
3) If you use Group Selection and then want a total of just the groups that meet the criteria
4) If you do a TopN without others and need a total that doesn’t include the others

Some users also use running totals because you can apply a condition directly to the total. This way the total only includes records that meet a specific criteria. But if you write an If-Then formula with your condition you can use a regular summary field and get the same result. This gives you the conditional total without giving up all the advantages of summary fields.

And, if you get really stuck on a total issue, you can call me for a short consult.

My last post talked about Group Selection and showed off some quirks. For instance adding a Group Selection criteria might cut your report in half – but the record count stays the same. So I was curios to find out how the functions Previous() and Next() are affected by Group Selection.

First, what do these functions do? Normally, when you refer to a field in a formula the values for that field will come from the current record. These two functions allow you to refer to the record before or after the current record. So, if I wanted to know the number of days between one order and the next I could write the following formula:

{Orders.Order Date} - Previous ({Orders.Order Date})

This would tell me the number of days between the two dates. But what if you are using Group Selection. Do these functions use all the records from Record Selection, or just the ones that meet the Group Selection criteria?

Say I wanted to limit a report to the records that were above average. I could calculate the average order amount for the entire report then use that total in the group selection formula like this:

{Orders.Order Amount} > Average ({Orders.Order Amount})

Adding this rule would cause about half of the orders to be eliminated from the report. But as we mentioned above those records are still in memory. They need to be because they are used to determine the overall average. So what happens if you write a formula like the one above that refers to the previous order date? Will it see all records, like the record count does, or only ones that meet the Group Selection criteria?

My gut told me that this formula would include records that didn’t meet the Group Selection criteria, but I was surprised. The Previous() and Next() functions are apparently evaluated AFTER Group Selection. So the formula above will always pull values from the records that meet the Group Selection Criteria.