Compile Data and Produce a Dashboard

[Mahendra S]

Hi Luke,

I appreciate the 3rd table in dashboard which is exactly implemented and your assumptions on filter are correct and no need to bring it one more time, the idea of selection in previous filters and drive the result in table3 is awesome this makes the command and center of excellence with dashboard capabilities!

Regarding the mismatch between Dashboard{All} <> (Master Data Total records & Snapshot History)

Based on my investigation and root cause analysis, I found the issue where it is.
I am not counting headers as we know this is default rule just to be clear and on same page as you!

Root cause of above mismatch: Basically in the source sheets, for example take 'Oracle Prod Defects' tab and see the row9, priority column, cell (H9) I have removed the value, now it shows as empty. This is a possible case some times in source records. Due to empty value the record count is varying and hence the mismatch between
Dashboard{All} <> (Master Data Total records & Snapshot History).

*(Please see latest attachment of template)

To resolve this critical data mismatch issue I believe we need to do data integrity check in code and arrive at solution.

Please let me know the fix with below 04/03 - design enhancements:

04/03 - design enhancement1:
In source worksheets if Priority column is empty then assign default value as "Medium", so that above data mismatch issue will get resolved.

Also few other cases we need to verify as part of above solution.
Earlier we had designed to implement check condition to verify Priority columns values (1-Critical, 2-Severe, 3-Average, 4-Low) and convert them to (Critical, High, Medium, Low).

If we have any other text like TBD or some other text in Priority cells, then all TBD/unknown texts if any, simply replace by "Medium"

In my view this will be a perfect design and sure we will not see discrepancies, please let me know what you think on this design enhancement.

