Compliance Reporting


I have compliance reporting to keep track of.

The reporting is grouped as follows
After each Disbursement Date

In each Group, there are many items to keep track of.  For example, Annual reporting requires financials within 120 days from years end.  Quarterly reporting requires financials within 60 days from quarter end.  After Disbursement Date a Debt Service Coverage Ratio is  required within 60 days from the Disbursement Date.  I have many other items to keep track of in each group but for simplicity sake I am  keeping it simple.  

I will have to keep track of all this to an end date of 1/1/2020

I imagine some experts have designed something similar to this and can give me a few tips in how best to do this.  
For example, do I make fields for all the items to track or is a calculated field better somehow?  I am also thinking of a check box to track whether or  not it has been completed.  

Thank you
pdvsaProject financeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Russell FoxDatabase DeveloperCommented:
I think your best bet is to make a date "dimension" table, basically just a list of dates, every day through 2020, with columns for each of the time periods you need to track:

DimDate         Year         SemiAnnual      Quarter     DisbursementPeriod
1/1/2014       2014       201401               201401      3/31/2014
1/2/2014       2014        201401              201401       3/31/2014
7/13/2018      21018    201802              201803      9/1/2018...

Then you just join your data data field, like if it's an invoice date:
DateValue([InvoiceDate]) = DateDimention.DimDate
You need the DateValue function to strip the time part of your timestamp data type, otherwise your dates will not match. You can find some tools online that will generate this for you, though you'll need to tweak them to fit your particulars:
Are your quarters calendar year, or some weird financial schedule?
Do you know the Disbursement dates ahead of time, or will you need to add those when they come up?
Calendar year or fiscal year?
pdvsaProject financeAuthor Commented:
Russel, thank you for your response.   I am looking forward to hearing more.  

<Are your quarters calendar year, or some weird financial schedule?
==>Answer: Calendar year (jan 31-Dec 31)
<Do you know the Disbursement dates ahead of time, or will you need to add those when they come up?
===>Answer:  I do not. It would be every month though and quite possibly up to 5 disbursements per month
<Calendar year or fiscal year?
===>Answer: Calendar (jan 31-Dec 31)

I think I understand a good part of it but could you provide a little more explanation as to why your semiannual, qtr dates above are the same?  
I dont follow the DateFunction function and why its necessary.  I would prefer to sidestep any special function and try to keep as simple as possible.

thank you
Russell FoxDatabase DeveloperCommented:
I'll start from the bottom: the timestamp right now is [2014-09-01 07:57:10.813], but you'll need to do a JOIN with only the date portion, 2014-09-01, otherwise you will not get any matches to your date table. If you store your dates in a field without the time, you won't need the function, but most people use the timestamp. Without doing this, you would either need a date table with an entry for every millisecond, or you would need to do some sort of BETWEEN comparison on every query: this will be hell.

2014Q1 = 1/1/2014 through 3/31/2014
2014Q2 = 4/1/2014 through 6/30/2014, etc.

2014S1 = 1/1/2014 through 6/30/2014
2014S2 = 7/1/2014 through 12/31/2014, etc.

