Compile Data and Produce a Dashboard

[Mahendra S]

Thanks Luke, to understand more on template the properties we don't need to turn the properties on and off programmatically please correct me on this.

Also can we have the input box header as Create New Worksheet and disable the ok till the validation passes
currently for example I give "IBM Prod Defects" it is accepting the duplicate and creating the tab. We should have a message to user to enter a new name as worksheet exists.

I look forward for latest template.

Thank you,
M

 

[Luke M]

Hi M

Correct, you don't have to turn on/off properties. I was just showing one way you can use to unhide the template sheet, if you wanted to make changes to it. There's many ways you could use.

To add the input box header, change this line in Macros -> CreateWS:
newName = InputBox(myMsg)

to this:
newName = InputBox(myMsg, "Create New Worksheet")

and that will add the header.

The issue with the "duplicate" you saw is not with the code, but with the original sheet. The sheet currently existing is named "IBM Prod Defects " <--Note the space at end
Up to this point, our WS Sync tool has been doing this work for us, so it's not an issue. But, it does make it different than "IBM Prod Defects" <--No space

Again, I can't "disable the ok till the validation passes" because as soon as user types anything, the validation passes. When checking passwords, you have small set of valid answers, out of infinite choices. In this case, we have inifinite valid answers, with small set of invalid.

To see example of error message, try adding a sheet called "Dashboard", and you'll see that the msg in the InputBox changes.

 

[Mahendra S]

Thanks for quick response Luke. Will try with input box update.
Good catch with this space issue


Regards,
M

 

[Mahendra S]

Hi Luke, Thanks for the template idea which is cool

Issue1: how ever I see an issue running with Publish DefectSet.
When you have multiple lines as 'Yes' in Publish column the proc runs fine first time.
Second time When you reduce to just one 'Yes' (example I ran with ETR Prod Defects as Yes and remaining all as No) and try to run the Publish DefectSet

This will give following err, messing up with CopyShapes.



Glad we had err handler to tell where it is getting off..
Issue2: Looks like the Template has some cell comments can we take out those invalid comments so that we will have clean worksheet when it is created.

Enhancement: Can we make sure all table names has good naming convention, as-is they are as below.
Can we have all these to start with tbl<SheetShortName>+
(for example all super admin tables to follow as tblSAPublish)



Also when ever we create a new worksheet based on template the table in the new worksheet should follow above convention
(ex tblETRProdDefects for ETR Prod Defects worksheet)

otherwise some unknown default table names (Table_owssvr_) getting loaded.

Please let me know on these.
Let me know if you need latest template which has above err, no change in code so not attaching.

Regards,
M

 

[Luke M]

Re: Issue1
Yikes, that's a bad one! Looks like the problem is because there is only 1 row in the DP table...then the SpecialCells method starts looking everywhere, not just the specified column. I changed that bit of the code to use Intersect, so it has to pick cells within the table. Seems to be working now.

Re: Issue2
Comments cleared

Re: Enhancement
Table names have all been changed. Follow a format of "tbl_SheetName_Description". The tables for source worksheets simply have "tbl_SheetName". All new worksheets created will also have their tables re-named to follow this convention. VBA has been edited to refer to all these new names. The NamedRanges list has been cleaned, deleting many of the old names. The 3 names remaining are
PubCheck - Cell containing formula to determine if any sheets are getting published
ColorCode - Cell containing color to mark changes
Extract - XL internally named range, defines output of Adv. Filter

List of all named ranges:


Possible Issue:
The CopyShapes macro for DP gets called frequently, to update shape if User changes a Status. This call is triggered by select the DP sheet. However, if DP is filtered, and then macro gets triggered, the hidden cells' shapes still appear, messing things up. Options:
a) Set macro to only generate shapes for visible cells. Negative is that if User then unfilters, some shapes will be missing (until sheet is re-selected)
b) Set macro to always unfilter when select DP. Negative is that this could be potentially annoying if User wants to stay filtered
c) Do nothing. Negative is that problem persists.
d) Something else? Open to ideas.

 

[Mahendra S]

Thanks Luke, Publish now works looks like with Issue1 (agree that was a messy one) but need more through evaluation, will do later and address if any issues.
The new table names are good and the template definitely meets good quality standards, this is really very good.

Regarding CopyShapes performance will further research on to see any best practices and thanks for the outlined options.

Have a good weekend Luke!

