Compile Data and Produce a Dashboard

[NARAYANK991]

Hi Mahendra ,

I think it may not be right that I interject at such a late stage , but don't you think the Dashboard looks a bit tame considering the work that has been put into developing the workbook itself ?

Surely the data being made available on the Dashboard page is not desirable ; after all , that is what charts are supposed to be there for , to make it easier to visualize data ? To put in all that data there , and to push the third chart down does not appear very presentation-oriented ?

Can you say for whom the Dashboard is intended ? Are their purposes being met by just the 3 charts ?

Would it not be nice if you could have some kind of a drill-down so that details of at least the Critical items are available for further analysis ? A Dashboard is supposed to focus attention on the right needles in a haystack !

Narayan

 

[Mahendra S]

Hi Narayan, Charts is only an extended form of report which is best example to represent three dimension report. Yes surely you can do this in other ways like using pivot, but to consider less code effort the current implementation is best as per our understanding.

The Dashboard is suitable for anyone who has similar kind of filter on source/group to have the summary and numbers at high level. This can be customized with your own data which is the reason this is refereed as template in our own words.

Certainly this template format helps for projects that have heavy operations lift.

I encourage you to come up with detailed analysis to support drill down capabilities so that it will be a easier to implement. I always believe in with right design and approach upfront so that you will have clear outcome, otherwise you will get randomized with implementation effort.

Please come up with sample format in new tab so that we can review for any critical analysis further.

Regards,
M

 

[Luke M]

I think Narayan wants me to work harder.

 

[Mahendra S]

Hi Luke, I appreciate all the support you have provided so far and the template simply looks great.

Few things I would like to discuss here.

Enhancement1: I have added the button 'Snipshot of DP' basically the functionality of this button is to generate an image file of DefectsPriority tab with cell range (A1 to M(x)). M(x) can be dynamic value based on PublishSet
for example the snipshot range would be (A1 to M26) in attached latest template.

