Archive for the 'Method' Category



Get distinct count subtotals to add up to the grand total

Tuesday 8 September 2020 @ 11:36 pm

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.




Why would you “Perform grouping on server”?

Monday 31 August 2020 @ 9:34 pm

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.




Objects highlighted, but only in design view

Saturday 25 July 2020 @ 6:16 pm

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.




Fun with looping logic

Wednesday 17 June 2020 @ 11:59 pm

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.




Giving the database what it needs

Monday 8 June 2020 @ 8:35 pm

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.




Highlighting a phrase within a larger field

Wednesday 27 May 2020 @ 1:58 pm

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.




Repeating the first columns of a cross-tab for each value in the second column

Friday 8 May 2020 @ 11:44 pm

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.

cross-tab without repeating the first column

I found a relatively simple way to do this:

  1. I created a formula the combined the two column fields into one string, with a dash between them.
  2. 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.
  3. I then clicked the”Options” tab and checked the option “Customize Group Name Field”.
  4. I used the drop down to select the database field that was the original field used for the first column, then clicked OK.
  5. I went to the the last tab in the Cross-tab expert named “Customize Style” and highlighted the concatenated formula in the “Rows” box.
  6. I checked the option “Suppress Subtotal” (if not already checked) and clicked OK.

The cross-tab then looked like the one on the right.




Reading an Excel column as a true DateTime rather than a string.

Wednesday 29 April 2020 @ 10:43 pm

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.




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.




«« Previous Posts
Jeff-Net

Recrystallize Pro

Crystal Reports Server