Archive for the 'Method' Category



Creating a “Distinct Sum” when the duplicates not grouped together

Sunday 24 November 2019 @ 3:36 pm

Crystal has a distinct count function but not a distinct sum. A distinct sum would be a sum that skips values in one field whenever there is a duplicate in a separate “key” field. It would work something like this: “only add each customer’s balance into the total once – even if a customer shows up in several different places in the report. The customer ID would be the “key” field while the balance would be the field you are totaling. It should only count the value on the first instance of the key field.

If you can group the duplicates together the solution is simpler. You can use a running total and set it to “evaluate on change of group” where the group is the key field. I teach this method in my advanced material. But a long ago student showed me a clever way to identify duplicates, even when they were scattered. I had never seen that method and couldn’t find it described anywhere else. I published it in my newsletter in 2004. When I needed to use it last week I decided to post it here.

Last week my challenge was a payroll report that showed employees grouped by department, showing pay and withholding. Pay was split by Dept but the withholding was combined. So when an employee worked in two different departments, his withholding would show up twice in the totals. I needed the totals to count the withholding once per employee, even if the employee showed up in several departments (on different pages).

To use this method you first create a running total field that is a distinct count of the “key” field. Then you create another running total, this time using variables, to total the numeric field. This formula has logic to only add the value when the first running total has changed from the record before. Any time there is a change in the first running total it must mean there is a new value for the “key” field. When the first running total doesn’t change it means that the “key” field value has appeared before. The formula I used looks like this:

WhilePrintingRecords;
NumberVar Prior;
NumberVar LYS;
if {#CustCount} = Prior + 1 //test if first record for this customer
then LYS := LYS + {Customer.Last Year's Sales};
Prior := {#CustCount}; //store current count to use for next record
LYS

If you need a Distinct Sum as a Subtotal you would reset the running total and both of the variables with each group.

I have seen other developers solve this problem by creating an array of all of the “key” values and checking each “key” value against the array before adding the numeric. This method lets Crystal handle that duplicate check so there is no reason to maintain an array.




Converting an Excel column from Characters to Numbers

Sunday 17 November 2019 @ 11:25 pm

I have written before about the challenges of reading data from an Excel, mainly because Excel doesn’t define data types for columns like you do in a data table. That article included a formula to convert a column of numbers into a column of equivalent strings.

Last week I had to do the opposite. A customer wanted to link a data table to a spreadsheet, but the linking field in the spreadsheet had numeric characters, while the data table had a true numeric column. There is nothing you can do in the Crystal linking window to get a numeric column to link to a character column, so we had to convert the Excel column from characters to numbers. Just like the previous article, you can’t fix this by formatting the column. The data type assigned to an Excel column isn’t affected by the format of the column, but only by the actual values stored in that column.

After a few experiments I discovered a solution. If you write a formula like this in Excel:

A1 + 0

The result will be a number even if the cell A1 is a character string with numeric characters. Even better, if there are any true numbers in the column, the formula will work the same for them. So we copied that formula for the entire column and that new column became our linking field.




No column headings on the last page (v2.0)

Tuesday 29 October 2019 @ 7:29 pm

Last month I wrote an article about suppressing the page header on the last page when there are no details. This is handy if your last page is a subreport, a chart or a cross-tab. After my newsletter went out one of my readers shared her approach to the same problem. She uses the group header of a dummy group, and sets it to “repeat” on each page.

Any time you have a Group Header you can set it to repeat on each page. One feature of  a repeating GH is that it won’t appear on the last page of a group, unless that page has at least one detail record.  I wrote about that behavior in another article long ago.  So the only trick is to create a primary group that includes every record in the report. Then you set this group header to repeat on each page and it behaves just like a page header, with the exception of not printing on the last page.

So how do you create a group that includes all the records in the report? You group on a value that doesn’t change.  If you have a DB field like “company” that doesn’t change you can use it.  But you can always create a formula that isn’t tied to any data fields.  My favorite dummy group formula is:

WhileReadingRecords;
"All"

The word “All” can be any value. Just keep in mind it will appear as the overall node of the group tree so you might want it to make some sense. The WhileReadingRecords function allows the report to see this static value as a recurring value, which makes it eligible for grouping.

Once you create the formula you use it as Group 1 in the report and then go into Group Options and check “Repeat Group Header on each new page”.  If you put your column headings in this Group Header they will appear on every page, but won’t appear on the last page (unless there are details printing on that page).

And thanks to Tina Nordyke, the DBA for Advocates for Basic Legal Equality, Inc for suggesting this method.




Literal values that include quotes

Saturday 26 October 2019 @ 12:25 am

I was recently working on a formula that had the crystal syntax for “is one of”.  It looked something like this:

{LastName} in [ "Smith","Jones","Thompson","Rutledge","Harris" ]

Each value goes in quotes (single or double). You separate the values with a comma and you put square brackets around the list. So I was surprised today when I saw that the formula was actually like this:

{LastName} in [ "Smith""Jones","Thompson","Rutledge","Harris" ]

Notice the comma missing between the first two values.  The report had been running for months without error messages which I didn’t understand, so I started testing.  My experiments pointed me to a syntax rule that I had read about but never used. It is for when your formula includes a literal string that you surround with double quotes, and when the literal string itself contains double quotes.  Crystal would assume the first quote is the ‘open’ and the second one is the close, even when you want the second one to be part of the visible output.   One solution is to use two consecutive double quotes within the literal. Whenever Crystal finds two consecutive double quotes within a literal surrounded by a pair of double quotes, Crystal will interpret the consecutive quotes as one literal quote and not as a closing quote.  This is much easier to explain with an example.  So if I wanted a formula to output this string:

The syntax for "is one of" uses brackets

I could write it like this inside the Crystal formula:

"The syntax for ""is one of"" uses square brackets"

The formula engine would only display one of the two consecutive double quotes in each pair.  That explains why my formula with the missing comma doesn’t generate an error.  Crystal treated the consecutive double quotes as a single literal and then combined the first two elements in the list as being the single value:

Smith"Jones

There was no error message, but the results would not have been correct.

BTW, the same principle applies to single quotes. If you put two consecutive single quotes in a string surrounded by single quotes, Crystal will ignore the first and treat the second as a literal quote.

The reason I didn’t think of this right away is that I have never used consecutive quotes in a formula. If I have a string that needs to contain single quotes I surround it with double quotes. And if the string needs to contain double quotes I surround it with single quotes. In the rare case that a string needs both I would split it into separate pieces and combine them. I find that using consecutive quote pairs makes the formula harder to follow.




Table filters in the database expert

Tuesday 8 October 2019 @ 9:06 pm

This week I worked with two different customers, helping them locate data buried in a large system with hundreds of tables. In both cases I had to use the table filter method to help me find the correct tables. For instance, when we needed to find the vendors table we looked first in the V section of the table list. However, in the system we were using all of the tables names had a 2 character prefixes for a dozen or more different modules. It wasn’t clear which module might contain this table. So I added a filter to the list of table names shown in the Database Expert. This way only table names that contain VEND somewhere in the name would be displayed. It made it easy to spot the correct table.
To add a filter to the table list you need to go into File > Options > (Database Tab).

In the middle section you will see two boxes on the right, and the top one is for table filters. It is labeled [Table Name LIKE:]

In this box you enter the characters you are looking for with a percent sign before the characters and/or after the characters. This percent sign is a wild card symbol. So since we only wanted to see tables that contained VEND anywhere in the name we put in a filter like this:

%VEND%

If I wanted all tables that have TMP on the end I could enter a filter with only one wildcard, like this:

%TMP

The only downside I have seen when using these filters, is that it is easy to forget about the filters. It then takes a bit to figure out why the table list is missing or incomplete. So don’t forget to take the filter out when you are done.




Suppress column headings when there are no details on the last page

Sunday 22 September 2019 @ 10:38 pm

Sometimes there are details printing on the last page and sometimes the last page is just the Group or Report Footer.  You may not want column headings on the last page if there are no details.  I have seen and tried several methods to accomplish this but not all are reliable. For instance using “OnLastRecord” as a suppress condition will work most of the time, but will also suppress the headers when the last page still contains exactly one detail record. I have found the approach below to be the most reliable.

First you split the Details band into A and B subsections.  You suppress Details B and use Details A for your visible fields.  Then you create the following formula field and place it in Details B:

WhilePrintingRecords;
BooleanVar Ending := OnLastRecord

Last you use the following formula to suppress the Page Header, or the part of the Page Header with column headings:

WhilePrintingRecords;
BooleanVar Ending

A Boolean variable is False by default.  The variable will only be True once the report has passed the last Detail A, which means that the last record has printed.  If there is a page header after that point it will be suppressed, because the “Ending” variable is now True.




How to turn records/rows into columns

Tuesday 27 August 2019 @ 7:32 pm

A customer recently requested an unusual report layout. They wanted the field labels in the first column on the page and each record to be a new column on that page. I remembered struggling with a similar layout request years ago and pulled up an old report from 2009. What I came up with then worked fine if you only needed one set of labels on each page, meaning that the columns were so tall that there wasn’t room to fit a second set below the first one each page. In 2009 I had half-height columns which meant two sets of labels along the left side of the page. That got very complicated.

Fortunately my current project had full page columns, which meant my 2009 solution was a perfect fit. Here are the steps:
1) Create a static value formula that can be used as a group. I usually use something like this:

WhileReadingRecords; 1

2) Group on this formula, and in the group options check “repeat Group Header on each page”
3) Make the group header about 6 inches deep and type all the field labels along the left side.
4) Make your details section about 6 inches deep and put the corresponding fields in.
5) Go into the section expert for the details section and check “Format with multiple columns”.
6) When the layout tab appears set the width of the column to an appropriate number.
7) Check the layout options: “Across then down” and “Format Groups with multiple columns”.

Note – you can do something similar with a cross-tab, but the additional pages for cross-tabs are all to the right on page 1 when in preview.  And the layout isn’t as flexible with a cross-tab.