The image file required to be in either jpg or gif format and automatically saved to local directory upon clicking the Snipshot of DP button. (automatic save location would be on local drive where ever this new draft template is available, for example if u have this template in c:\Luke directory, then the jpg or gif ( Snipshot-DP-<MM/DD/YYYY>.jpg) needs to be saved to the destination c:\Luke\snipshotarchive\

Not sure the feasibility of implementation since this requires dir and file/save object to implement.
Please let me know what you think on this feature.

Enhancement2: Need to have additional header in DefectsPriority and MasterDataworksheets for header purpose. This was my fault I haven't included earlier. However I think it is possible to have this. But after inserting a row(A1 - new row added in template and saved) in Defects Priority and try to run Publish from SuperAdmin.
It is throwing following err



I believe we can fix this issue. as I am sure no more rows will get added on top.
If not please let me know we can still live with out additional header row in DefectsPriority as well as in Master Data tabs. (In latest template I have only added a row in DefectsPriority and need to add new header row in Master Data worksheet as well)

Enhancement3: I would like to control the alignment format of columns in DefcetsPriority as well as in MasterData tabs.
Can I have the column name = align center individually for each column in the code
rather than to have like range as we have now
Worksheets("MasterData").Range("B:C, D:E, H:H, I:I, Q:R").HorizontalAlignment = xlCenter

can I have xlCenter property as below (for example I have given few here)
Worksheets("MasterData").Range("J:J").HorizontalAlignment = xlCenter
Worksheets("MasterData").Range("L:L").HorizontalAlignment = xlCenter
Worksheets("MasterData").Range("M:M").HorizontalAlignment = xlCenter
Worksheets("MasterData").Range("N:N").HorizontalAlignment = xlCenter

All the columns values need to be center aligned except Title, CustomerImpact, Description, Progress/Resolution.
in both DefectsPriority tab as well as Master Data tab

This way I will have clear control on the alignment, but please advise on this.

Issue1: After adding a header row in DefectsPriority tab I think I lost the color format for ETA ToFix column.
is this b'cos of new row got added in this tab
If not possible to fix we can have with out header no problem, please let me know.

Clarification: Is it still required to have ALL in A2, B2, C2 in DP Tab, please let me know, I think we have this in code already but u might be keeping for hidden value to support the filters.
If these values not required in above cells, we can clean it up please let me know.

Thanks for all the great support!

Regards,
M

 

[Luke M]

Re: Enhancement1
New module and macro added to export picture. Code assumes that the folder "snipshotarchive" exists. I also had to change date format to use periods, since slashes mess up the file name.

Re: Enhancement2
Thankfully, I have been working mostly with Table objects, trying to keep things relative, so didn't need to tweak too many things. I believe i've changed it now so you can add as many header rows as you would like.

Re: Enchancement3
Horizontal Alignments have been moved to a separate macro, called ColumnAlignment. Uses a Case Select method to set alignment, should be easy to adjust if needed. Uses Table column names for added robustness.

Re: Issue1
I'm not seeing this anymore, so must have been tied to header row issue.

Re: Clarification
Those cells are just being used to populate the formulas showing "Total number of...", and what's been filtered.

 

[Mahendra S]

Thanks for the update Luke!

Just did a Publish which is working. Clicked the Snipshot of DP button (I do have the snipshotarchive folder)
It is throwing following err



a friendly reminder we should have err handler

Will verify other enhancements later, but looks like they are working

Regards,
M

 

[Luke M]

Hello Mahendra,

You're correct about the error checking, I put it in there, but never turned it on. Oops. While I was fixing that, I realized that over the course of development, there have been several macros with ScreenUpdating and EnableEvents getting turned on/off, and I went through and cleaned it up. I think it's reduced the number of "screen flashes" greatly.

That error was an annoying little bugger to figure out. Wouldn't always appear. After much digging, it appears it's a problem with XL itself, in that it doesn't stop to make sure it's finished writing to clipboard before attempting paste. So, I added a manual Wait into the code, and it seems to work now 100% of time.

 

[Mahendra S]

Thank you very much Luke, snippet image is working, cool feature.
Will verify other scenario's and let you know.

Have a good evening!

Regards,
M

 

[Mahendra S]

Hi Luke,

The template works well with Publish and snapshot, I would like to discuss below

Issue1: When ever you try to run with Publish = Yes for all then the DP Tab is showing correct.
In next run if you select few and Publish = No for few then the bottom rows are getting colored with Yellow rows


This should not be the case, some formatting is getting this corruption, not sure from where this yellow color is coming.Please investigate, I will try from my end but thought let u know this.

It might be a symptom of previous cache while clearing the content it is still staying in the worksheet, this may be in correct.

Enhancement1: The picture from Snipshot DP is very nice, I believe with this enhancement we will have more control.
As-Is if you generate snipshot the file is getting generated how ever if you attempt second time or third time it will not bring latest jpg to snipshotarchive folder, b'cos the file name is same.

I believe there are two possible solutions to fix this issue.

One would be to have current file name to be appended with timestamp as Snipshot-DP-04.16.2014-07.58PM and next time after two minutes if u try to snippet again the file name should be Snipshot-DP-04.16.2014-08.00PM.
Hopefully attaching timestamp is simple and this issue can be resolved.

Other way would be appending the numbers to the tail so that we will have first time as Snipshot-DP-04.16.2014
and second time we can have Snipshot-DP-04.16.2014-2 and so on based on Snippets.

Please let me know one of above option is feasible to implement or not.

Enhancement2:Also size of the jpeg file may be large sometimes, I am thinking little beyond, will it be smaller size of the file, if we go with gif or bmp format or any other picture supported, but not sure, please let me know on this.

Thanks again for the great support, and kudos to you!

Regards,
M

 

[Mahendra S]

Yes I forgot to mention, the flash got reduced while Publishing which is very good improvement!!
***** for great work.

 

[melody]

hey maybe this workshop helps you if you have problems with excel: http://www.excel-aid.com/excel-compare-listpivot-table-properties-and-options.html

 

[SirJB7]

Hi, melody!

As a new user you might want to (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regards!

 

[Debraj]

With a hearty congratulation.. and a BIG CLAP for Luke M

Can we close this thread.. for further post..

Approval request to.. Mahendra S too..

 

[Luke M]

With a hearty congratulation.. and a BIG CLAP for Luke M

Can we close this thread.. for further post..

Approval request to.. Mahendra S too..

I'd prefer not to close. While the title may have been initially misleading, this is all one project. I see it as very helpful reading for future readers, as they can see the development path that this took.

@Mahendra S
Re: Enhancement1 & 2
It's no problem to add a timestamp. Uses a format of MM.DD.YYYY hhmmss
Showing the second, coupled with the fact that there's a forced 1 sec delay in macro, ensures that we don't have to worry about dupliace files.
I tested saving as different formats, and it looks like a png format will work best for file size reduction. A bmp was terrible...

Re: Issue
Not sure how that color was getting in there...wasn't a change event, which was my initial guess. Was appearing when we resized the table. I added a line to FilterRows to clear out the old formatting before pasting in new data, and that seems to have done the trick.

 

[Mahendra S]

Thank you very much Luke, the time stamp works perfect!
and Yellow color issue is resolved looks like, but I will do stress test some time later to see the behavior and handle with DP Snippet.

Earlier my ***** are for Luke for his all time great support, and I agree we shouldn't close this thread at this moment.

Since the effort behind multiple feature set built in this program all associated and will be great help for any other software design and development which has same feature set.

Luke, probably we should change the title of this thread to some meaningful name, please suggest a good program name and I authorize you to change the name of the thread if you are able to.

Regards
M

 

[Luke M]

My suggestions for thread names:

• Dashboard construction project
• Create Summary report with Dashboard
• Compile and produce dashboard

 

[Mahendra S]

Hi Luke, Third option looks somewhat near to overall. Will you be able to change the title of this thread? not sure.

How ever few issues I want to discuss with you.
Issue1: I think we lost the non editable functionality for all the columns except Status and Progress/Resolution,
remember these columns need to be un protected before Publish and once everything completes protect except the data in Status and Progress/Resolution columns, this is the original req.
Did we loose this functionality after snipshot implementation.

Please let me know if we can get back this functionality in Defects Priority tab.

Enhancement1: The status(col G) and status icon(col H) need to be in sync after any chnages in column G cells.
Please note I have changed the status to Ready To Test in row 21.


we need to sync the both col G and col H data before closing this DefectsPrority tab or before running the snipshot proc and any other possible event. Otherwise you will have misleading information, please let me know on this enhancement.
Attaching the latest template for your reference.

Thanks for all the support.

Regards,
M

 

[Luke M]

Yes, I can and did change the thread title.

Re: Issue1
Yep, looks like the Snipshot macro was unprotecting the sheet, but then not re-protecting it. I've modified that macro appropriately. Functionality appears to have been restored.

I added a worksheet_activate event macro to Defects tab. When you select the tab, Status bar will indicate that a refresh is happening, and update icons. The Snipshot tool will also refresh the icons before taking the snipshot.

 

[Mahendra S]

Hi Luke, Thanks for updating the title of this thread.
Looks like protect issue with DP got resolved and icon sync is great but will I will verify further later on this.

Regards,
M

 

[Mahendra S]

Hi Luke, I hope you are doing good!

Thought I will discuss with you on Reverse Synchronization feature as we have most of the functionality.
Really need this feature if we can make it as we post poned this in the past due to complexity involved.

Requirement: We can have a button in SuperAdmin with name 'DP / Source Sync'
(pl see the latest template for ref)
When you click this button, it should update Source tabs with updates from DP to corresponding rows in source tabs.

I know we have highlight with color for cell in place for manual work around.
But if we have this synchronization feature in place it will help a lot and reduces manual errors and update mismatches between DP and Source Tabs.

We can attempt with Status column match between DP and Source Tabs to start with small set and later we can extend with other column Progress/Resolution.

One possible way is to have a function/proc with unique value to see is Ticket/Defect#
and other possible parameters I would think WS Name and WS Code.

I am sure we can implement as this one bit tricky but only one time run from Super Admin which makes bit less complicated if I understand the approach correctly.

Please let me know the possibility on above requirement implementation.

Regards,
M

 

[Luke M]

Hi M,
Yes, I've been doing pretty well.

Well, I made an attempt at building the macro, but I've hit a snag. The new module, ReSync contains the macro. Macro goes through DP table, finds original records, and updates them. This works (seemingly) well. However, here's the problem:
If I open this file and Populate DP sheet with CopyRows macro, everything is good.
If I then run the ReSync macro, still good.
If I then run the Populate DP after running the ReSync, bad news. Suddenly the code starts crashing, and none of the On Error commands seem to work. It has me quite stumped for the moment. Uploading the file just so the two of us can tackles simulataneously, see if you can find something I've missed. Again, this is not a good workbook. Each macro works well on it's own, but PopulateDP will not run correctly if run after ReSync.

 

[Mahendra S]

Thanks for the prompt response Luke, I will take a look.
There should be sequence of activities, will find out later today.

Thank you very much for great support.

Regards,
Mahendra

 

[Mahendra S]

Hi Luke, I think the solution is working with small subset of data.


The sequence always like this
1. Run the Publish DefectSet with CopyRows.(We need to make sure Master Data and DashBoard also get updated accordingly. - Yes I think this works perfect.
2. If any changes in DefectsPriority tab, need to save the tab after changes as we are highlighting with yellow.
3. Then need to run the DP/SourceSync to have the updates reflected back to source tabs.
4. Then run the Publish DefectSet again to have the latest data from source tabs.

Issue1: Only thing I had noticed with small subset of data is the icon is getting expanded bit stretched as shown below, when ever you have updates in cells by double click.(attaching the latest template, no change in code)



I can evaluate large set of data in next steps to see if any issues.

And also we may not need all columns to be updated always in Defects Priority Tab.
Only columns I see which will have updates (Status, Priority, AssignedTeam, Assigned Owner, ETA To Fix, Progress/Resolution, Defect Close date)

Issue2: Need to control the edit capability of above columns to admin only and when you close the template it just need to be readonly and protected the DefectsPriority Tab.
Please suggest a design approach for this issue. I will also think about this.

Regards,
M

 

[Luke M]

Re: Issue1
I've changed the CopyShapes macro to use the constant rowHeight, rather than actual cell height. Since both the Master Sheet and DP also use this constant, it keeps the shape from getting distorted.

Copy Data
Changed the ReSync macro, commented out the lines we don't need. Now it only copies the items you listed.

Re: Issue2
Need more clarity as to who can change what. When normal person opens workbook, do they need to be able to edit anything? If so, we can just password protect the entire workbook. If not, is there a single admin password we can use for the worksheet, or do we somehow need to tie it into the Super Admin sheet (multiple users/passwords). Can admin have write access to all cells, or still limited to just those specified?

Also, from previous posts, we've limited which cells can be protect to only the Status and Progress columns. Depending on how we setup protection.

Re: Continuing problem
The issue with ReSync messing up Publish macro does not seem consistent...can't figure out what consistently causes it yet. Attached is workbook with slightly updated code.

 

[Mahendra S]

Thanks for the update Luke! Appreciate all the good work.

Regarding Issue2: I do think following scenario's needs to be carefully handled, some intelligence here to distinguish before opening DefectsPriority tab as below:

Scenario1: Admin should be able to edit only above columns (Status, Priority, AssignedTeam, Assigned Owner, ETA To Fix, Progress/Resolution, Defect Close date) in Defect Priority.
Other columns should be non editable for admin as well.
For others/ normal user it should be only read only and non editable.

Scenario2: As soon as Admin signs on for Super Admin, we should open the gate for Super Admin and Defects Priority tabs (editable for only those columns identified) for Admin users only as we have in uname as-is what we have.

Scenario3: If admin directly clicks on Defects Priority tab it should not ask for Sign On, it should still be read only.

Scenario4: If normal user clicks on Defects Priority tab it should not ask for Sign On again, it should still be read only.

Scenario5: As soon as Admin updates the Defects Priority tab in any of these columns and close the tab it should save and read only. Probably close event we need to make sure to turn the Defects Priority tab read only.

Please let me know what you think on above each scenario.

Also another enhancement for review.
Enhancement1: Need to have Format Cells -> Alignment -> Wrap Text for all the cells in Defects Priority.
Can we have this setting in the Format macro where ever we have other formatting.

Regarding ReSync crash, I will attempt to load more data and see if any issues later in the evening.
Please let me know above scenario's and enhancement.

Regards,
M