Archive for the 'Method' Category



Cross-tabs can total formulas that you can’t normally total

Tuesday 10 March 2020 @ 1:44 pm

One of my first 10 blog posts explained why some formulas could be totaled and others could not. Two of the things that prevent a formula from being summarized (totaled) are if the formula itself refers to a subtotal, or if it uses the functions Previous() or Next().

But I was reminded recently that both of these types of formulas can be summarized in a Cross-tab. Take these two formula examples:

// Rebate:
if Sum ({Orders.Order Amount}, {Customer.Customer Name}) > 25000
then {Orders.Order Amount} *.05
else 0

//Days Between Orders:
if {Customer.Customer Name} = Previous({Customer.Customer Name})
then {Orders.Order Date} - Previous ({Orders.Order Date})
else val({@null})

If I wanted to do a grand total of my rebates or an average of the days between orders I wouldn’t be able to use normal summary functions.   Even Crystal running total fields won’t work with these. In most cases people would resort to using variable to accumulate these totals. However, both of these formulas can be summarized using a cross-tab. You could do a simple cross-tab with a single cell to show the grand total and no row or column fields.  Or you could do breakdowns by other fields.

Not only does this save you dealing with variables, but a cross-tab can put these totals on the first page (Report Header), while variables will only be complete on the last page (Report Footer).  One more reason to use my favorite objects.




Charting based on a parameter value

Saturday 29 February 2020 @ 10:51 am

I have had several customers over the years who wanted to add a fixed line to a bar/line chart. This is typically some type of target or standard that they want to compare the actual data. If this value is in the report’s query results, then it is pretty straightforward. But what if the user wants to be prompted to enter the value to be used in the chart? Crystal won’t let you add the parameter directly to the chart. Even if you put the parameter value in a formula, Crystal might not allow that formula in the chart.

The reason is that parameters come before the data is read. This means that they are evaluated “BeforeReadingRecords” by default. Values that come before the data is being read are not eligible for grouping, sorting, totaling or charting. But if you put the parameter in a formula you can change the evaluation time of the formula to “WhileReadingRecords”. This will make it into a column of values. And even though they are all the same, they will be available in the chart expert as a value to be summarized. Something like this:

WhileReadingRecords;
{?YourParameter}

Then in your chart you can either summarize it by using a Maximum, or checking the “Do Not Summarize” check mark.

If you need to do this and have trouble then call me to schedule a short consult, or Email me your report and explain your requirement.




Using the ‘parent’ section in the Section Expert

Sunday 23 February 2020 @ 7:24 pm

If you have ever done a page break or a suppress condition in Crystal Reports, you have been in the Section Expert. This is where you can change any of the formatting properties of the sections. And if you have ever split a section into subsections, you know that each subsectioin has their own set of properties in the Section Expert.

But users don’t always notice that whenever you split a section into subsections, the parent section still exists in the Section Expert. So when you split the Details section in to Details A and Details B, there is still an item called Details in the list of sections, and this ‘parent’ section has properties that apply to all of the subsections at once. For example:

  1. If you check “Keep Together” in the parent section Crystal will try to keep all the subsections together on a page, and if they won’t fit will move them ALL to a new page.
  2. If you put a suppress condition in the parent section it will suppress ALL of the subsections at once.
  3. If you check “New Page After” in the parent section it will generate a page break after the last detail subsection, even if you rearrange the details.

You might find that this helps simplify formatting sections with subsections.




“New Page After” on group 2 orphans group footer 1

Tuesday 18 February 2020 @ 8:24 am

If you have two group levels and you put a “New Page After” on Group Footer 2, you will find that Group Footer 1 gets orphaned onto it’s own page. To fix this put a “New Page After” on Group Footer 1. Then instead of putting the same check mark into Group Footer 2 you use the following condition on the “New Page After” property:

{Group1.DatabaseField} = Next ({Group1.DatabaseField})

Of course you use your own database field from Group 1 in the formula. This prevents Group 2 from doing a page break on the last record of the group. On that record the next value for Group 1 is different, so the page break is handled by Group 1.




Column breaks in a multi-column report

Monday 30 December 2019 @ 11:12 pm

Crystal allows you to have multiple columns on a page. This is handy when you have a narrow list or a label and you want to pack more data on each page. To set up multiple columns you go to the section expert, highlight the ‘details’ section and check the option to ‘format groups with multiple columns’. This exposes a tab on the right that is labeled “Layout”. Here you can set the width of the column, the space between columns, and if the columns should go across then down (filling the top of the page first) or go down then across (filling the left side first).

Here you can also determine if the group headers and footers are part of the columns or if they go across the entire page and are separate. Usually, you want the groups to be included when you are using “down then across” and not when you are using “across then down”.

If you are using “down then across” and your groups are part of the columns you might want to have each group start in a new column. Crystal has a specific feature when you want a page break after a group, but not when you want a column break after a group. My workaround is to create a very thin group footer and then set it to “print at bottom of page”. This will force the next group to be at the top of a column.

Since long groups might start on one page and continue on the next, you should probably also set the group option to “repeat group header on each page”. This way the group spilling on to the next page has a label to identify the group.

This method isn’t perfect. You might occasionally get an empty column if the records fill the column exactly. Or you might want to line up the top of each group’s second column with the top of its first column, or maybe even repeat the headings with each column. For help with these issues see Expert Techniques Volume 2.




Deleting fields without losing (linked) column headings

Tuesday 24 December 2019 @ 5:14 pm

Whenever you drag a new database or formula field onto the details band, Crystal will add a column heading for you, automatically. This heading is just a text object but it will stay internally linked to that field. If you move the field horizontally, or change its width, the heading will adjust to match.  If you delete the field, the heading disappears as well.

This can sometimes present a challenge. For example, when you have formatted and aligned column headings and you need to replace the fields below them. When you delete the original fields the headings will also be deleted and then you have to recreate them and reformat them.  I was in this situation twice in the past few days. In one case I had to replace a group of tables with a SQL command, in the other I replaced a SQL command with a subreport. In both cases the database fields on the report had to be replaced. And as soon as I deleted the original fields I saw that the headings also went away. I didn’t want to recreate the headings to match the old ones, so I hit undo and did some experimenting.  I learned two things that allowed me to keep my original headings in place even after the original fields are deleted:

  1. If you copy the headings and paste a second copy of them somewhere else on the report, Crystal will only delete one set.
  2. If the new headings are in a separate section Crystal will delete the headings in the higher section.

So my new approach for this scenario is to:

  • Split the section that contains the headings to create a new temporary subsection.
  • Drag the new subsection to be above the original section.
  • Copy the headings and paste the copies into the new blank section.
  • Insert the new fields into the new subsection (so they won’t create new headings)
  • Use the format painter to format the new fields based on the format of the originals.
  • Use the menu (format > make same size) to size the new fields to match the originals.
  • Delete the original fields, which should delete the new headings in the higher section.
  • Move the new fields to the original section and align them with the original headings.
  • Delete the temporary subsection.

This process adds a few extra steps, but it keeps your original headings exactly as they were and allows the new fields to look exactly like the ones that they replaced.




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.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server