MITS Report Tip: 4 easy steps for comparing day(s) of the week over multiple years

Submitted on: Thu, 07.03.2014 07:48pm - Annie Eissler |


Distributors, like other businesses, have a need to compare values from the current year to previous year(s).  The simple way to address this is to create a report that compares a date with the same date in the previous year(s).  This works well if your business has consistent sales volume over the week, but most distributors have peaks and valleys throughout the week.  This issue, therefore, requires a different solution where the user is able to compare days of the week to the same days of the week in the previous year(s).  

One way MITS has seen this handled is with a prompt allowing the user to select the exact date ranges they want to compare.  This provides the proper results but is time consuming and introduces risk, as every time the report is run the user needs to open up their calendar to determine the date ranges for which they are looking.  This solution also assumes that the person running the report completely understands the purpose of the parameters and selects the proper dates.  

MITS Report is a perfect tool for creating this kind of report as Tim Trainor, MITS BI Solutions Developer, recently found while working with one of our MITS for Prophet 21 (P21) customers.  Here is a quick summary of how Tim recommends you put the report together:

Step 1
First, you will need to create or update a Report Source to include a column for day of the week and week number.  For example, in SQL Server (P21) the code would look like this: DATEPART(datepart, date) where “datepart”  is year, quarter, month, day, week, weekday and “date” is the date of which you want the part.  For other databases a simple google search should provide you with the code to find the needed values.

Step 2
Once the source is created you can create a report that prompts the user to select the day(s) of the week you are trying to compare.  Then select the week or weeks you are trying to compare.  Finally, select the years you are trying to compare.  You can add as many years as you want (in Tim’s example, he used two years):

Step 3
Once the user selects are applied, the following report is produced.  Tim just assumed some simple columns, but the report could display any column in the P21 database that is in the source file. Here is Tim’s sample report:

Step 4
You can then click into each Branch Year combination to see the invoice line that generates the top totals. Tim’s example made some column assumptions, but they can be changed with any display and any column in the P21 database:

We’ll be posting more MITS Report Tips here on our blog in the future.

In the meantime, we’d like to hear about the kinds of reports you are creating with MITS Report—the options are practically endless.