Better Approach to generate Quarterly and Annual Reports

nate777

New Member
Joined
Aug 2, 2006
Messages
27
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
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,834
Messages
5,855,901
Members
431,772
Latest member
dannyboi1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top