Regards,
M

 

[Mahendra S]

Hi Luke, Hope you had a good weekend.

A few issues noticed with latest version of Publish.
Issue1: By using template when you try to create a new worksheet, save with name BEI QAT Defects.
and fill all the columns accordingly in Publish table prior to Publish macro and run by clicking Publish Defectset.
Below err is showing which is in correct. Based on template the worksheet is available and it has one row.


I think the Publish Macro is not recognizing eligible worksheets, please let me know on this issue.

Issue2: Also for some reason If I have additional rows in Publish table by right click not able to delete, even though we had enable ribbon selected. This is creating unnecessary empty message at end of Publish macro.

Attaching the latest template for above err reference.

Regards,
M

 

[Luke M]

Re: Issue1
Actually, the error message was not saying there was a problem with worksheet not existing or being recognized, but that it couldn't filter it. I've turned on the filter for the template, which has corrected this issue. I've also edited the CopyRows macro to check if a table (ie ListObjects) exists on the worksheet, and it so, apply the AutoFilter to just the table. Issue has been resolved with this fix.

Re: Issue2
Looks like at one point either you or I added a Workbook_SheetBeforeRightClick event in the ThisWorkbook module which was cancelling all right-clicks. As we now control this via the sheet module, or the Enable/Disable ribbon code, I've removed the event, and right-clicking is working now.

 

[Mahendra S]

Hi Luke, Thanks for the update on Issues. Please let me know where exactly you have turned on the Filter is it in properties of the template or with in the code, just to know and on the same page.

Need to tweak more on template creation. Will let you know later on how we can improve the template further.

Regards,
M

 

[Luke M]

I selected the new sheet that you added, and turned on the AutoFilter (Data - AutoFilter). Then, I unhid the Template, turned on filter there as well, re-hid the worksheet.
In the code, under Macros - CopyRows, line 126

'In case AutoFilter needs sheet to be active
filterSheetFix:
If ErrPassed Then
    MsgBox "Unable to filter worksheet: " & cellRange.Value, vbOKOnly, "Filter Error"
    GoTo skipSheet
End If
ws.Select
If ws.ListObjects.Count > 0 Then
    ws.ListObjects(1).Range.AutoFilter
End If
ErrPassed = True
Resume

The ws.ListObjects.Count > 0 is new. Check if a table exists, and if so, make sure AutoFilter is on. If AutoFilter is already on, clears any filters.

 

[Mahendra S]

Hi Luke, Thanks for the details regarding auto filter.

Please let me know on following enhancements, I would like to understand feasibility from implementation point of view.
Enhancement1: As-Is after creation of new worksheet based on template the Publish table (tbl_sa_sheets) getting updated the row with WS Name, is it possible to have complete row update with default values, for example as below

Publish = No, WS Name = <Dynamic Value> (Currently we have this value coming), WS Code = NA, Env = NA, WS Source = NA.
This will give complete rowset values with Publish table.

Enhancement2: Added Canceled as new status (new icon at H15) in rows below sheets criteria.
Few updates needed in DP and MD tabs with icon columns to show accordingly.
Also need to insert a Canceled row after Returned row in all three dashboard tables and charts,
This is good to have to status sync with DP and MD.
Please let me know on this adjustment.

Enhancement3: Some times we might have Group as Engineering in few tabs. For consistency I need to convert if the Assigned Team = Engineering, it need to bring Assigned Team = ENG in DP, MD and DashBoard tabs accordingly, I believe this need addition need to happen in DataCheck Macro, please let me know if any other impacts from code perspective.

Issue1: Please let me know the reference usage of Table_owssvr_15310 from table list, are we using this table for for template.

Attaching the latest template with Canceled icon for your reference.
Thanks for all the great support.

Regards,
M

 

[Luke M]

Hi M. You're welcome about the explanation.

Re: Enchancement1
This is easily implemented. Changes made to CreateWS macro, near the end when we add ws.Name to table.

Re: Enchancement2
CopyShapes macro has been edited, "Canceled" is now included. Rows added to Dashboard. Small note, in modifying the charts to include the new row, I couldn't remember if "Total" should be included or not. I put it in for now, but you may need to remove (which is fairly easy) if you didn't want that series.

Re: Enchancement3
You were correct, DataCheck macro has been modifed. One more line to do a "find and replace"

