Better Approach to generate Quarterly and Annual Reports

[nate777]

Hi All,

This is more a question on better approach and/or validate my current approach to 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

 

[Dufus]

Nate,
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?

Dufus: No, I think native Excel is your best bet for reporting on a database of information such as you describe. Until you decide that you want all the reports generated on the fly (Select a report from a list and then generate it...) you will still have a tab per report or a bunch of reports on one tab. A tab per report gives infinite formatting control. A bunch of reports per tab limit formatting options. Using a macro for on the fly reporting could reduce the additional tabs to one.

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.

Dufus: Yes, it's possible. Page fields in a Pivot come to mind here. One page could be each quarter so you only have to select the correct quarter and print without actually having a different tab per quarter for example. Be aware that formatting can sometimes change when Pivots change.