Month End Reporting Magic

Submitted on: Mon, 12.08.2014 09:35pm - admin |

Note: The following blog post was written by guest blogger Andy Weith.

How many reports do you run at month end, manually selecting a new date range each time?

For example, I run a commissions report the first of December for INVOICEDT between 11/01/14 and 11/30/14. The date function below, which is in about 25 MITS Report Sources at Crescent Parts, does it for me each month and won’t stop till I say ‘Uncle’:

SALES.INVOICEDT <= LAST_DAY(ADD_MONTHS(SYSDATE,-1))
AND SALES.INVOICEDT >=LAST_DAY(ADD_MONTHS(SYSDATE,-2))+1

I call it magic.

The fairy dust is all in the clauses LAST_DAY and ADD_MONTHS, which finds the last day of the month, and which month, respectively. So we use two statements here: the first to find the last day of last month, and the second to find the last day of two months ago, plus one day (the first day of last month). Voila, we have November 1 and November 30! Give me sales between those two dates Mr. MITS please and thank you!

MITS knows not to look for a November 31st. It knows when it’s January that the month I’m looking for is December of the previous year. Heck it even knows when it’s a leap year and February gets all crazy.

A small bonus? You aren’t required to run all of these on the first of the month because we’re always looking back a month and then figuring out the dates. Run reports on the 2nd, the 5th. Give people a break knowing they’ll already be hit with 100 reports on the 1st from other tools that aren’t as smart as MITS.

Those of you already rocking MITS Report will note you can update the source on Dec 1, but not send the report until Dec 5 as an alternative. Potato-potahto; both options are awesome and not available with our previous BI tools.

Have a favorite MITS report example to share? Bring it!

Andy Weith is the Founder of ASW Global Consulting, focusing on helping businesses better utilize the technology they have and improve the processes they employ. In addition he is acting CIO of Crescent Parts & Equipment, an HVAC and Refrigeration wholesale distributor, where he enjoys using MITS every day!