Archive for the 'Method' Category
When you add a filter to most reports you create a rule that applies to each record and determines if that record qualifies or not. Examples would be:
{table.state} = 'PA' and {table.Amount} > 50
This is called record selection and is stored in the Record Selection Formula. You might enter the rules using the Select Expert, but Crystal will convert those rules into the Record Selection Formula.
But sometimes you need to add criteria that applies to an entire group based on a group subtotal. For instance you might want to limit the report to states where the total amount for the state is over $5,000. This is called group selection and these rules are stored in the Group Selection Formula. The rule I just mentioned would look like this:
Sum ( {table.Amount} , {table.state}) > 5000
When the subtotal already exists on the report you can create the rule using the the select expert. You would choose the “Group” option instead of the “Record” option of the select expert. Crystal will add the rule to the Group Selection Formula. Any type of summary operation can be used for Group Selection (Sum, Count, Average, Minimum, Maximum, etc).
Just keep in mind that using Group Selection comes adds a few quirks to your report.
1) Adding a group selection rule will shorten the report but the record count (shown in the status bar) won’t change. That is because the record count is determined by Record Selection before Crystal applies the Group Selection Formula.
2) Groups that don’t meet the Group Selection rules are eliminated from the report but will still show up in the group tree. The group tree is created before the Group Selection Formula is applied. So clicking some values in the group tree will not take you to that group.
3) Grand total summary fields will not be reduced by Group Selection. The Grand totals are created before Group Selection. This issue can be solved by re-creating your grand totals as Running Total Fields rather than as regular summary fields. Running Total Fields are evaluated AFTER Group Selection so they will be lower than the original grand totals.
I just had a customer ask me about their distinct count grand total and why it didn’t match the sum of the subtotals. This is common. When you add up distinct counts at the group level they often won’t add up to the grand total. This is because of what a distinct count is designed to do.
A distinct count summary will count how many different values there are in a column. So if I group patient visits by doctor and then want CR to tell me how many different patients each doctor saw in that period, I would use a distinct count at the doctor group level. And if I also wanted to know how many different patients were seen across ALL doctors I could create another distinct count of the patients and put it in the report footer. But it is very likely that this grand total is not going to match the sum of the doctor subtotals. This is because any patient who saw more than one doctor in the period will show up once in each doctor’s group, but then will only be counted only once in the grand total.
But there are special cases where the grand total needs to be the sum of the subtotals. If you find yourself with this requirement the solution is simple. You create a formula that concatenates the group value with the field used in the distinct count. (In the example above you would combine the doctor with the patient.) Then you do a distinct count of that formula. This will give you the same values at the group level, but it will prevent the grand total from combining values from different groups.
This technique is article #25 in my Expert Techniques volume 1.
I was asked about this feature today and noticed that I had never mentioned it in my blog. It is a strange feature because you can turn it on in most reports and yet in most reports it will do absolutely nothing. But when you get it to work it can greatly improve the performance of the report. You will find this option in the database menu.
So what does it do? It tells the database to do the grouping and subtotals. The database will only return one summary record for each group. Check the SQL statement and you will see a GROUP BY clause at the end. This is useful when you have huge amounts of data to process and when you don’t need to show any detail level values. But, this feature only works in reports that meets some very specific criteria.
Things you must do:
Group on a database field or a SQL expression (not a formula)
Hide or suppress the details
Make all of the visible fields either a group field or a summary field
Limit the summaries used to those supported by your Database
(Sum, count, minimum and maximum should be safe in all databases)
Things you must NOT do:
Use specified order grouping.
Add running totals using detail fields.
Add summaries using formulas fields.
If your report meets these criteria, Crystal should be able to add the GROUP BY to your SQL statement, get the server to group the records and calculate all of the summary fields. Use the Show SQL option to confirm the GROUP BY appears.
I like to highlight key objects in my reports. For instance I highlight small subreports that might be confused with regular fields, or that might not be noticed at all. I also highlight formulas that involve variables as a reminder that they shouldn’t be deleted or moved. (Usually a specific location is required for variables to work correctly.) I will sometimes highlight several formulas with the same color so that I can quickly see which formulas work together. This is simple when the formulas are suppressed or when they are in a suppressed section, but sometimes the object is visible. In that case the object highlighting should only be visible in design mode and not in preview mode. Here’s how you do that.
Most Crystal formatting properties have a formula condition button [x+2] to the right of the normal controls (check mark, drop down, etc). This allows you control that property based on parameter values or data values. When you use a condition formula there is no need to set that same property using the normal controls. If you set a formatting property both ways the normal control setting will apply to design view while the formula will apply to preview.
So say I would like a field to have an aqua background in design view but no color in preview. I would first format that field by selecting aqua from the drop down. Then I would go into the condition formula and type: CrNoColor
The field will have an aqua background color in design view but no background color in preview.
I had a fun challenge today. A customer had a table with an odd structure. One column was a payment amount. It was followed by 40 different columns for various fees. He wanted the payment amount to be applied to the fees from smallest amount to largest amount until the payment was used up. The report was to show the balance for each fee after the payment was applied.
Normally this type of data would be vertical. There would be a separate row for each fee and only 2 columns (fee name and the fee amount). With that structure you could sort by fee amount and use a variables to apply the payment to the records in ascending order. But with 40 fixed columns I had to load the values into an array, and then put the values in the right order.
I loaded the fees into the array as strings, combining the amounts and fee names into one element looked this:
24.50=xyzFee
This allowed the fee name to follow the amount through the sorting process.
Once I loaded all these strings into the array I used my bubble sort formula to re-sort the array from lowest amount to highest amount. In the bubble sort comparison I used the Val() function to convert the strings to numbers. This put them in order by their true numeric value.
Then I wrote a second loop to apply the payment. It steps through the array, which is now in the right order, and applies the payment to the fees, one at a time. Each fee amount is reduced to zero while the payment value is reduced by the amount of each fee. If the payment’s remaining value drops below the amount of the next fee the remainder is subtracted from that fee and the payment is reduced to zero. Any fee amounts in the array beyond that point stay the same.
Last, to display the results, I wrote 40 separate formulas, one for each fee. Each of these formulas loops through the array looking for its specific fee description. When it finds the element with a matching description it uses the Val() function to convert that string to a numeric value and displays that value. This is the relatively simple loop formula:
EvaluateAfter({@Build Fees Array});
stringVar Array Fees;
Local numberVar i;
Local numberVar fee;
FOR i:=1 to ubound(Fees)
DO (
if 'xyzFee' in Fees[i]
then Fee := val(Fees[i])
);
Fee
Not many people would think this process was fun, but I did enjoy mapping out a creative approach to a unique requirement. And it was less than 2 hours from the requirements to a validated report. The next time your report requirements are a bit unorthodox, keep me in mind.
Today a user asked me how to automatically select the prior month’s transactions from the database. The transactions didn’t have dates, but instead had two numeric fields for Period (month) and Year. They were using a formula to convert these two numbers into a date and then using that formula as part of the selection formula. That approach will work but it is usually slow. This is because the database (or the SQL Query) doesn’t know anything about the date formula in the report, so it can’t apply any date criteria before sending back the data. Crystal gets all years and periods and then has to apply the criteria in the report.
A more efficient approach is to start at the other end. Instead of converting the DB fields to match the criteria values, we convert the criteria values to match the the database fields and let the database do the heavy lifting. In this case created three formulas:
// Prior Pd End Date
Maximum (LastFullMonth)
This gives us the last date of the prior month. From there I created two formulas that extract the year and the month from that date:
// Prior Pd Month
Month ({@Prior Pd End Date})
// Prior Pd Year
Year ({@Prior Pd End Date})
Then the selection formula can use these values to filter the DB fields, something like this:
….
and {Trans.Year} = {@Prior Pd Year}
and {Trans.Period} = {@Prior Pd Month}
This should allow the criteria to be passed to the WHERE clause of the SQL, and maybe even hit an index to make short work of finding the correct records.
The principle works the same if you are using a Date Range parameter. You could use formulas to convert the parameter values so they match the database fields. I wrote about this a few years ago in a similar case where the dates were stored as strings.
One of my readers shared a solution he recently found for highlighting a specific string within a larger field (like a comment, notes or memo field). To provide an example, I wrote a report against the Xtreme database and highlighted the word “graduate” the first time it occurs in the employee notes field.
There are two parts to his approach. First you write 3 formulas that split the memo field into three separate pieces: the target string, everything before it, and everything after it. Then you reassemble these three pieces by dropping them into a single text object. The target string formula can then be formatted separately so that it stands out within the text.
I used 3 additional formulas to make the process more generic. Here are the formulas I used:
//Target String - replace with your own target
"graduate"
//Target Start - replace Notes field with your Notes field
Instr ({Employee.Notes}, {@Target String})
//Target End
if {@Target Start} = 0
then 1
else {@Target Start} + Length ({@Target String}) -1
//Output Before- replace Notes field with your Notes field
if {@Target Start} <= 1
then ""
else {Employee.Notes} [1 to {@Target Start}-1]
//Output Target - replace Notes field with your Notes field
if {@Target Start} = 0
then ""
else {Employee.Notes} [{@Target Start} to {@Target End}]
//Output After - replace Notes field with your Notes field
{Employee.Notes} [{@Target End}+1 to -1]
The 3 “Output” formulas are dropped into the text object. Make sure the text object is set to “Can Grow”.
The process can be made more sophisticated if the target string varies in length, as long as you can clearly identify the character pattern that marks the beginning and the end of the target string. If you need help with that let me know and we can schedule a short session.
There is also an alternate approach where you embed HTML tags before and after the target string and then use Crystal’s HTML interpretation to change the format at runtime. However, this is limited to the HTML tags that Crystal supports.
Finally, thanks to Doug Weiner at Beacon Legal Software Services for suggesting this post.
This is better illustrated with pictures. Recently a customer had a Cross-tab that looked like the image on the left below. But they wanted the first column to repeat next to each value in the second column, like the image on the right below.
I found a relatively simple way to do this:
- I created a formula the combined the two column fields into one string, with a dash between them.
- I went into the Cross-tab expert and clicked the “Group Options” button for the first column field. I changed it to the new formula.
- I then clicked the”Options” tab and checked the option “Customize Group Name Field”.
- I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
- I went to the the last tab in the Cross-tab expert named “Customize Style” and highlighted the concatenated formula in the “Rows” box.
- I checked the option “Suppress Subtotal” (if not already checked) and clicked OK.
The cross-tab then looked like the one on the right.
I have written before about data type changes in Excel. One article explained how to convert a column of numbers into character values so that they can link correctly to another column of character values.
Today I was asked how to do something similar with dates. Two spreadsheets were being linked on a date field, and Crystal was reading one field as a true date and one as a string date. I found a simple way to get Crystal to recognize the string as a date. I created another column that was simply the Date String column plus zero [e.g. =A3 + 0]. This turned the new column into a number representing the date. Then I formatted that column as dates and that allowed Crystal to recognize the new column as a date.
It surprised me to find that I had to format the field as a date. When it comes to dealing with strings and numbers the formatting is usually irrelevant, but apparently with dates the format is important. When I tried to skip formatting the column as a date, Crystal read the new column as numeric values.
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.