Math tweet splits internet mathematicians

Tuesday 20 August 2019 @ 12:10 pm

One of my colleagues sent me a New York Times article about a math tweet.  The tweet was of a deceptively simple math equation and asked people to calculate the result.  All of the internet mathematicians immediately divided into two camps based on their interpretation of the rules of precedence, which were intentionally ambiguous in the equation. The point of the article (and of my colleagues sharing) was the importance of using parentheses to clarify the order in which things should occur. I often add extra parens, even when the order of precedence wouldn’t require them, just to give myself a visual clue of what is supposed to happen when.

The funny thing about the original article, and an interesting follow-up article, is that experts can still be found on both sides of the divide. That is somewhat surprising given the apparent simplicity of the problem.

And thanks to Zvi Flanders of Huron Consulting for sending me the link.





Calculating the begin and end of Daylight Saving Time

Friday 16 August 2019 @ 11:32 pm

I am not a big fan of Daylight Saving Time (DST). I even hear some states and countries are talking about dropping it (yeah!). But in the meantime there are plenty of reports that need to adjust the time twice a year. This usually happens when the datetime values are stored in Greenwich Mean Time (GMT) and have to be converted to a local time. Then you need to know when DST begins and ends.

The first two formulas below calculate the beginning and ending dates of DST, based on the year of your transaction date. The third formula uses the first two formulas to make the one-hour adjustment. Substitute your GMT date fields into the first and third formula.

//DST Start
DateVar Start:= Date (Year ({@YourDateTimeGMT}) , 3, 15);
DateVar BOM:= Start - Day(Start)+7;
DateVar BOW:= BOM - DayOfWeek(BOM) + 8;
DateAdd('h', 2, BOW);

//DST End
{@DST Start} + 238 ;

//Adjusted DateTime
if {@YourDateTimeGMT} in {@DST Start} to {@DST End}
then DateAdd('h', 5, {@YourDateTimeGMT})
else DateAdd('h', 4, {@YourDateTimeGMT})





When that zero isn’t really a zero

Thursday 8 August 2019 @ 10:24 pm

Last week a customer was really befuddled. He had a formula that said:

if {@field} = 0 then ...

He could see lots of zero values but the formula didn’t work as expected.  He couldn’t figure it out so he sent it to me. The first thing I did was add a few more decimals to see if it was a rounding issue. That didn’t show anything, but the formula still insisted that the value was somehow NOT equal to zero.

So I went into the formula and multiplied the current value by one trillion. Then instead of zeros I started to see some small numbers.  I am not an expert on floating point values or database precision but I have seen this before in reports.  The solution is to round the value in the formula before comparing it to zero. In this case we rounded the value to two decimals like this:

if Round ({field}, 2) = 0 then ....

That made the formula behave as expected.  The odd part is that I have seen the same problem with two other customers in the past week. It could be just a coincidence, but I figured I would mention this and see if this is happening to more people.





RPT management utilities for 2019

Sunday 28 July 2019 @ 10:37 pm

I have just updated my comparison of RPT management utilities for 2019. These are tools that allow you to scan, document, compare and in some cases batch update RPT files. The list includes 9 tools:

Report Runner Documentor by Jeff-Net
R-Tag Documentation and Search by R-Tag
CR Data Source Updater by R-Tag
Visual CUT and DataLink Viewer by Millet Software
Report Miner by the Retsel Group
Code Search Professional by Find it EZ Software Corp.
Dev Surge 365 by Find it EZ Software Corp.
.rpt Inspector 3 Professional Suite by Software Forces, LLC
.rpt Inspector Online by Software Forces, LLC





Preventing the “division by zero” error

Friday 26 July 2019 @ 9:52 am

Crystal formulas can use 3 different divide operators:

  • Regular divide [ / ]
  • Integer divide [ \ ]
  • Percentage [ % ]

But all of these will fail if you follow the operators with a zero. The report will stop and Crystal will throw the error message “Division by Zero”.  The standard solution is to check and make sure the number you are dividing by is not zero before you do the calculation, something like this:

if {fieldA} = 0
then 0
else {fieldB} / {fieldA}

This way, whenever the bottom of the fraction (denominator) is a zero, the formula will print a zero and NOT try to do the calculation.
But even when customers use this formula pattern I still see the divide by zero error. Some users mistakenly check the top of the fraction (numerator) instead of the bottom (denominator).  Some do it correctly at first, but then change the denominator and forget to change the first line to match.  So I have developed the habit of using Local variables to make things easier. My normal pattern now looks like this:

Local Numbervar n:= {FieldA};//numerator
Local Numbervar d:= {FieldB}; //denominator
if d = 0 then 0 else n/d