Since you are using calendar dates (assuming you meant Jan 1 - Dec 31), you can also just use the Datepart function to get this info (current quarter number = DatePart("q", Date())), but having the date dimension opens up a lot of possibilities. Below is the table I use which I got as an open source TSQL script from CodeProject (for SQL Server). There are a lot of columns, like displaying the current quarter as "Q3" or 201403, as well as tracking common holidays, including USA and UK. I show holidays on my reporting to remind management why we had no revenue that day.  

     [DateKey] INT primary key,
            [Date] DATETIME,
            [FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
            [FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
            [DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
            [DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
            [DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
            [DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
            [DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
            [DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
            [DayOfWeekInYear] VARCHAR(2),
            [DayOfQuarter] VARCHAR(3),
            [DayOfYear] VARCHAR(3),
            [WeekOfMonth] VARCHAR(1),-- Week Number of Month
            [WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
            [WeekOfYear] VARCHAR(2),--Week Number of the Year
            [Month] VARCHAR(2), --Number of the Month 1 to 12
            [MonthName] VARCHAR(9),--January, February etc
            [MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
            [Quarter] CHAR(1),
            [QuarterName] VARCHAR(9),--First,Second..
            [Year] CHAR(4),-- Year value of Date stored in Row
            [YearName] CHAR(7), --CY 2012,CY 2013
            [MonthYear] CHAR(10), --Jan-2013,Feb-2013
            [MMYYYY] CHAR(6),
            [FirstDayOfMonth] DATE,
            [LastDayOfMonth] DATE,
            [FirstDayOfQuarter] DATE,
            [LastDayOfQuarter] DATE,
            [FirstDayOfYear] DATE,
            [LastDayOfYear] DATE,
            [IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
            [IsWeekday] BIT,-- 0=Week End ,1=Week Day
            [HolidayUSA] VARCHAR(50),--Name of Holiday in US
            [IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
            [HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

pdvsaProject financeAuthor Commented:
Russell, Im not following...sorry.  

Specifically, I am referring to this part:
2014Q1 = 1/1/2014 through 3/31/2014
2014Q2 = 4/1/2014 through 6/30/2014, etc.

2014S1 = 1/1/2014 through 6/30/2014
2014S2 = 7/1/2014 through 12/31/2014, etc.

I am referring to specific days and not ranges.  
For example, year end financials are to be submitted withing 120 days of years end and within 60 days of quarters end.  I dont follow why a range would be important to this solution.  Obviously, I am not as adept as you and I just dont follow where we are going.  

thank you
pdvsaProject financeAuthor Commented:
I will give another example:  

Annual reporting
-within 120 days = 4/30/2015
-within 120 days = 4/30/2016
-within 120 days = 4/30/2017
to 2020

This same concept would be applied to all the other Groups.  Maybe you have provided me with a solution but I dont understand the date dimension table method you referred to earlier.  I can imagine I would need some kind of loop code that ends on a [EndDate]...something like that.  

let me know if you need more explanation.  I might be making this more difficult than it really is.
Russell FoxDatabase DeveloperCommented:
Yeah, I think I misunderstood the question. I thought you were looking for a way to pull data and group it into your time-frames, but it sounds more like you're looking to set up like a reporting calendar to keep on top of when reports are due? So your database entry might look like this:

ReportType    Period          LeadTime   RequiredDate
Quarterly        2014 Q4       60 days      3/1/2014
Annual            2014             90 days      4/30/2015
Quarterly        2015 Q1       60 days      6/1/2015
Disburse         9/16/2014    60 days       12/16/2014

Is that more like what you're looking for? Then you can run a weekly/monthly report that tells you what reports are coming up.
pdvsaProject financeAuthor Commented:
that is exactly what I am looking for.   I see we are both on the same page now.  What else can you tell me now?
pdvsaProject financeAuthor Commented:
I am now thinking I need some type of VBA code coupled with a Between function (not sure if that is how to say it in computer lingo).  Also, note that the [DisbursementDate] is on the fly...there can be many per month and I have to comply with many conditions with each of these disbursements but they are all the same conditions per disbursement.  

I need sort of a skeleton of what to do and the VBA code.  Appreciate your help
Russell FoxDatabase DeveloperCommented:
Gotcha. For the annual, semiannual, and quarterly, it will be easier to just generate those values once (which I already have, so I can send that to you). What's the lead time on the semi-annual report (60 days, 90 days)?

For the disbursements you'll probably want a simple form with a datepicker and a button: select a date that the disbursement occurred and hit the button to automatically add a reminder entry. So for this reminder from above:
Disburse         9/16/2014    60 days       12/16/2014
You'll enter 9/16/2014 and the button will create the entry, automatically adding the 60 days for you.
pdvsaProject financeAuthor Commented:
Russell, typing from phone
The lead time is 0 but more rather a "do within".  Ie the report must be within 60 days after quarter end.
Russell FoxDatabase DeveloperCommented:
Gotcha - sample database attached with reminders for annual, semi-annual, quarterly, and disbursements (same as quarterly), with a [Completed] checkbox that you can tick off when you've done the report - a simple form opens when you start it. There's no code at all, so you can hit "Enable" when the security message comes up. You can get fancy by making the rows turn color when they're coming due or overdue.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pdvsaProject financeAuthor Commented:
nice!  thank you.  I might need to ask another question in regards to this as I get further along.  thank you once again....
Russell FoxDatabase DeveloperCommented:
No problem. Sorry for leading you down the garden path at first.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.