Comprehensive Report for Analysis

I looked at both tables they look nicely formated and well done !
Can you pls specify what you are after ?
What type of report you want ?
what are the fields that are mandatory to you and the ones that can be overlooked ?

This would give a better grip on how to approach what you need
gowflow

Hi Gowflow,

Since the tables are good to use, I am going to maintain the same format for my Dashboard reporting. The Dashboard report is actually an extraction data from employees who are using the same Daily KPI & productivity excel sheet for everyday operation. I have modified Time Index (Sheet-1) based on new table but the dynamic chart in Weekly Summary Report (sheet-2) has lost the focus because of this new changes. In this new report I have incorporated benchmark for each main component (Media Processing/Work Order, Scheduling, Localization and Channel Ops & Admin). This needs to be incorporated as well in Chart but I have no clue how to create this.

I hope with this sample report, you could rationalize the purpose of having this report. I believe my chart is not up to standard to capture individual performance and compare it with peers (I have already grouped them into Associate, Media Coordinator and Scheduler). Therefore I would to have your expertise and creativity to make this report more effective. Hope you can help me to create a dynamic chats which are capable to describe the individual performance and the group performance as well. Attached is the sample Dashboard Report that was created for this purpose.      

ID-26434997-Productivity-Report.xls

Opps, sorry wrong attachment: here's the right one
ID-26435005-Dashboard-Report.xls

Am a bit confused here:
1) You mention:
I have modified Time Index (Sheet-1) based on new table but the dynamic chart in Weekly Summary Report (sheet-2)
but in fact  Weekly Summary Report is in Daily KPI file but you are talking about Weekly Productivity Report in Weekly Dashboard file right ?

2) You want to link the charts to the existing tables in Weekly Productivity Report sheet ?
3) I have incorporated benchmark for each main component (Media Processing/Work Order, Scheduling, Localization and Channel Ops & Admin). This needs to be incorporated as well in Chart
You mean you want to ADD to the existing 4 graphs this info ? or you want to add this info to the exisitng tables and then populate it to the graphs ? It is not clear what you are after
4) I have already grouped them into Associate, Media Coordinator and Scheduler).
In Time Index you have: Media Processing/Work Order/Scheduling/Localization/Channel Ops & Admin
I do not see your groups pls clarify !

rgds/gowflow

Hi,

Time index Sheet is actually a consolidation data table from employees. Each staff need to capture their performance in Daily KPI & Productivity workbook. The data that were gathered in this workbook are transferred into this Time Index workbook according to the names (Row-A,Time Index). Each name in this Time Index represents an individual performance.
 
>1. Weekly Summary Report is actually a workbook to capture individual KPI performance. Here I have 16 staff and therefore I will have 16 Weekly Summary Reports and I transferred all these data into Time Index  (Weekly Dashboard Report workbook). In other words, I have 2 workbook, 1 for capturing individual performance and the 2nd workbook is a consolidation data for all staff. Since my table in Daily KPI & Productivity looks OK, thus I standardized this table to be used to all staff  and transferred this data into my Weekly Dashboard Report workbook. Hope my explanation is clear for this question.

>2. The Chart should be linked to the data that were listed in Time Index (Weekly Dashboard Report). Each name in Row-A in this workbook represents individual performance for that month.    

>3. You're right, I would like to ADD this new info in the graphs based on information provided in Time Index (Row-A86).

>4. In Weekly Dashboard Report,  there are 5 graphs (left to right), 1st graph represents all staff (16names), 2nd graph (7staff) belongs to Associate, 3rd graph (5staff) representing  Schedulers.4th graph belongs to Media Coordinators (4staff) and finally 5th graph, shows total hours performed by all staff (16).

I hope my explanation is clear. Please let me know if you need more clarification and I am really sorry if my explanation not giving you a better picture.
         

Hi gowflow,

Please do let me know if my explanation not clear.

ok I will. Was aways the past 3 days had no conncetion. Once I look into it will give u a feedback
gowflow

Thanks, take care and enjoy your day:)

Hi Theva,

I looked up your explanation and looked it against what you send me and seems some discrepancy. First I attached the file you send me maybe its the wrong version pls chk it. I will comment point by point on what I found:

>1. Noted and understood. You have 2 workbooks:
Daily-KPI---Productivity-Report-.xls (16 copies 1 for each employee they plugin daily info)
Weekly-Dashboard-Report-New-Ver-.xls (1 copy that you handle to gather info produced by KPI and to produce graphs and stats)

>2. The Chart should be linked to the data that were listed in Time Index (Weekly Dashboard Report). Each name in Row-A in this workbook represents individual performance for that month.
>>>> Wrong. Now the charts are linked to the table above the charts. The tables get their info from Time Index Column A (You mention Row A, I presume you mean Column A). Is this how it is supposed to be ? Chart linked to the small table and table updated from Time Index ? I find it correct !