Re: Issue1
You were correct, it was the template table. I forgot that when the sheet is VeryHidden, it doesn't show up in Name Manager, so I missed it when I was renaming things. Table re-named to "tbl_Template".

 

[Mahendra S]

Thank you Luke, this is awesome and I will further evaluate the template base runs later.
For time being let us have Total, if more data loads how these charts will get displayed need to verify if any issues, hopefully none, thinking charts might get overlapped, again need to verify then we will know.

On the Snipshot archive side, is it possible to show a message to have snipshotarchive folder, if 'snipshotarchive' folder is not created / not available in local app path location where ever the template saved.

This is good to have otherwise we can live with assumption of above dir exists always. Please let me know on this.

Best,
M

 

[Luke M]

Sure thing. Attached workbook has modifcation to ExportPic macro. Includes a line checking to see if folder exists, and if not, create it.

For other readers, code to do this looks like:

Dim myFolder as String
myFolder = ThisWorkbook.Path & "\snipshotarchive\"
If Dir(myFolder, vbDirectory) = "" Then MkDir (myFolder)

 

[Mahendra S]

Hi Luke, I would like to discuss few changes,

Issue1: We need to make sure the DP and MD tabs Column Names reflects same as what we identified in tbl_SA_Headers DP and MD has column "UniqueId" still, need to replace with WSRowId.

Ideally DP and MD tabs has total of 20 columns (5 additional columns + tbl_SA_Headers)

Above column name change will have impact in the code where ever we are calling UniqueId, one event I know is worksheet change event for DP. please review the code one more time to make sure we have all correct column names across. hopefully not many conflicts but need to reflect DP and MD columns the same names.

Issue2: As-Is what we have the column order in DP tab is good but we need to add "Change Control#" column (not sure we might have taken out in the past or ignored, please add this as column T in DP tab.)

Issue3: For consistency purpose let us have MD tab also has the same column names and order same as DP.
remaining rules everything remains same.
This re-ordering might have code impact in few places, please verify and let me know if this is not possible, we can still live, but good to have consistency, let me know your thougts.

One requirement I am thinking regarding Pie Charts in Dashboard.
Please let me know any good thoughts as I want to represent both normal and pie charts on right hand side as-is we have only normal chart.

One thought is probably we can have 2 option buttons (Normal & Pie) in cells E2 and F2.
By default we will show with Normal chart, if user chooses Pie option need to display with Piechart between cells O2 and H13

The same pattern applies to 2nd table (Group) as well but may not need two option buttons in cells E16 and F16.similarly for third combination table.

Not sure how complicated this chart representation from implementation perspective, please let me know your thoughts.
No new attachment as there is no change in code/configuration.

I appreciate all the great work done on this program so far.

Best,
M

 

[Luke M]

Re: Issue1-3
These are all somewhat related, so addressing together. In the MasterData worksheet, column order needs to match source sheets order. This is because in this data transfer, we are copying the data directly from sheet to sheet. We were able to change order when going from Master -> DP because Adv.Filter doesn't care about order, just needs to have matching column headers. If we re-arrange MasterData, it will mean increasining # of copies we needs to do (rather than a large block, would have to copy 1 column at a time), increasing processing time. Is this something you still want to pursue?
Because of the header relationship in Adv.Filter between Master -> DP, and just a copy relationship between source ->Master, there is no requirement from code perspective to have column labels in Master match source. They just have to match DP. I've manually changed the column labels to match source, for now. These sheets will not be updated in the WS-H Sync macro, due to the differences in column order. These column names are also hardcoded into macros, so caution should be used when changing. These headers could be adjusted manually by Super Admin, if needed, but macros would also need to be updated.
Change Control # has been added to DP. Currently sits in col T.
Note that because of AdvFilter's ability, we can re-arrange these columns to whatever order you like, if re-arrangement is desired.

Re: Charts
I'm not sure Pie Charts would work well in this situation, as you have multiple categories and multiple statuses. We could only effectly chart one category at a time, showing the different statuses. Also, can't guarantee that slices are arranged in descending order of magnitude, so hard to see total value (this may not be needed since data table is present). This is Pie chart of category "Open"


Another idea might be a stacked bar chart

I've purposely made this chart same size as pie, but it shows much more information. Can implement the "changing chart" idea. Could go with option buttons, or a single button (we change caption depending on what chart is visible). I've put in the bar chart for now, with an example button.

 

[Mahendra S]