This ensures that the value being checked is always the value on the bottom. Some other advantages of this method are:
1) If d is a subtotal or a long expression you only have to enter it in one place.
2) If you have to create a series of similar expressions, like for 12 different months, you can duplicate the first example and you only have to change the values at the top of the formula.





How to make sure you are on the last day of the month

Friday 19 July 2019 @ 9:18 am

Lots of reports require that you compare two different date periods and I often calculate prior date ranges based on the current range. But you have to be careful when the end of your prior period falls in a month with more days the the current period month?

Here is an example. Lets say your current period is in June and your prior period ends 6 months earlier in December. Calculating the Start Date is simple. If your Current Start is 6/1/2019 you can use a simple DateAdd like this:

DateAdd ('m', -6, {@CurrentStartDate})

but if you try a similar formula for the End Date you won’t get the right date. The following formula will return 12/30/2018:

DateAdd ('m', -6, {@CurrentEndDate})

The same issue occurs when your prior period is a year before and your current period ends in February. If he prior year is a Leap Year your prior period end date will be off by one day, ending on the 28th rather than the 29th.

My solution involves adding one day before you do the DateAdd and then subtracting that same day back out again, like this:

DateAdd ('m', -6, {@CurrentEndDate} +1) -1

This works because adding the one day puts on you on the first of the month. This is always a clean calculation when moving forward or backward by months or years. Then once you get to the first of the month in the prior period you subtract one day which always puts you on the last day of the month prior.





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.





SQL Syntax for several common date ranges

Friday 21 June 2019 @ 8:26 pm

I have been writing lots of SQL commands lately for my reports.  I am doing this so much now that I regularly tap into my SQL cheat sheet which shows the most commonly used SQL functions and calculations for the seven SQL flavors that I see the most.

Recently a customer needed several date range functions to be used in the WHERE clause of a SQL Command. The list was quite similar to the date range function list available in Crystal. He wanted the following functions: YearToDate, MonthtoDate, LastFullMonth, LastFullYear, LastFullWeek, Last7days, PreviousDay.

So I did some research and found some excellent discussions on the best approach to take. For instance, I read that you don’t want to use any functions on the database field itself. I had started to use the Oracle TRUNC() function to strip the time off of the database field, but I read that this can prevent the query engine from using an index. However functions don’t affect the index when used to calculate the literal dates that you are comparing. I also read that when you don’t strip the time from the DB field it gets harder to use a BETWEEN comparison. The most knowledgeable posts I read avoid Between and instead used the raw DB field in two separate comparisons like this:

db.field >= CalculatedRangeStart and
db.field < CalculatedRangeEnd

The CalculatedRangeEnd is always the day AFTER your range ends, so you can use < to get all times on the last day.

This is what i came up with for SQL Server:

Previous Day
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) -1,0) and
X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullWeek
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) -6,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)
- DATEPART(dw, CURRENT_TIMESTAMP) +1,0)

Last 7 days(to yesterday)
X.DATE >= DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP)-7 ,0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

Month to Date(to yesterday)
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

YearToDate(to yesterday)
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)
and X.DATE < DATEADD(d,DATEDIFF(d,0,CURRENT_TIMESTAMP) ,0)

LastFullMonth
X.DATE >= DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(month, DATEDIFF(month, 0, CURRENT_TIMESTAMP), 0)

LastFullYear
X.DATE >= DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP)-1, 0)
and X.DATE < DATEADD(year, DATEDIFF(year, 0, CURRENT_TIMESTAMP), 0)

And this is what i came up with for for Oracle:

Previous Day
X.DATE >= TRUNC(sysdate) - 1 and X.DATE < TRUNC(sysdate)

LastFullWeek
X.DATE >= trunc(sysdate - 7,'WW') and X.DATE < trunc(sysdate,'WW')

Last 7 days (to yesterday)
X.DATE >= TRUNC(sysdate) - 7 and X.DATE < TRUNC(sysdate)

Month to Date(to yesterday)
X.DATE >= TRUNC(Sysdate,'MM') and X.DATE < TRUNC(sysdate)

YearToDate(to yesterday)
X.DATE >= TRUNC(Sysdate,'Year') and X.DATE < TRUNC(sysdate)

LastFullMonth
X.DATE >= TRUNC(TRUNC(Sysdate,'MM') - 1, 'MM') and X.DATE < TRUNC(Sysdate,'MM')

LastFullYear
X.DATE >= TRUNC(TRUNC(Sysdate,'Year') - 1,'Year') and X.DATE < TRUNC(Sysdate,'Year')





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