>3. You're right, I would like to ADD this new info in the graphs based on information provided in Time Index (Row-A86).
>>>>> Cell A86 contains NOTHING actually last cell in your book is A81 so pls Explain. I presume you have sned me the wrong workbook !!!

>4. In Weekly Dashboard Report,  there are 5 graphs ...
>>>>>> WRONG I only have 4 graphs pls chk also seems I have the wrong version

Rgds/gowflow

ID-26453334-Dashboard-Report.xls

Hi,

Sorry for giving you wrong version, never though I'm capable to do this type silly mistake. Apology for that.    

>1. You're absolutely correct.
>2. Also correct, the small table gets their info from Time Index, please maintain the format. If you have better idea then this, please proceed.
>3. It shows my clumsiness, sorry for that. I attached the correct version.
>4. Again, sorry for this mistake.

Here's the right version. Sorry for the inconvenience caused.

ID-26453728-Productivity-Report.xls

Hi,
Actually you did not do a 'clumsy' thing, you did me a favor by giving me the file that work and the file that you want to acheive ... much easier to decode ! I'll work on it and revert

gowflow

Hi Theva,

In RBS Weekly Dashboard Sheet Time Index
1) Every Staff has a command button linked to macro copy_paste and in col A underneith the command button there is his name on each line. As I do not have the files and cannot test the macro but what I saw is that you are not polling the correct data. I will assume all the info you have already beside each staff to be the correct data and you shall take care to get it there RIGHT ?

2) I wonder how will the data be imported say in May where you will have not only 5 lines per staff but over 20 ! will the command button expand vertically to include the 20 lines ? My question is that I will assume that whatever data is in Col A (staff name) I will pull the coresponding details.

3) In the tables you have section Ezwan and the only group not represented is Chanel ops and Admin is it the same ? pls confirm

4) Presume you want to add all the times in one group and show it the coresponding time frame in the table ? or else pls clarify how to populate the times in the tables. In the old table you had 1 col populated in 1 graph now you have several col in 1 graph you want to group them or see the details ??

5) An other question in sheet 'Weekly Productivity Report' the tables have 5 lines coresponding to the data in Time Index how would you like to acheive the expansion like additional data ? in month 5 you will have 20 lines in month 9 you will have some 40 lines and end year you will hv 52 !!! so do you think it is wyse to keep all 5 graphs in 1 worksheet ???
... At this level I think it would be wyser to concider 1 sheet per graph. Like you would have a sheet
'Weekly Productivity Report- RBS Employee' that will have its table together with its graph !
Basically you will have 5 sheets

... Or maybe 5 sheets each having the tables and 1 sheet grouping all graphs ???

Don't really know you can best judge how you want to view the data and what is best.

An other thought is I see you have in the tables the month January what we can do is to keep the tables as monthly basis and when you change the month then it populate the coresponding data !

Sorry lot of questions but your request is somewhat very broad
rgds/gowflow

Sorry Ignore my point 3) it is the cumulative table
rgds/gowflow

Hi Jack,

>1. You're right, I set the copy and paste based on name with a specific range. I have tested and its working. Actually I'm using a workaround process to get this result. Since this is workable thus I rather stick with this method at this moment.

>2. I've allocated 5 lines (weeks) for each staff. I will get staff feedback on their performance each week (date). I also noticed staff always confused with the date. Realized this when they start entering February's KPI. If you have better solution please incorporate this in your design.  I believe your solution always give me the best approach.

>4. I prefer to have same dynamic chart type, but believe me, I  got this idea based on my limited knowledge. If you think you have better approach (no doubt about it)  to handle this, please incorporate this. End of the day, all we need is to see staff performance and comparison with peers.

>5. I like your idea. At this moment i don't know how I am going to combine all months and show this in Chart. In this case, I totally depend with your creativity. I think  "5 sheets each having the tables and 1 sheet grouping all graphs" could give a reader (big bosses) better view. Again if you think this is too much just stick with a simple one.

I really need your ideas to create this report. Hope you don't mind to inject your creativity in this report as well like how you'd created the Attendance masterpiece. If possible try to maintain with standard MS Office Library to allow more users to get access with this file.    
   

OK clearer now. I need to understand following:
How often you need the reports like monthly ? if Monthly then we can keep you 5 weeks as it is the mx weeks in 1 month and take it from there we will then maybe create other charts then by month 1 line for the total month max would be 12 lines per chart. I think this way you can have the details when you want them (per month) and a total view for the whole year What do you think ?

For my item 2) your answer is not clear coxz you say I've allocated 5 lines then you want to see all hte months ? What is it ? Can you pull data for every single month and throw it in Table Index ? or it is too much for you ? Basis this then I can comeup with something intresting

gowflow

Hi,

I like your approach to have 12 lines, pls proceed.
>2. At present I only able to transfer a month data in Table Index, let say if I want to work for January, I can captured only January KPI, if need to be captured February than I have to remove January's data. Therefore I can't have months comparison with current version. Its grade if we could accumulate the data for consecutive months.      