Thanks you very much Luke for the prompt response.
Yes I agree with you on processing time from performance point for MD just remain as-is with latest template.
no need of further work with Issue 1-3 what we have now is better any way you have changed the column headers.
Will verify few more times from my end later.

I thought the same with Pie charts the representation will be difficult, let us leave it for now and the bar chart with button is good, need to verify further and let u know if any issues.

We can come with another dashboard report later for Pie chart, probably some kind of pivot tabling but need to do some analysis how much value we will have before implementing.

Good work!

Regards,
M

 

[Mahendra S]

Hi Luke, Hope you are doing good.

Few changes and clarifications I would like to discuss with you.
Enhancement1: Need to populate a new column "Launch*" after Priority column in DP tab only.

The rule applies for Launch* to populate would be
if Status = Open and Priority = Critical then Launch* = Yes
in all remaining cases Launch* = No

Hopefully this one is not complicated to implement since we had flexibility with column population in DP, please let me know otherwise.

Enhancement2: If I have new status = On Hold in source tabs then when it comes to MD tab it should convert Status = Postponed.
The condition we need to validate is if a row has status = On Hold then need to make sure the Priority = Medium.
If not the Priority should change to Medium before Publish to DP.
and when resync runs the source tab Priority should change to Medium.
also I believe the source tab will get updated status = Postponed after resync.

Please let me know on feasibility of implementation of this enhancement.

Enhancement3: Is it possible to create a table in a worksheet if that existing ws do not have one and have our naming convention tbl_<wsname>Defects (for ex: tbl_MSProdDefects for MS Prod Defects tab)
This enhancement can we trigger with macro run from WS-H sync button in SA tab, please let me know.

Issue1: One observation, For example If I have created a new ws based on template, the new ws is getting created. However if I o copy a row(ctrl c) from another tab with all columns from A to N then paste function(ctrl v) this should work but this is not happening.
The pre-condition of course I have enabled ribbon.
Please let me know if u see the same issue, and is there a way copy/paste works please let me know.
(Ex: I would like to copy row3 (col a to col n) from BEI QAT Defects to BEI Prod Defects)

Clarification: I was looking at source code and observed there was Macro1 in Module1, s this just for validation purpose. we may clean it up if not required otherwise we can have some comments.

Also can we rename the Macros in Modules to PublishDS or some appropriate name
attaching the latest draft for your reference.

Thank you,

Regards
M

 

[Luke M]

Re: Enchancement1
Column has been added. Similar to how we calculate Defect Age in master and then copy it, I've added a formula column in Master to calculate Launch* and then it gets copied into DP.

Re: Enchancement2
Line added into DataCHeck macro to replace all "On Hold" with "Postponed". Note that the next section in code is where we make sure any Postponed Status gets a Medium or Low priority. I'm assuming this logic is okay to use for On-Hold (i.e., if it was Low - On Hold, it's not Low - Postponed, otherwise it's Medium - Postponed)
Correct, if the record gets resynched, it will update status. Remember, we only resync the records that have been flagged as being changed. However, since we're catching the change when going from Source to Master, this won't be an issue.

Re: Enchancement3
WS-H macro now checks for table, and creates one if not existent. To be safe and make sure that even incomplete records are included in the table, I'm telling the macro to use the entire UsedRange. This sometimes causes the Source table to be bigger than absolutely needed, but will not cause errors as the table gets filterd before being copied to Master anyway.

Re: Issue1
The problem is/was the Workbook_SheetActivate event, which was controlling whether to display the formula bar or not. Changing this settings turns off CutCopyMode, preventing you from pasting.
My proposed solution is to format the cells in Master, Dashboard, and DP to have Hidden value, meaning that you can't see the cell's contents in formula bar when sheet is protected. Then, we just leave the formula bar on all the time. For now, I've put a "Exit Sub" at beginning of the Event macro so you can see if you like this solution. If you like it, the event macro could be deleted.

Re: Clarification
You were correct, that was just a test module that got created when I was practicing manipulating shapes. Module1 has been deleted, Macros module has been renamed to PublishDS

 

[Mahendra S]

Thank you Luke, need to verify these bit later today, will get back if any issues. Copy/Paste related to Issue1 is working, but need to see any other symptoms, let us see.

Regards,
M

 

[bobhc]

I hereby claim copyright in this thread and by doing so exclude all others of being allowed to publish such post or any parts of it and its contents....

