Compliance Reporting
Experts,
I have compliance reporting to keep track of.
The reporting is grouped as follows
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
I have compliance reporting to keep track of.
The reporting is grouped as follows
Annually
Quarterly
Semiannually
After each Disbursement Date
Quarterly
Semiannually
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
ASKER
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
<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
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.
Quarters
2014Q1 = 1/1/2014 through 3/31/2014
2014Q2 = 4/1/2014 through 6/30/2014, etc.
Semiannual
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.
Quarters
2014Q1 = 1/1/2014 through 3/31/2014
2014Q2 = 4/1/2014 through 6/30/2014, etc.
Semiannual
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
ASKER
Russell, Im not following...sorry.
Specifically, I am referring to this part:
Quarters
2014Q1 = 1/1/2014 through 3/31/2014
2014Q2 = 4/1/2014 through 6/30/2014, etc.
Semiannual
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
Specifically, I am referring to this part:
Quarters
2014Q1 = 1/1/2014 through 3/31/2014
2014Q2 = 4/1/2014 through 6/30/2014, etc.
Semiannual
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
ASKER
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.
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.
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.
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.
ASKER
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?
ASKER
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
I need sort of a skeleton of what to do and the VBA code. Appreciate your help
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:
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/2014You'll enter 9/16/2014 and the button will create the entry, automatically adding the 60 days for you.
ASKER
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.
The lead time is 0 but more rather a "do within". Ie the report must be within 60 days after quarter end.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
nice! thank you. I might need to ask another question in regards to this as I get further along. thank you once again....
No problem. Sorry for leading you down the garden path at first.
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: