Compile Data and Produce a Dashboard

[Mahendra S]

Thanks for the update on Issues Luke, I understand the display issue associated with Issue1 and no need for any fix. Good to catch with Total on Issue2, will further evaluate on this.

I understand the Multi Import has significant impact and agree need to carefully tweak, but this feature will help alot and thanks for taking on this.

Regards,
M

 

[Luke M]

Hi M,

I think I've got the importting setup now. Rather than trying to build from scracth a browsing dialogue and opportunity for user to give an invalid file name in the UserForm, I decided to use the built-in dialogue to get the file name. Macro shows dialogue and only shows XLSX files. If user cancells, macro stops.

Otherwise, we open the ImportWB, and proceed to look through each WS. If the worksheet is one of our control sheets (Super Admin, DP, Dashboard, etc.) we ignore it completely. If it already exists in the current workbook, we ask for confirmation to overwrite, with default option being no (don't want someone accidentally hitting this).

After importing, close ImportWB. I then have it call the two sync macros, VerifySheetNames and VerifyHeaders.

One issue that popped up (and was resolved) during process was that the imported sheets had their own named Ranges that were getting carried over. I believe I've setup a check to make sure any imported names get deleted at the end of import though, so we should still be good.

 

[Mahendra S]

Hi Luke, Thanks for getting back on this important setup, looks like you got browse dialog which is good I was not sure earlier whether we can call it or not and built custom UI and I think we dont need custom UI looks like.

I will verify this functionality later and get back.

Thank you very much for all the great support Luke!

Regards,
M

 

[Mahendra S]

Hi Luke, It is great to see the Multi Import functionality implementation.
Need support with below issue resolutions

Issue1: From zip attachment please extract and try to open the new draft template which is throwing below excel notification about recovery, not sure about this, as soon as you click yes it giving message and and then working, but need to understand why we are getting may be this is due to data cache, this is happening first time when u try to click and open the template after that it works and noticed asking for save as when you try to save the file. please let me know on this symptom.



Issue2: I could import a test file successfully with and all the tabs are coming in template.
After importing seeing the WSRowId column width is not same across and also the borders of this column is not there some times. I believe we can adjust this column settings.

Issue3: please see the Publish table which I had modified and input the values accordingly and successfully ran publish defect set. However Advanced Reporting Options both reports are not working.
Summary Report is showing with in correct Publish names with zero values, I think we can fix this issue.

Issue4: Advanced Reporting Options - Source+Group report first table is not working for ALL values, as I believe I had mentioned this in prev thread during design, what will happen if we have many values in Groups especially with ALL, now the impact we can see in practical and the table is out of sync here. please let me know if we can resolve or we might exclude ALL. either way is ok, but hopefully we can fix this issue.

Issue5: Generate DB Source Report is throwing 1004 err due some protection issues and popping pdfReport tab.

Issue6: Generate DB Group Report is also throwing 1004 err due some protection issues and popping pdfReport tab.

Please let me know on above issues, upon fixing these we need to do through validation not break any where and probably solid code review to make sure to go with high quality standards.

Please see the zip file which has new draft which includes imported tabs and also attaching the test file which will help you to troubleshoot in case if you attempt with your yesterday template.

I appreciate all the great support Luke!

Regards,
M

 

[Luke M]

Re: Issue1 (unsolved)
Not sure on this one. Will continue to test various imports/settings.

Re: Issue2
WS-H sync now copies formatting and column widths from template sheet and applies to all source sheets.

Re: Issue3
I had not realized before that the source names need to be dynamic, depending on what sheets are included in populate run. I've created another code to populate this table, PopulateSummary, which is very similar to the PopulateSourceGroup macro.

Re: Issue4
I see what you mean. I've ammended the Export macro to not include source = ALL now.

Re: Issue5 & Issue6
Previously, I was just do a simple copy from the dashboard, which you are allowed to do on a special sheet. Then, with the Adv Reports, I had to start specifying to only copy visible cells, that that specification is NOT allowed on protected sheets. Ugh. Thankfully, just a couple of lines to protect/unprotect the dashboard during the run.

 

[Mahendra S]

Thanks for the update Luke! glad to know we could control remaining issues.
I Will do further load to verify import especially overwrite functionality and direct import combination and we can discuss if any issues further.
I will further troubleshoot on Issue1 to find root cause with cache symptoms.

Have a good weekend!

Regards,
M

 

[Luke M]

Re: Issue1
I think I tracked down the problem. The imported sheets had tables that were being generated from an external location, like SharePoint. When they got copied into our WB, that link was getting screwed up, and XL was having to try and remove it.

To resolve this, I've changed the ImportWS macro to delete any external connections. I then found there was still a lingering error, as the table no longer had an active connection, but had a defined external reference (I would have thought they were the same, but no...). So, the WS-H macro, which goes through all the tables and is called by the ImportWS macro anyway, now makes sure that every source table has no linked source. After that, I was able to run an Import, save, and re-open w/o seeing an error.
Can't guarantee that other problems aren't lurking in the shadows, but we're at least moving forward.

Have a good weekend.

 

[Mahendra S]

Thanks Luke, I will put light on further on these to nail down, let us verify name manager as well, we can synch on Monday. Mean while I will further loom into.

Thanks much!

Regards,
M

 

[Mahendra S]

Hi Luke, Hope you had a good weekend, after some more evaluation with Publish I came across a scenario which I believe we need to fix, below details on issue.

Issue1: In normal use case basic scenario the Publish from SA works perfect and Import seems working well.
After running Publish proc, select Publish = No for all rows in Publish table.
Save the template and close.
Now open the template and see the Dashboard Source filter, we don't see source filter values except ALL.
This is same case with DP tab Source filter and ENV filters.
We need to make sure even after opening the template with first instance even though the Publish = No all the filter values need to displayed based on last Publish Run. (This should be common standard for all filters in DB, DP tabs)
Please let me know on this issue resolution.

Issue2: Some times we could see different tab during the loading of the template when you open, though the first screen by default is shown Dashboard when the template is loaded.
For ex if you were on some other tab in last session other than Dashboard and close the template.
During next time when u open the template it still keeps last viewed tab during last session still shown for split of time.
Can we control this by eliminate/clear the cache(last viewed tab to show for split of time) during the load (do not show previous cache), please let me know.

Requirement1: As soon as we run the Publish need to display the last Publish date/time stamp in DB cell O1
Probably we might just merge all cells AI to N1 first row in DP and merge O1 and P1 separately
The Snapshot History Complete date and Snapshot complete time together need to be displayed on merged cellO1 in DP as header row.

Requirement2:
All the reports we need to make sure to display the header as the image of Cell value image from A1 to N1 first merged cell and O1+P1(merged cells) this need to be in same width as table1 width. height would be ~length of 2 rows of table.
This header needs to be repeated header if the report spans to more than a page.
I hope this is possible, please let me know on this design, this has some degree of complexity involved with shrinking of image per my understanding, but hopefully possible to implement.

Thanks for all the support.
Attaching the latest template for you reference.

Regards,
M

 

[Luke M]

Re: Issue1
I've added an additional column on SA to the Sheets table, in col B. Publish macro uses it to flag which sheets actually published, and then the populate CB macro uses this column, rather than the regular Publish column. Col B has been hidden as it should not be something changed by admin.

Re: Issue2
I've modified the Workbook_Open event so that the Dashboard is selected as the first step, and then screen updating turned off until other macros finish (disable ribbon, populate CB). The other alternative is to select the Dashboard right before closing workbook, but the same "flash" would be visible to user. Hopefully this fix is "good enough".

AVOID MERGED CELLS! Merged cells are the bane of VB coding, causing massive headaches because they screw up cell references and can't be accessed directly. There are many good visual alternatives to merging cells, such as using horizontal alignment = center across selection. It would be my strong recommendation that you learn to avoid merged cells at all costs, especially in workbooks w/ macros. I've noticed that we've added a few merged cells in sheets already, but as the Dashboard sheet gets used a lot in the reporting macros, we need to stay away from them.

Re: Requirement1
I've placed a formula in OP1 that looks up latest time from snapshot.

Re: Requirement2
I'm not sure what you mean. By reports, I assume you mean the PDF reports? Are you wanting to use some cells as the header, or an image? You mentioned cells A:N, but the table doesn't go that wide...are we making a picture of a1:n1 and then shrinking it? I believe I undestood the sizing requirements. Once I know what we are copying, I'll modify the PDF export macro to first copy something to row 1, adjust height of row 1 accordingly. the AdvReport sheet can be pre-configured to repeat row 1 across all sheets.

 

[Mahendra S]

Hi Luke, Thanks for the update and I'm inline with avoiding merging cells as I attempted it is messing many other things and your input really helped to understand.

Regarding Req2, yes in pdf reports need to bring the header, however the original thought was to merge the cells
and I am certainly not going in merging cells route due to avoid further complication as you had suggested.

Instead can we just put the rectangle (similar one as we one in SA tab at row2) with text as Dashboard view (this is static), this rectangle can span on row1 of Dashboard tab, please advise.

Regards,
M

 

[Luke M]

How's this look for the header row?

 

[Mahendra S]

Thanks Luke, wondering if the title is changed in DB will it reflect in pdf report, please let me know.
also the timestamp if we can restrict to two lines would be good (date and time in same line in pdf)

 

[Luke M]

Yes, the cells from DB get copied to report, so whatever you make the DB say will be what is on PDF report.
Yes, I can fit it one 2 lines. To not mess up the column width of table cells, I'll have it span the last two columns (centered across).

 

[Mahendra S]

Thanks for the update Luke, I will get back on some of the color formatting and header changes later.

Regards,
M

 

[Mahendra S]

Hi Luke, Thanks for all the updates earlier. A few changes related with headers and reporting options as below, please take a look and let me know the feasibility.

Requirement1: As we had ColorCode table and can we use this table color to reflect with color of rounded rectangle in SA in row2, if we change the color in R8 cell (current color code) the updated color should reflect in rounded rectangle21.
Idea behind this requirement is we will have good control on color palette.
Hopefully this is possible to make it if not we can stay as-is.

Requirement2: I have placed similar rectangle in DB, MD tabs and these rectangles color can controlled by same color code table from SA tab. However I would like to see the similar rectangle in DP tab at row1, unfortunately it is not letting me to place a rectangle in DP tab, looks like entire range we had a another table but not sure, also we can un merge cells in row1 so that we don't have to merge cells if that makes things easier.
DP row1 rectangle we need to have title as Critical & High Priority Defects View
please let me know on this need.

Requirement3: The Adv Report and pdfReport we had for some reason I could not change the color of the header. Can we have the header color controlled by ColorCode table from SA tab.
please let me know on this need.

Enhancement1: I have added additional three option buttons in Advance Reporting PDF Reports please see. Basically these additional reporting options are nothing but we had buttons in SA tab. The same macro calls need to happen with these new three option buttons. Once we had implementation on this we can take out the 'Generate DB Source Report', 'Generate DB Group Report' & 'Generate WS Source Report' buttons.
Hopefully this makes simpler.

The ok button by default should be greyed out, as-is if u dont select any option click on ok button it is not generating which is ok but we should grey out and if user selects any option button then ok button should be enabled.

Enhancement2: all the advance pdf reports should be saved under snipshotarchive\pdfreports\
the image report we had with Snipshot DP should be saved under snipshotarchive\imgreports\
This dir structure will help us to have clear path on different kinds of reports save locations.

Issue1: In MD tab, we need to make sure Launch* column show as Yes if Priority = Critical and Status is either Open or Closed.

Issue2: WS Source column width(in DP & MD tabs) I would like to see based on value of the cell, can we control the width of this column dynamically if not possible we can increase width to 20. please let me know on this.
The reason behind this issue is the text is not showing completely if we have more characters.

attaching the enhanced design template for your reference.
Once we implement above requirements and enhancements we need to verify all the reports and publish and other functionality to make sure it is not breaking any where else.

I appreciate all the great standby support!

Regards

 

[Luke M]

Re: Requirement1
I went ahead and created 2 color cells, one for header bars and one for changes. Having them be the same, a light blue, made it very hard to see which cells get changed in DP and source WS. Coloring shapes is a little trickier, I can't trigger an event through coloring a cell. I setup a sheet_deactivate change event, which checks if the cell got changed to a different color. If it did, it goes through and changes header bar. Not quite real-time, but it gets the job done, and presumably you won't be changing the color very often.

Re: Requirement2
The shape you were adding was getting deleted as part of the CopyShapes macro to add the icons (for status). I've added an extra check in CopyShapes to not delete a shape called "HeaderBar". Merged cells have been unmerged.

Re: Requirement3
Because the pdfReport gets cleared and recreated each time, I add the rectangle programatically (and the color). Code now looks at the HeaderColor cell.

Re: Enhancement1
Done. First report is selected by default now, removing need to Ok to be grayed out.

Re: Enhancement2
Done

Re: Issue1
Formula changed, done.

Re: Issue2
Done, column set to be AutoFit after Publish macro.

 

[Mahendra S]

Thank you very much Luke, hopefully no issues, will get back if any.
Good to know about explanation on Requirement2 otherwise no clue on this.

Regards

 

[Mahendra S]

Hi Luke, Overall the header change look and feel is better now.

Few minor issues as below:

Issue1: Earlier we placed "Updated as of date & time stamp" formula in row1 of o1&p1 in DB, now due to header bar this date and time stamp is not visible. we need to bring this on to header bar at same place so that the date and time stamp is visible.

Also please change the text to "Data Refresh & Publish as of date & time stamp" I hope this is possible, please let me know.

additionally we need to display "Data Refresh & Publish as of date & time stamp" in headers of DP and MD tabs at end of header bar on right hand side.
The "Data Refresh & Publish as of date & time stamp" also need to be displayed in pdf report template tab.

Enhancement1: Can we use Header color dynamically for SA, DB, DP, MD & SH tab colors, please let me know
as-is u r showing this as green and SH(snapshothistory) as blue. If we control these admin tabs with header color which will be nice other wise if not possible that is fine we can live as-is. Please let me know.

Previous enhancement, I believe pdfreports folder creation is not done, probably missed
Enhancement2: all the advance pdf reports should be saved under snipshotarchive\pdfreports\
please let me know any restriction on this hopefully no.

Luke, some times I am seeing an error, I think very first time the pdf report generation is not happening and it is throwing err and bringing pdfreport tab visible, but if u dont save and close and re open the workbook and generate a pdf report this is not happening. please do a code review if any issue, If I see this err next time I will capture as this is intermittently happening.

Enhancement3: When we do Multi WS Import after bringing all tabs at end can we execute WS-H sync procedure at the end of Import, as -is we are doing manually to create the WS Row Id & table names after Import, so that we dont need to run WS-H seperately when we Import multi tabs.
This will automate and reduce one step before running Publish.

We still need to keep these buttons for other exclusive purposes.
Please let me know on this.

Requirement1: Is it possible to generate a line chart for option1 (Report by source summary) basically need to ask user upon clicking option1 and ok, need to ask user choose between line and bar cahrts.
(line chart reference from Insert in upper menu)

if user selects line chart we need display the line chart in Report by source summary
if user selects bar chart we need display the bar chart in Report by source summary.

Please let me know if this is not possible or any other better design to show both line and bar charts seperately.

Regards

 

[Luke M]

Re: Issue1
Used a couple of shapes grouped together to create header bar. TimeStamp is calculated on SuperAdmin sheet, the shapes link back to it. PDF-report has the shape copied from Dashboard, and resized to match table width.
Timestamp label changed.
Timestamp added to DP and MD

Re: Enhancement1
Tab coloring has been added

Re: Enhancement2
I do remember this request, not sure what happened. Nevertheless, I've put it back in.
Let me know what you find out about the error. It may be the issue with pictures not getting copied to clipboard, in which case I can put in the forced wait again. Good side of the wait is we make sure the bug doesn't happen, bad news is that it's a longer wait to process reports (forced wait of 1 sec per chart). Let me know what you think, or how prevalent the bug is.

Re: Enchancement3
The WS-H macro is already called at the end of Multi Import. Is this not happening?

Re: Requirement1
I'm not sure how a line chart displays the data...but I'll give it a shot. Additional chart created on AdvReport tab if you want to modify the layout. User is asked which one they one.

 

[Mahendra S]

Thank you Luke for prompt responses! I appreciate the great work.

The headers time stamps are perfect!
pdf reports folder is getting created now, need to see if snipshotarchive folder is not available in case first time will it create parent and child folders are not. will evaluate this to make sure no err occurs, hopefully no err.

regarding enhancement2, the wait mode is perfect solution per my understanding will verify multiple scenario's to see how this goes and get back if any other symptoms.

I was not sure about enhancement3, good to know ws-h is getting called will double validate this. saw an issue in one previous runs where header column was not in sync but will check again on this.

The design regarding line chart is good, need to see what will happen if you have more than 10 sources on x axis and how it will get displayed, will play around and let you know, but I appreciate the work done on this report, very nice work!

Thank you,
Have a good weekend!

 

[bobhc]

When this thread reaches 300 the poster will supply unlimited donuts to Luke M

 

[Mahendra S]

Hi Luke, I hope this thread will reach triple hundred with great quality of all good features!

I came across below issue related with app path and directory location.
Issue1: As long as we have snipshot archive folder created or existing in the direcory where this template is stored the reports are getting saved perfect. How ever, if you copy this template to another different folder where you dont have snipshot archive and underneath sub folders (pdfreports and imgreports) you will get following err 76, and pdfreort tab opens up.



Our need is any time if we dont have snipshot archive and subfolders the template should create intelligently, please let me know on this, if this is a limitation we can live as -is.

I have a new requirement which you will like hopefully.
Requirement1: I have created a button Multi WS eXport in SA tab.

Basically when you click it should create a seperate excel file with multiple tabs and save it snipshotarchive/excelreports which is a new sub folder.
this folder path and sub path should be created dynamically if not exists where this the main template resides.

(The file name would be dynamic with date and timestamp as you have for pdf and img reports, the name would be prefix with DefectTracker-<datetimestamp>.xlx)

The multiple tabs criteria for this new file export would be as below:
The new export xlx file should have DefectsPriority tab and all the remaining WS source tab names where Publish = Yes (WS Name from Publish table in SA) which are

For ex: if we have Publish = Yes for (ORP Prod Defects, ERP PIT Defects) then new export xlx file should have three tabs (DefectsPriority, ORP Prod Defects, ERP PIT Defects)

If we have complexity involved with exporting DP tab(which has shapes and criteria calling from SA), then the simplest way is put the image of the DP in new tab and attach other tabs as per Publish=Yes criteria from SA tab.

I believe above design is implementable, but see some complexity though..
Please let me know your thoughts on this new req and design feasibility and open for any suggestions too.

attaching the template for your ref.

Regards.

 

[Mahendra S]

a small correction the export file extension would be xlsx which I meant earlier.

 

[Luke M]

Re: Issue1
I learned something, the Make Directory command can only do one folder at a time. Added an extra line to check if the snipshotarchive (main folder) exists, if not, add it, and then do same check for subfolder.

Re: Requirement1
Yes, I like the idea. A few things I thought of, hopefully you agree.
First, very glad we are saving as xlsx. That removes all the VB, which is good since the new DP sheet would not function correctly on it's own. Thinking of that, we will need to remove the dropdowns, since they will no longer function. With no dropdowns, need to make sure that the DP is unfiltered in new WB.
Next, when checking for which sheets get copies, similar to earlier issue, macro checks which sheet actually got copied, in case user changes settings on SA before hitting Export button. This ensures that exported DP matches the exported source sheets.
Question: Should the new DP be protected? New workbook will have ribbon visible, and user might want to turn on AutoFilter/resize rows themselves, so I'm assuming no.

New macro, under Export module, has been created. First checks and creates if necessary the file folder, then creates new workbook with new DP, then moves over all requires source sheets. Saves and closes file.