Unanswered: Validate/Better Approach to Generating Quarterly and Annual Reports
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.
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?
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.
I learned an approach that Charley Kyd developed; I participated in telephone Seminar. I developed and adapted it for our use in Analytics and it has proven so successful that my approach is now being set as the standard for the entire marketing department (for a Fortune 40 company). I trained the first group of Reporting and Analysis employees this morning.
What about some query tables and a little SQL? If parameterised, might be very slick. It seems suited more to a database-type approach. (Which suggests linking the table into Access and run the reports there as an alternative.)
To build on what Fazza wrote, Excel User (web site referenced above) provides the Excel sequence and display regardless of how the data is acquired. I do use VBA to pull data from the mainframe using Essbase into an Excel. That workbook becomes the "database" for the next Excel workbook in the file, which is data setup, and the data display in another workbook, and then I use VBA to copy a linked picture of everything into PPT. The VBA code for 198 markets with 27 separate channels for every week of the year takes 22 minutes. Once that is done I change one cell in the data setup and then update links to all 600 linked pictures. Whole process takes less than 45 minutes. Except for that one cell I never touch the data setup, or data display excel files, and never adjust the graphs/tables. It took 3 months to setup properly and make sure links worked (all the while still reporting the old way). So it wasn't the only project I worked on. Given time now, I can setup the entire process in a few hours (since I know what I am doing ).
That is only one of 10 projects that I have automated this way, but it is the longest and largest. Some take less than 2 minutes to update. It is the only way to handle these kinds of tasks. And by compartmentilzing each piece, there can be a change in the database structure and not affect everything else. In fact, we have had two changes in the Essbase pulls in the past 45 days, and the only thing that has to change is the initial XL file - everything else works perfectly.
Last edited by shades; 10-07-06 at 10:56.
old, slow, and confused
but at least I'm inconsistent!