All monies from said publication shall be mine, all mine....

Due to the legalities of the owners publishing rights (me) I will be unable to acknowledge those who have contributed to this thread as if I do the buggers will probably want some of my money....


.

 

[Luke M]

I hereby claim copyright in this thread and by doing so exclude all others of being allowed to publish such post or any parts of it and its contents....

All monies from said publication shall be mine, all mine....

Due to the legalities of the owners publishing rights (me) I will be unable to acknowledge those who have contributed to this thread as if I do the buggers will probably want some on my money....


.

Ha ha, nice, Bob.
This thread is certainly one of my top interesting ones. I've gotten to learn a lot myself about table references in VB, and modular VB construction. Doing a lot of work early on with proper naming conventions and good commenting makes it a lot easier here at the end when we need to tweak things.

 

[bobhc]

Luke, I would like and I mean really like to include you in my plagiarism, but if I did others would cry foul and demand monies, I do know you could do with it as you have the new rug Muncher....


I am watching this thread and I am sure many others have learnt from it as I have, thank you.


.

 

[Mahendra S]

Hi Luke, Thanks for good suggestions and clean code across the program!

I would like to discuss below items.
Issue1: In code few observations, in SyncDP and TableEdits saw the intersect with "ETA to Fix"
is this need to be "ETA To Fix" (T capital?) please let me know on case sensitivity here as we are referring column name in multiple places.

Issue2: As-Is in our design we are displaying in Dashboard tab the source and group filters.
if we select a Source in Source filter say CRM, then only CRM related groups need to be displayed in second filter Group.

The issue I am seeing is currently we are displaying all other groups as well in second filter, which are not associated with CRM
This is making kind of out of sync between filters.

Solution: Can we fix this design by allowing only related groups to be displayed in Group filter based on parent selection from Source. The exception is if we have ALL as source the Groups need to show ALL.
Any way third filter is combination of filter1 and filter2 if we go with this design modification.

Please let me know your thoughts on this design change.
Hopefully we can implement but need to know any side effects.

You may like below enhancement.



Enhancement1: Need to Generate a Dashboard Report in PDF format by clicking 'Generate DB Source Report' button from SA tab, (this report need to include all sources both table and chart side by side)
This PDF report contains all sources for example in current template we have (ALL, CRM, RS, Oracle, MS, BEI)
The PDF report need to have above 6 sources
ALL (Table + Chart)
CRM (Table + Chart)
RS (Table + Chart)
Oracle (Table + Chart)
MS (Table + Chart)
BEI (Table + Chart)

This PDF report need to be stored in snipshot archive folder.
Please let me know the feasibility of this report implementation.

attaching the latest draft for your reference.

Regards,
M

 

[Luke M]

Re: Issue1
Normally, you would be correct, VB is case sensitive by default. However, I realized this could potentially cause problems as it's fairly easy for users to change case, and XL's data validation doesn't force a particular case. So, at the top of the module, you'll notice the line:
Option Compare Text
This line tells VB that for this module, ignore case sensitivity when comparing text. The default, or if I wanted to change it back, is "Option Compare Binary" which compares items at binary level, where case would matter.

Re: Issue2
I'm not completely sure what you mean. Table 1 and Table 2 on the dashboard are completely independent, whereas Table 3 is the dependent upon both. If we make the 2nd dropdown dependent on first, then the 2nd chart would become a duplicate of the 3rd. IMO, the chart labels define what type of "cross-section" you're looking at, but you know the target audience better. Will user know that each chart is independent, or will they think that each chart is a "subset" of the one above? Please clarify how you would like to proceed, aka, what should the charts look like?

Re: Enhancement
Macro has been built, stored in Export module. Uses similar naming convention to the snipshots. Currently set to open PDF after creation. Also, I went ahead and made the macro flexible enough to take different inputs, with the guess that you might also want a Group PDF report. I've put a button on the SA sheet (bad placement, but you can move/resize where ever) just to show how it works.
Since I'm copying a chart as a picture again, I suppose there's a small chance that the XL bug about picture not getting copied to clipboard could emerge again, but I haven't seen it. There's a line commented out currently in the macro to force a wait, which would make sure this bug doesn't happen, but the downside is that since there are more than 1 copy, this would cause a longer wait (1 sec * # of charts). Play with it a bit, let me know if error pops up. If it does, we'll just have to deal with the wait.