Finding groups where the last record meets a criteria

Monday 15 July 2019 @ 9:52 pm

One of my students presented me with the following challenge. Their address records were stored in a table that keeps an address history. That means that new addresses don’t replace the old addresses. Each new address is a new record with a time stamp. The current address is the record for that customer that has the latest timestamp.

To display the current address for each customer is fairly easy and can be done in one of two ways:

1) Group by Customer and sort by time stamp. Hide the details and place the address fields in the Group Footer. This would display the last address for each Customer.

2) Group by Customer and put in a group selection formula that says:

{Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Address.CustomerID} )

Either of these will work to show the current address for each customer. But if you want to select only current addresses in a particular state, like NY, you have to be careful. If you put the State criteria in the record select expert or record selection formula the criteria will be applied before the grouping happens. Crystal will start by selecting only New York records regardless of how old the timestamp is. Then it will do the grouping and show the last NY record in each group. You would end up with the last New York address for each customer, rather than getting the accounts that have New York in their last record.  Anyone who had moved of NY to somewhere else would still show up.

My original solution involved a formula that combined the Date and the State into a single string field. Then I used a complicated group selection formula to find the right records. You can read about it here and it works fine.

But today I realized there is a simpler approach. The key is putting the State rule into the group selection formula, so it is applied after the grouping is done. So your Group Selection would look like this:

{Address.TimeStamp} = maximum ( {Address.TimeStamp} , {Address.CustomerID} )
and {Address.State} = "NY"

As long as the last line stays in the group selection formula this will return the desired records.




Moving cross-tab numbers to Excel

Thursday 27 June 2019 @ 9:00 am

If you are trying to move cross-tab numbers into a spreadsheet, there is a short cut. You can simply copy and paste the entire cross-tab into your spreadsheet. Right-click in the upper left (empty) cell of the cross-tab and select “copy”.  Then switch to the spreadsheet and right-click in a cell and select “paste”. The cross-tab numbers should appear in the spreadsheet.

The only limitation is that the cross-tab has to fit on one page in Crystal. Fortunately, all recent versions of Crystal allow the page to be as large as needed. Go into “File > Page Setup” and check the option called:

“Disassociate Formatting page size with Printer Paper size. “

Then set the height and width to whatever you need to accommodate your cross-tab.

If you have an older version of CR (before CR 2008) the option above is missing. Instead you can use a PDF virtual printer like Cute PDF, and set a custom paper size so you have enough room.




How to reorder tables to improve performance

Monday 17 June 2019 @ 12:20 pm

So say you have four tables A, B, C and D. A joins to B, B joins to C and C joins to D. If all the tables are required in the results (i.e. you are using inner joins) you can theoretically use 4 different link configurations that should give you the exact same output. If you start with A or D the joins would be in a straight line (ABCD or DCBA).  If you start with B or C you would get a fork, like B to A and B to C with C linking to D. But even though the results will be the same, the performance could be dramatically different. So how do you decide which pattern is most efficient?

There isn’t a simple answer that works in every case, so testing is important. However, there are two places I look that often help: the indexed fields and the WHERE clause fields. You can often see the indexed fields in the linking window (colored tabs) or you can ask someone who knows the database what the indexes are on each table. To see the WHERE clause fields go to the database menu in Crystal and select “Show SQL Query”. The fields mentioned in the WHERE clause should match your record selection formula.  If they don’t you may need to tweak the formula so that the criteria can translate into SQL.

Indexed fields:
When linking you want your join to go TO indexed fields and ideally to ALL the fields in that index. So say Tables A and B are linked on two fields from each table. And say that these four fields all have red index tabs. But table B has a third field with a red tab and that field isn’t part of the join. That would mean you should link from B to A.  This uses the complete index in A which is the more efficient than linking to the partial index in B.

And don’t assume that because B is sitting on the left that the join starts at B and goes to A. I always hit the “auto-arrange” button in the links window to confirm the direction of the joins. After hitting “auto-arrange” all the joins flow from left to right. If a join is backwards, you can right-click that join and select “reverse join”, then click “auto-arrange” again to confirm the new direction.

Here are some other posts where I discuss the affects of linking on indexes:
https://kenhamady.com/cru/archives/2923
https://kenhamady.com/cru/archives/2653

WHERE clause fields:
Now lets also say that most of the WHERE clause criteria applies to the C table. I try to take the table with the most restrictive criteria and put it all the way to the left (or as far left as possible). That way they query starts out with the smallest data set possible and each subsequent join has fewer matches to find.

If the primary field in the WHERE clause is found in more than one table you get some flexibility. You can select the table that works best for indexing and then use the field from that table in the criteria.

So based on the above scenario I would recommend starting with table C. Then forking from C to both B and D, with a final link from B to A.

In some rare cases the indexed fields  and the WHERE clause fields can’t both be optimized at the same time because they point in opposite directions. When that happens you have to test different join patterns to see which works best.

One last note. In most reports the order of the joins is obvious from the link pattern.  But if you look at the SQL and the links aren’t in the order you want, you might have to use the “order links” feature of the database expert.

 




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server