Never Underestimate Your User. Expect the Unexpected.

Never underestimate your opponent. Expect the unexpected.

Never underestimate your opponent. Expect the unexpected.

Earlier this week, I was reminded of this post from some time ago. Yes I know I’ve already used that picture, but you can ever have enough Swayze. The company in question uses a 4-4-5 Calendar for accounting and this makes reporting more difficult. For example the month of March started February 28th and ended April 3rd. Something like the following will not work:

sum(CASE month (SOM.forderdate)
                   WHEN 1 THEN (sor.forderqty * SOR.funetprice)
                   ELSE 0
               AS [Jan],

The code is more cumbersome for a 4-4-5 system, but I welcome the challenge and it makes me a better writer of T-SQL. I’ll post the code for how I do this using an external calendar table in a future article. Anyway, I was asked to create an automatic sales report with the following criteria:

  • Sales orders received during the current month (4-4-5). This is of course month to date (MTD), but cannot be calculated using established formulas because the first day of the month is always different.
  • Sales order line items which are due before the end of the year. The end of the year is always 12/31.
  • Include customer number, name, and a grid that looks like the following:

		Cust		Jan	Feb	March	April	May	June	July	Aug	Sept	Oct	Nov	Dec	Total
Ordered		Acme Rockets	0	0	0	0	0	0	0	165636	0	0	0	0	165636
Shipping	Acme Rockets	0	0	0	0	0	0	0	100000	1250	9999	54387	0	165636

For simplicity’s sake, I’ve only shown one customer order. The report typically has several hundred records. Anyway, I wrote the code and tested it thoroughly. It was set to run as a scheduled job in Crystal two minutes after the end of the business day every Friday.

Everything ran fine for quite awhile until the CFO brought one of the reports back to me which showed an amount shipping in July (the previous month).

Since the code was fairly complex and difficult to write, I kept looking for mistakes. After looking for close to an hour, I decided to change the query to return every sales order represented rather than the aggregates (sums) per order.

When I isolated the sales order and line item, I found that while the order date for the sales order was in August, the due date was a month prior. Armed with this knowledge, I took the issue to the sales manager, and he told me that the information was correct. Company policy is to use whatever date the customer lists on their purchase order, no matter if it’s realistic or even possible.

What did I do? Well, I asked the CFO for a clarification and with his permission changed the code to include any line items with a date prior to the current MTD to reflect the current month in the report.

I guess my point is that you always have to expect that your users are going to do strange things. Expect it, plan for it, or get called on the carpet when your code is “wrong.”

Have any of you folks run into similar situations when reporting from M2M?

1 comment to Never Underestimate Your User. Expect the Unexpected.

  • I run into this all the time. Part of it has to do with being familiar with the system at my previous employer but not so familiar with M2M. Some of the terms that are used, acronyms in particular, are used with both systems but mean different things, so while I think I know what the person wants it ends up being wrong due to terminology.

    I run into similar problems due to user terminology which is more like what you’re talking about here. Users might tell me they want the Closed Date when they actually want the Due Date. I have two specific examples of when that happened, but I can’t remember now what fields they were getting mixed up. It was such a random difference in what the user called it and what it actually was that I just had no idea how they could have gotten the two mixed up. I don’t know, maybe I’ll remember later when I stop trying to think of what it was.

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>