[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Compliance Reporting

Posted on 2014-08-31
Medium Priority
Last Modified: 2014-09-03

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
Question by:pdvsa
  • 7
  • 6
LVL 14

Expert Comment

by:Russell Fox
ID: 40295802
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?

Author Comment

ID: 40295853
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
LVL 14

Expert Comment

by:Russell Fox
ID: 40296845
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

ID: 40297151
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

Author Comment

ID: 40298383
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.
LVL 14

Expert Comment

by:Russell Fox
ID: 40298866
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.

Author Comment

ID: 40299032
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?

Author Comment

ID: 40299052
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
LVL 14

Expert Comment

by:Russell Fox
ID: 40299133
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.

Author Comment

ID: 40299349
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.
LVL 14

Accepted Solution

Russell Fox earned 2000 total points
ID: 40301572
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.

Author Closing Comment

ID: 40301727
nice!  thank you.  I might need to ask another question in regards to this as I get further along.  thank you once again....
LVL 14

Expert Comment

by:Russell Fox
ID: 40301742
No problem. Sorry for leading you down the garden path at first.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question