04/03 - design enhancement2:
Master Data Tab the color consistency: Any source tab that has any different colors (see example "MS Prod Defects" tab Assigned Team column color. when copy rows proc executed dynamically we need to change the color and keep it same table alternate white/blue table color.

However a condition need to apply here if you have a row with Status as "Closed", we need to change that specific row (celllA to cellQ - all cols) cell color to be in grey color and text needs to be displayed in black color for the row. This will give a great user experiance to see the closed defects in different color.

04/03 - design enhancement3:
DefectsPriority Tab related rows needs to be displayed systematically and in consistent format always even though source tab columns has different colors. these different colors should not be transmitted in to DefectsPriority tab.

Only The ETA To Fix column is shown with red color/text with white/yellow combination as per our previous design enhancement. I hope this will help to bring the experience as complete user friendly.

04/03 - design enhancement4:
Snapshot history needs to locked always after record entry completion, one should not tamper the history data, I believe this is simple, probably to call same code base but need to know your thoughts on this.

I would like to discuss a snapshot extended report on third table advanced combination in next thread, I am sure you will like it.

I appreciate all your support here and thanks to Ashish and Narayan as well for their appreciation!

Regards,
M

 

[Mahendra S]

Sorry missed the attachment earlier and attaching now for your reference Luke.

Regards,
M

 

[Luke M]

Re: Enhancement 1
I see what you mean now. I've modified the DateCheck macro. Instead of doing a Find and Replace on Priority column, it steps through each cell and uses a Select Case. It's slightly (at this table size, talking milliseconds) slower method than before, but the Select Case lets us use a "Case Else" to capture anything not already defined, ensuring that everything gets categorized correctly.

Re: Enhancement 2
ApplyFormatting macro checks cell contents and applies formatting

Re: Enchancement 3
After fixing #2, this also gets eliminated, as MasterData is populating the DefectPriority.

Re: Enhancement 4
Correct, easy fix. UpdateSnapshot macro now does the Unprotect/Protect calls.

Various places:
I've learned a bit more about how to reference Tables and Table columns, so a few minor edits on how things are references. Should make the code more robust as it lets you insert/remove columns from the tables w/ more flexibility.

 

[Mahendra S]

Hi Luke, Good to see dashboard is in match with masterdata and snapshot tabs.

However following critical issues making this template not usable unfortunately. Hopefully these defects can be resolved.
After loading two additional worksheets "ETR Prod Defects" & "RS Prod Defects" still we have following issues.

Issue1: Pl see Priority Tab in the latest, most of the records are coming as "Medium" even tough they are non medium in source tabs, like ETR Prod Defects, there is something in correct in the code, need to troubleshoot and fix this issue otherwise the template loses basic functionality.

Issue2: Data mismatch issue, If you have blank records with all columns and or a record with only "ID", in this scenario the Master Data is getting populated with Medium as well, see the records 67 & 68 from ETR Prod tab and these should not get in to Master Data and DefectPriority tabs. A check needs to performed, otherwise we will have the "Status" as empty and mismatch will occur between dashboard and masterdata and snapshot.

Issue3: DefectsPriority is messed up and not reflecting true records. the fundamental rule here is records with
Priority "Critical" and "High" and status of (Open, ReadyToTest, ReadyForDeployment, Returned, PostPoned) related records always need to be shown in DefectsPriority tab , this is most important rule which should never fail. I hope this can be resolved.

Enhancement: Also iam thinking can we eliminate Transfer to Defects button click, and instead in CopyRows execution completes then exceute Filter rows to populate DefectsPriority. we can take out Transfer to Defects button, not sure what complexity we will face in case if failures we do not know exactly where the err is. Need to scale the code not to have too big proc/func though, but if it is simple please do the needful.

Please find attachment of latest template where you can see above issues.

As we load more data, I am seeing issues.
Thanks for all the great support, please let me know resolution on above so that we have good working template.

Regards,
M

 

[Luke M]

Re: Issue 1
Ugh, that's my fault. When I setup the Select Case, I forgot to take into account that some records would already be correct. I've added the correct option into the Case lines, which resolves this issue. Sorry for missing that.

Re: Issue 2
Hmm, this is a new problem...blank rows. I notice that we sometimes have omitted Ticket # and Change Board #. We need to have some "thing" that we can count on as a hard rule...is it safe to say that "If the Title cell is filled in, include this row, otherwise ignore"? I've edited CopyRows to include a line to AutoFilter the Title column and not include blanks. Ran it several times, and it seems to be functioning correctly, but may need to come back to this. Count of records across the sheets all currently say 127.

Re: Issue 3
This seems to have been connected to #1. Now that everything is not (incorrectly) getting marked as "Medium", the records are populating in Defects sheet.

Re: Enhancement
No problem at all, this is a simple 1 line. I'll added a line at the end of CopyRows to call FilterRows. I've removed the button on the Super Admin as well. It's still the same two procedures, so debugging won't be any harder than before.

Bonus Enhancement
While figuring out how to filter the source data sheets, I discovered (finally) how to hide the AutoFilter arrows on DefectsPriority sheet, so that user has to use the dropdowns at top, which I believe we investigated a few weeks ago.

 

[Mahendra S]

Hi Luke, Thanks for the update and very nice to see complete fix with priority and will load some more tabs during weekend and let you know how it goes, need to strike all mismatch issues before going with higher volume of data.

I want to bring it to your quick attention about one more mismatch issue.

Issue1: Mismatch between Dashboard(ALL) <>Defects Priority Count for Critical and High of "ALL"
If you see in dashboard by default (ALL), Not Closed "Critical" and "High" -> count comes to 44
where as Defects Priority tab we are seeing :Total number of not closed Critical and High defects for All:43

Please shed some light on this mismatch, understand it is difficult to row row by row but need to fix this otherwise with large volume it will be more painful to make data mismatch troubleshoot.

Need to think some creative way like snapshot history to include critical and high number to tie, will let u know on design improvement, but welcome your suggestions.

For now please look in to above 44 <> 43 mismatch and let me know today if possible.

Regards,
M

 

[Luke M]

Hi M,

On Super Admin, the "Postponed" category had not been added to the table which controls which records get copied to Defects page. I've added it to the table, and now have 44 on DefectsPriority sheet.

 

[Mahendra S]

Luke, I think Postponed record is populated in master data and also in dashboard but not in priority tab.
Row7 from Oracle Prod Defects and MasterData which is not in Priority is the issue here,

Please double check for all status criteria for previous Issue3: Below is the default criteria:

Priority "Critical" and "High" and status of (Open, ReadyToTest, ReadyForDeployment, Returned, PostPoned) related records always need to be shown in DefectsPriority tab

I think PostPoned is missing looks like.

Regards,
M

 

[Luke M]

Follow-up...
since it seems to be that the DefectsPriority sheet is getting everything that is not closed, we should simply change the criteria to "<>Closed", rather than try and list every option. Check out the SuperAdmin sheet table in this one to see what I mean. Both this and file above work ok, it's just a matter of how they will handle future new categories.

 

[Luke M]

Priority "Critical" and "High" and status of (Open, ReadyToTest, ReadyForDeployment, Returned, PostPoned) related records always need to be shown in DefectsPriority tab

I think PostPoned is missing looks like.

Looks like we both found it at the same time. See my L3 file for a proposed, better solution.

 

[Mahendra S]

Thanks Luke
<> Closed is good idea and I think this should resolve let us see.

Regards,
M

 

[Mahendra S]

Re: Issue 2, I think Title is mandatory and let me evaluate if I encounter any other scenario will let you know.
For now we are good.

I still need to design some advanced report and will do in next threads.
Have a wonderful weekend to you!

Regards,
M

 

[Luke M]

Thanks, and you as well.

 

[Mahendra S]

Hi Luke, I hope you had a good weekend.

I could spend some time with latest template during weekend and found some issues and I believe these are kind of critical bugs and need resolution, please see below.

Issue1: I have added a new tab 'CRM Prod Defects' and and re arranged the order in super admin which you can see in attached template, also added few records in RS Prod Defects and clicked CopyRows button (Renamed button as Publish DefectSet, I hope this will not have any impact, otherwise pl let me know)
After clicking Publish DefectSet it is throwing following err9



At any point of time we should be flexible to add/remove worksheets in super admin and based on table data dynamically the Publish DefectSet should get executed and bring all corresponding rows from source sheets and filter based on Priority (High and Critical)
If this fails we are stuck fairly at first step in recordset population with copy rows.

Issue2: I will have following four important scenarios with Sheetnames table in super admin.

Usecase1: If I have 9 tabs placed in the entireworkbook and all tab names are identified in the sheet names table, then copyrows should execute in the order from sheet table. I believe this works as-is. but please check to make sure we understand the process.

Usecase2:If I have only three worksheets for example in the sheets table(say ETR Prod Defects, CRM Prod Defects, RS Prod Defects) and we have total of 9 tabs placed in the entire workbook.
So the copy rows should execute only tabs(ETR Prod Defects, CRM Prod Defects, RS Prod Defects) which are identified in worksheets table from super admin. This scenario is failing to copy.​

Usecase3: If I have say 10 records identified in worksheet table in super admin tab and if we have only one tab identified in the workbook then the copy rows should execute only one tab and reflect data match between dashboard, MasterData, and SnapshotHistoty tabs.​

Usecase4: Zero records in worksheet table or Zero source tabs in workbook.
In this case the CopyRows should throw message accordingly if Zero Records in worksheet table throw a message and halt with Copy Rows execution.
If we have at lease a record in worksheet table and zero source tabs then need to throw a message saying that the "source worksheet is not available, please contact administrator"​

Please let me know possibility of above usecases to evaluate, this will make the template as super robust and no chance to err out with copy rows proc.

Issue3: Formatting issue, after execution of copyrows need to see last recotd in master data tab and make sure the formatting is correct.

we should not keep open rows with table colors, basically after last row insert completion, only copied rows should be "all" border lined beyond the last record it should show with no grid lines (empty white space), this scenario occurs if u have more records when you run copyrows, and in next run if you have less records, the remaining rows are showing empty but they are showing with previous grey /blue/white colors in the rows.

If we can eliminate empty rows like in above scenario, then we will have tremendous user experience and richness of the record set in excel rows.
Please let me know what you think about above issue.

Issue4: Similarly the DefectsPriority tab encounter the same formatting issue as described in Issue3 above.
We should avoid having empty rows with previous grey /blue/white/red colors in the rows

Please let me know with possible solutions. I ams ure this will give lot of value to the template.
Attaching the current template which has above described issues.

Regards,
M

 

[Luke M]

Issue 1 and 2 are related. The overall problem is, how do we handle things when the sheets listed don't match the sheets in workbook? Currently, this causes a non-descriptive error, as shown in Issue 1, sinice our list currently has "CRM QAT Defects" listed, but this sheet does not exist.

To handle names listed that aren't in workbook, I've change the macro to check if the given worksheet exists. If it doesn't exist, msg box appears, and we move on to next sheet.
Re: UseCase1
Correct, this is how things currently go
Re: UseCase2
This is how things were/are setup
Re: UseCase3
Modifed macro can now handle this. Msg Box will appear for names given that don't actually have tabs.
Re: UseCase4
After trying to filter, msg box will now appear if unable to complete, and will then move to next sheet.

As this section is new, and has the greatest chance for errors to occur, definitely recommend you test different scenarios out here. We've got it set so that the code should continue no matter what, and user get some sort of informative message box. I'm not 100% sure we've got it error-proof, as there's always new ways things can go wrong, but I think we've done a pretty good job. Please let me know what you think, or if we need new error traps.

Re: Issue3 & 4
CopyRows and FilterRows now have lines to ClearFormats, added right after the ClearContents line. The sheets look much better now, and adjust if we go from many records to less.

 

[Mahendra S]

Thanks Luke, I appreciate the messaging with missing worksheets, actually it was my mistake as it suppose to be
CRM Prod Defects and I had input as CRM QAT Defects, but this helped us sort out this subscript out of range issue.

I will evaluate usecase4 later at eod today. Hopefully it will work but If I see any issues will let u know.

Infact I have missed about the mandatory elements for a record based on previous conversation

Re: Issue 2 from lastweek
Hmm, this is a new problem...blank rows. I notice that we sometimes have omitted Ticket # and Change Board #. We need to have some "thing" that we can count on as a hard rule...is it safe to say that "If the Title cell is filled in, include this row, otherwise ignore"? I've edited CopyRows to include a line to AutoFilter the Title column and not include blanks. Ran it several times, and it seems to be functioning correctly, but may need to come back to this. Count of records across the sheets all currently say 127.

For blank rows we can have Title as mandatory to check, and then you are filling the Priority as "Medium" if this is missing. similarly we need to fill Status as Open if status is blank, otherwise we will see mismatch between Dashboard v/s Master data v/s Snapshot.
(attaching the template for your reference)

Please let me know on this.
Thanks for all your great work on the template!

Regards,
M

 

[Luke M]

No worries about the typo, it's those types of things which lead to better debugging, an "unexpected" event.

Line added into DateCheck macro, finds all blank cells in Status column and fills them in. Along a similar path, the Dashboard now has different #'s due to the same 2 blank records not having Assigned Group. Is there a default group we should default to?

 

[Mahendra S]

Thanks for quick turn around Luke, I think we should add TBD in Group table and assign if this is blank in the record.
Please let me know what you think.
attaching the changed template for your ref.

Regards,
M

 

[Mahendra S]

now with template

 

[Luke M]

Sounds good to me. Required just 1 more line to DateCheck macro.

 

[Mahendra S]

Thanks Luke and the template looks good, no change at my end.

I want to verify with you on following enhancement and advanced implementation requirement

Enhancement: For usecase3, Is it possible to show missing worksheets at the end after completion of copy rows, this will avoid multiple clicks for missing records.

Requirement: Is it possible to add a column for worksheet table in superadmin.

Basically the header row should have a checkbox (for select all /un select all - operation)
and every row should have first column with a check box dynamically attached.
The "Publish DefectSet" will get enabled only if atleast one record is selected from checkbox.
(of course we can have by default select all and enable the button)
When you enter a new row for worksheet name table first cell should have a check box attached.

This will give control to Admin to choose which worksheets to run in a particular instance.
If user wants to select only few rows then user will have control.

Please let me know the possibility to attach a checkbox and implementation feasibility.
If it is hard to implement no need, the template can sustain with out above req.

Code cleanup: We need some cleanup through out template especially with variable names and declarations.
I would avoid single letter dimensions (Dim c as range) instead can we have meaningful dims across.
Please let me know.

We need to have cross verification for Group list in super admin. I will write the req in next threads.

Regards,
M

 

[Luke M]

Re: Enhancement
Error message now appears at end of macro run, listing the missing worksheet(s)

Re: Requirement
I would vote against the use of a checkbox. It will get tricky to keep track of adding/deleting checkboxes as needed, linking them to correct cell, and getting correct value. Would a more basic option of having user input True/False or Yes/No be acceptable? I've got a basic setup in attached file using True/False. I've also built a "Select All" button, and the Copy Rows button is disabled if everything is marked false. There's a formula in cell B1 which is helping the code know whether to enable/disable button.

Re: Code cleanup
This is a little bit of personal preference. In my coding, "c" is always used for a cell range, and prefer it over the use of the variable name "cell" as some people think that cell then is an inherrent VB object, instead of just a variable. For this project, I will change them all to be "cellRange". I've gone through and removed redundant variable declarations and tried to add more comments.

 

[Mahendra S]

Thanks for the quick update Luke.

I will verify the err message in a while, but definitely good implementation.

Re: Requirement: I understand the complexity involved in tracking the checkbox dynamic value and I am with you on this as I have the risks associated with checkbox in thoughts...

I like the setup and I would go with Yes/No strategy to have friendly usable experiance
Also sheet table headers I would like to change to below to reflect correct header names, please let me know any impact
Publish WS Name Code Env
The Select ALL button is nice one but is it possible to place it in the column header itself, if not can we place in B2 as I understand you have already a formula in B2.

Thanks for the code review and clean up.

Please let me know once you have setup ready and complete, then we can verify.
I appreciate the great support!

Regards,
M

 

[Luke M]

Changed column to be Yes/No.
Header labels changed. (thankfully I didn't have much code referring to that column by name)
Select All button can be moved/sized as you liked. I moved it to Header bar for now.

 

[Mahendra S]

Luke, Thanks for the update again.

I am seeing the below err now when click Select All


Are we missing any thing here..

Also when you Click Publish Defectset it should only bring the source data(the ones identified as Yes in ws table) into MasterData and DfectsPriority tabs
This rule will be applicable for MasterData and DfectsPriority tabs.

Please confirm we are in alignment here.

Regards,
M