Ahhaaaaaaa now your talking. Well all along I was not seeing how oyu would be accumulating all the data this is why I was asking you all these questions as I couldn't see how you would be expanding your lines to fit all this data for all staff .... well now it is much clearer you get a month print/save whatever then next month you take same wkbook replace the data print/save whatever etc...

Well this is somhow Archaiiic work and will surely get rid of it. For this I need to modify the import routine so I need the file that your staff has to see what you pull and how and from there will see how we can keep the rest.

Also need to know from you the file that your staff has is also a Monthly file like you erase previous month to put new month etc ... or its an ongoing month ?

rgds/gowflow

Hi,

Here’s the generic workbook that were used by staff to enter their KPI data. Staff enters daily KPI in sheet-1 (Detail Task), and the data will be captured in sheet-2 (Weekly Summary Report) – This is considered my new version that I’m going to use. I’ll export this data (sheet-2) into my Time Index work sheet for Dashboard report – this is where I need your help. I have sent the staff files that I used to extract the data using your email account; this is my current working version. Please check.

I hope I have sent you the document that you’re looking for. Please let me know if you need more info.

ID-26472637-Productivity-Report.xls

Yes this is the same file you hv sent before. But you did not answer my question and also in the file I only have january. What do you do with february ? you erase january then do Feb ? Can you send me one that have more than 1 month ?

You mention:
I’ll export this data (sheet-2) into my Time Index work sheet for Dashboard report – this is where I need your help.
I don't understand I thought You needed my help in the Dashboard file. Do you mean you need help to Export the data or the setup of Dashboard which was my understanding ?

What did you send to my email? pls only use EE chanels do not use personal emails for issues posted in EE so it can be followed by all Experts.

rgds/gowflow

Hi,

The February's data yet to consolidate since the month just started, furthermore we're not working on 1st of February (Public Holiday). I only have January's data for a time being.

What I meant by "I'll export" is referring to current workflow that I 'd been doing with my old version. I really need your expertise to export this data and setup the dashboard report. What I'm performing now is more like "Mr.Bean job", by luck the copy and paste method works.

I tried twice to push this file here in EE, but failed. It just shows "processing" but nothing happened. That's the reason I choose E-mail as alternative option. Even I just try to push again this file but for 3rd time its failed again. Probably the network is too busy. I'll try to push this file again, hopefully it works. Sorry if this is really annoying you. I wont repeat this mistake, sorry.
   

funny Mr Bean I like it !!!
Well my question was: now that your people will enter data in the KPI workbook will add Feb on top of January or you blaked out January and gave them a clean new one to enter data ? like the file they will be entering data now has data of January or not ?
gowflow

So when you say:
What I'm performing now is more like "Mr.Bean job", by luck the copy and paste method works.
You mean you did not use the command macro for each staf that you have there you simply did a copy paste ? is that what you meant ? Coz most probably I will be replacing these command buttons into a single one called Import Data !!!
gowflow

Hi,

I'm using the Macro for each staff even though it can be used for multiple files. Sometime the staff not sending the file on time and that caused the loop function not suitable.

At present some prefer to use new workbook and some just like to continue updating the February’s Data on top of January. I just edit the date in summary report accordingly. Its quite risky, they might lose the whole data if the workbook is corrupted. However, I think having a new workbook for each month is more appropriate since the data will be archived in Dashboard report.

Hi,

You’ve gathered all info perfectly. I concur with your suggestion. We should dissect this into 2 phases – 1st “KPI/Productivity Data Workbook” for staff to enter their daily productivity and 2nd “Dashboard Report”. If this is OK, I’ll close this and posting a new Q.

Well yes I see it more like this:

Phase#1 KPI/Productivity Data Workbook (with its detailed tasks a to c)
Phase#2 Dashboard Report Import Monthly (with its detailed tasks a to e)
Phase#3 Dashboard Report Import Yearly (with its detailed tasks f to g) (This could be in the same wb as Dashboard Report Monthly) but need a total new approach

Case agrreed, will concider this question Phase#0 the planning approach for Solving your KPI Project more efficiently as it is exactly what you aimed in your question when I read it again:
quote
1) I need Experts ideas to make my report more extensive and easy to analysis
2) If Experts have better idea to create this report, please do so
3) I really need Experts idea and creativity to generate a better report
unquote
I think ID: 26482435 has laid out the plan for you to actually acheive practically what you need to acheive and answered your question accordingly.
rgds/gowflow

Hi,

I have no words to utter your perseverance in noting all probabilities in making sure the project will execute properly. I salute with your forward thinking capability. Not all granted with this gift, in a way I’m envying YOU!  I'll post our 1st Phase in a while.

Hi, just post 1st phase Q:ID: 25113334. Please look at this when you have free time.

wow tks for the very nice comment and grade which sure shows your high appreciation and integrity.
tks/gowflow

Hi Mod,
Here's the replacement file.
ID-25094426.zip