Better Approach to Generating Quarterly and Annual Reports

Hi All,

This is more a question on better approach and/or validate my current approach on generating quarterly and annual reports.

I'm working on Investigation Tracking and Managment Reporting Project, I have in my source data sheet about 20 column headings(see A below), which are added or updated on a ongoing basis. I need to trend and report this data to fulfill requirements below(See B & C)


A Source Data Items

1. Unique Tracking Identification number
2. Computer System Impacted
3. Reportable Event Code
4. Root Cause
5. Original Due Date (Date initiated +30 days)
6. ISR Due Date
7. Current Due Date
8. Days Open
9. Cycle Time
10. Closed Date
11. Type of Investigation (ERF, IR-SF, IR-LF)
12. Site/s impacted
13. Site reporting the event
14. Inv Status (Open/Close)
15. Date Reported
16. Date Occurred
17. Date Observed
18. Assigned to (Lead Investigator)
19. Related IR #

B Quarterly Report Requirements;

1.Total Number of Invs open, closed and overdue.
2.Number of new Invs initiated in reporting quarter (by event codes, computer systems...) Trend data
3.Number of closed Invs in reporting quarter (by event codes, computer systems...) Trend data
4.Percent ERF-Only versus total Invs for a three-month view for the quarter.
5.Open Invs with Event Codes –clustered in a smokestack chart by month for a quarterly view.
6.Closed Invs with Root Cause codes- clustered in a smokestack chart by month for a quarterly view.
7.Number of MNCE or Percent of MNCE versus total Invs for a three month view for the quarter
8.Percent of systems closed on time versus overdue for quarter.
9.By monthly bands (0-30 days, 31-60 days, 61-90 days) how many Invs closed within each band for the quarter(Cycle Times).


C Annual Report Requirments;

1.Average Cycle Time for each of four quarters depicted on one page for an annual view
2.Total # of Invs open/closed for each month and quarterly bands(1Q,2Q, 3Q,4Q) how many Invs open/closed within each band for the year.
3.# of new Invs initiated in reporting year (by event codes, computer systems...)
4 .% ERF-Only versus total Invs for a quarterly view for the year.
5.All ERF’s breakdown by event codes.
6 .# of closed Invs in reporting year (by root cause, computer systems...) Trend data
7.# of MNCE or % of MNCE versus total Invs for a three month view for the quarter
8.% Overdue versus Closed on time for the year
9.By Quarterly bands (1Q, 2Q, 3Q, 4Q) how many Invs open/ closed within each band for the year.
10.Pie chart of invs closed (as percentage values) in 0-30, 30-60, 60-90 and >90 intervals.

Question 1

I currently Fulfill about one requirement per tab sheet, using adv filters & formulas for some and Pivot tables/charts for others.Are there other approaches that can be used to fullfil all requirements in fewer tab sheets, and make reports more presentable?

Question 2

Some (If not most) of the requirements for quarterly and annual are similar(e.g requirement 4 in B & C). Is it possible that I can generate quarterly and annual views using just one pivot chart and table, using excel front end features.

Your Responses are very much appreciated.

Thanks in advance
Nate

I would generate a range of bespoke sumproduct tables, these hold their formatting when the base data is updated, ie they dont scramble. Then do all the analysis and charting.