Compile Data and Produce a Dashboard
- Thread starter Mahendra S
- Start date
Mahendra S
Member
Luke, Congratulations on triple hundred mark cross over!
Thanks for the update this is awesome and want to verify how many list items will fit in by default, is there any limitation, what if we have 50 worksheets, will it automatically populate with vertical scroll bars please let me know.
Thanks for the update this is awesome and want to verify how many list items will fit in by default, is there any limitation, what if we have 50 worksheets, will it automatically populate with vertical scroll bars please let me know.
You're welcome. Correct, the list box will add a vertical scroll bar if needed. Just to illustrate, I shortened the box and the ran the export macro
Obviously, with the scroll bar being as large as it, I think the scroll bar is not too big an inconvenience, and really the only practical way to handle that many sheet names. Have a wonderful weekend if I don't hear from you.
PS. I am going on holiday/vacation next week, so will not be posting anything then.
Obviously, with the scroll bar being as large as it, I think the scroll bar is not too big an inconvenience, and really the only practical way to handle that many sheet names. Have a wonderful weekend if I don't hear from you.
PS. I am going on holiday/vacation next week, so will not be posting anything then.
Mahendra S
Member
Thanks Luke, I will go over each feature set validation and code review next week and I will list out if any issues and we can resolve them following week when you are back from vacation.
Have a good weekend and enjoy your vacation time!
Regards,
M
Have a good weekend and enjoy your vacation time!
Regards,
M
Mahendra S
Member
Luke, thanks for checking and I hope you had very good long weekend
did very little in last week due to long weekend for me as well, will write later further on any enhancements / issues.
Regards,
M
did very little in last week due to long weekend for me as well, will write later further on any enhancements / issues.
Regards,
M
Mahendra S
Member
Hi Luke, I hope you are doing good, here are some enhancements I thought we can implement but need your valuable input and support!
Enhancement1: When you Export from SA, need to remove 'WSRowID' from all the tabs that are exported. Is this going to be an issue with DP Tab please let me know.
Enhancement2: When we Import, as-is we are directly going with dialog Open window, instead can we populate all existing tabs and by default select all tabs (highlighted) and interface should have Delete button and cancel buttons.
Delete = Ok should proceed to delete all the tabs except SA, DP, DB, MD and SH tabs. Then Import all the tabs, this way we will have clean structure always.
Delete = Cancel should just open the Open Dialog as-is what we have Import all the tabs and ask for overwrite if the same tabs still exists.
Please let me know on this enhancement hopefully possible to make it.
Enhancement3: Can we have pdf report(Report by Not Closed Defects) this is kind of Consolidated Report to see details like below
First we need to display
these two rows are example as we have in DB tab to be shown in pdf report as well.
then can we display all defects at high level
Critical:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
High:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
Medium:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
Low:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
Please let me know on this enhancement, I believe we can use MD tab to populate this report, but you might have other good idea's which are always welcome!
Enhancement4: I have Search button in SA, when admin clicks it should ask for popup window and have a text field, user need to enter Ticket/Defect #, click ok should retrieve if this ticket is available primarily in MD tab since we consider this as Master.
Note: How ever later we can implement to ask if Search is applicable for other tabs if we have Publish = No, such tabs to verify or not that would be Advanced Search just FYI, again your thoughts are welcome.
Attaching the template for your reference, SA tab has Search button now!
Regards,
M
Enhancement1: When you Export from SA, need to remove 'WSRowID' from all the tabs that are exported. Is this going to be an issue with DP Tab please let me know.
Enhancement2: When we Import, as-is we are directly going with dialog Open window, instead can we populate all existing tabs and by default select all tabs (highlighted) and interface should have Delete button and cancel buttons.
Delete = Ok should proceed to delete all the tabs except SA, DP, DB, MD and SH tabs. Then Import all the tabs, this way we will have clean structure always.
Delete = Cancel should just open the Open Dialog as-is what we have Import all the tabs and ask for overwrite if the same tabs still exists.
Please let me know on this enhancement hopefully possible to make it.
Enhancement3: Can we have pdf report(Report by Not Closed Defects) this is kind of Consolidated Report to see details like below
First we need to display
these two rows are example as we have in DB tab to be shown in pdf report as well.
then can we display all defects at high level
Critical:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
High:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
Medium:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
Low:
Defect Id, tab Name, Title, Description, Team, Assigned To, ETA in column format.
Please let me know on this enhancement, I believe we can use MD tab to populate this report, but you might have other good idea's which are always welcome!
Enhancement4: I have Search button in SA, when admin clicks it should ask for popup window and have a text field, user need to enter Ticket/Defect #, click ok should retrieve if this ticket is available primarily in MD tab since we consider this as Master.
Note: How ever later we can implement to ask if Search is applicable for other tabs if we have Publish = No, such tabs to verify or not that would be Advanced Search just FYI, again your thoughts are welcome.
Attaching the template for your reference, SA tab has Search button now!
Regards,
M
Good to hear from you again. Let's see what we can solve today. 
Re: Enchancement1
One line added to the ExportMultiSheet macro. As the DP within the exported file is static (no changing data), then no problem with removing col O.
Re: Enchancement2 (open)
I'm unsure/unclear on this one. This sounds like a big shift from current setup, so I want to verify. Process is:
Re: Enchancement3
I build a new worksheet, which contains just some simple COUNTIFS for the table, and then a PivotTable to generate all the data in your report. Advantage is that it's easy to build, can easily flex/shrink in size, and we can easily change filtering options in future. Report has been added to list on the "Adv Reporting Options" form.
Re: Enhancement4
Search macro has been built. Built in an option to search unpublished sheets. Macro is stored at end of SyncChecks module.
Re: Enchancement1
One line added to the ExportMultiSheet macro. As the DP within the exported file is static (no changing data), then no problem with removing col O.
Re: Enchancement2 (open)
I'm unsure/unclear on this one. This sounds like a big shift from current setup, so I want to verify. Process is:
- Check w/ user if we want to delete all existing data sheets
- If yes, clear existing, import all new sheets
- If no, check only which sheets we want to import
Re: Enchancement3
I build a new worksheet, which contains just some simple COUNTIFS for the table, and then a PivotTable to generate all the data in your report. Advantage is that it's easy to build, can easily flex/shrink in size, and we can easily change filtering options in future. Report has been added to list on the "Adv Reporting Options" form.
Re: Enhancement4
Search macro has been built. Built in an option to search unpublished sheets. Macro is stored at end of SyncChecks module.
Mahendra S
Member
Thanks for the update Luke, please see below.
Re: Enchancement2 (open)
I'm unsure/unclear on this one. This sounds like a big shift from current setup, so I want to verify. Process is:
We can build and tweak the logic after validation, please let me know.
Re: Enchancement2 (open)
I'm unsure/unclear on this one. This sounds like a big shift from current setup, so I want to verify. Process is:
- Check w/ user if we want to delete all existing data sheets - Yes Correct.
- If yes, clear existing, import all new sheets - clear existing and, need to ask which sheets to import
- If no, check only which sheets we want to import - Yes Correct.
We can build and tweak the logic after validation, please let me know.
Mahendra S
Member
Yes Luke, I know bit complicated to understand this process, I should have highlighted my answer with different font color earlier. please see below, I can prepare mock up if that helps...
- Check w/ user if we want to delete all existing data sheets - Yes Correct.
- If yes, clear existing, import all new sheets - clear existing and, need to ask which sheets to import
- If no, check only which sheets we want to import - Yes Correct.
Ah, I see the comments now. Sorry for missing them earlier.
Re: Enchancement2
Import macro now queries user before importing.
Various:
Re: Enchancement2
Import macro now queries user before importing.
Various:
- Importing macro now makes sure that imported sheets are unprotected. Needed so that we can later filter/change headers if needed.
- Worksheet Sync macro now clears out old entries from SA Sheets table. Before, old entries would be left behind. With Enhancement2, need to clear out unused sheet names seemed to be stronger.
- Export macro now breaks any XL links. I noticed that the time stamp in header bar was trying to link back to SA in original...don't want this to happen.
Mahendra S
Member
Thanks for the update Luke and above three points are well handled.
Before, it was throwing err's with Publish in y'day version but I will verify today later and let you know if issue still exists.
It is a good idea to clear the SA Sheets table, now this leads little bit a tweak, will write an enhancement on this which will make it more simpler.
Thanks again!
Before, it was throwing err's with Publish in y'day version but I will verify today later and let you know if issue still exists.
It is a good idea to clear the SA Sheets table, now this leads little bit a tweak, will write an enhancement on this which will make it more simpler.
Thanks again!
Mahendra S
Member
Hi Luke, I came across following scenario where our Import throws out err and interestingly Publish throws err's.
Issue1: Step1:Click Import and select the 'new-ws-import-test-07-25' (this is same sample test file as you have before, just including in case if u don't have),
step2: Delete All - Yes
step3: Import - Click Cancel, you will see error 424
step4: Click Publish, You will get err424(source: VerifySheetNames), and click ok will give and 91 (source: copy rows).
Ideally Publish should not be even enabled as we dont have any rows left in SA sheets table.
However to resolve this critical issue, in above scenario, at step3 we need to disable Cancel (only in this scenario), till either user selects all check box or chooses one or multiple list items. either way if not selects or not chooses then disable the cancel button so that we can avvoid above err 424 and err91.
Need your thoughts on above issue.
Enhancement1: In SA tab, Group table can we have tbl_SA_Groups by alphabetical order, hopefully we can make this otherwise also file please let me know.
Enhancement2: The consolidated pivot report we have with status need to be renamed to 'Report by Open (C-H-M-L) Status' in Advanced PDF Reporting Options window.
(FYI C-H-M-L stands Critical-High-Medium-Low), similarly the pdf file generated should be 'OpenDefectStatus Report-datetimestamp' we also need also need Defect/Ticket Number after ID. for each row and We don't need description column for each row.
Also alignment we need center for all columns except Title which is need to be left align, hopefully this can be adjusted in pivot template, but not sure, please confirm.
Enhancement3:Can we have another consolidated pivot report 'Report by Open (C-H-M-L) Assigned Team' in Advanced PDF Reporting Options window, this report is almost similar to above except we need to have 'Assigned Team' on left hand side like we have Critical, High, Medium and Low categories and remaining columns structure same. also need Defect/Ticket Number after ID in the report
Also alignment we need center for all columns except Title which is need to be left align, hopefully this can be adjusted in pivot template, but not sure, please confirm.
We don't need description column.
Enhancement4:Also wondering if we can make Outside border for each category like all Critical show in thick line border and all High show in outside thick border and same for medium and low.
Similarly this thick border line adjustment applicable for enhancement3 categories with Assigned Team categories.
Enhancement5: At each pdf report footer on right hand side can we display Page X of Y format (Page 1 of 5)
please let me know on this otherwise also fine, good to have.
Enhancement6:
After Import, as-is now manually we are editing the sa_sheets tables with wscode, env and ws source columns which is fine.
To make it more easier and to avoid errors with input values, can we save existing sa table values in a temp worksheet or some where and when you call it with Save / Replace WS Table button, we should have pop up with two options (Optionbutton1 - Save & Optionbutton2 - Replace) to ask choose,
If user selects Save option, simply save the structure in ws_sasheetref (a new worksheet, can be invisible).
If user selects to Replace option, it should bring all corresponding structures probaby with option buttons like we have in Advanced Reporting Popup form. (For example we have two different structures, first structure with 10 rows and second structure with 6 rows etc) based on structure selection option, the saved sa table values structure should just replace existing sa_sheets table (an example below for reference).
This will make it easier when ever you want same table to be replaced to avoid input values for WS Code, ENV, WS Source.
The condition still correct, Publish will only works if the WS Name matches with tab name and all other columns with filled already in this case. This will eliminate to manually enter the values in sa_sheets majority of the time if the sheets are same.
Also user will have choice after import if still want to manually enter/edit instead of going with Save / Replace WS Table
Structure1 Example:
Structure2 Example:
Similarly we should be able to save another table structure with for example only few rows in above table.
As soon as we click on Replace WS Table button we should have option buttons with each of above table value references.
As soon as user chooses one the sa table values needs to be replaced.
Please let me know if this is not clear, I can write more detail with mockup if required.
Attaching the new draft which has Replace WS Table button.
Thanks for all the graet support!
Regards,
M
Issue1: Step1:Click Import and select the 'new-ws-import-test-07-25' (this is same sample test file as you have before, just including in case if u don't have),
step2: Delete All - Yes
step3: Import - Click Cancel, you will see error 424
step4: Click Publish, You will get err424(source: VerifySheetNames), and click ok will give and 91 (source: copy rows).
Ideally Publish should not be even enabled as we dont have any rows left in SA sheets table.
However to resolve this critical issue, in above scenario, at step3 we need to disable Cancel (only in this scenario), till either user selects all check box or chooses one or multiple list items. either way if not selects or not chooses then disable the cancel button so that we can avvoid above err 424 and err91.
Need your thoughts on above issue.
Enhancement1: In SA tab, Group table can we have tbl_SA_Groups by alphabetical order, hopefully we can make this otherwise also file please let me know.
Enhancement2: The consolidated pivot report we have with status need to be renamed to 'Report by Open (C-H-M-L) Status' in Advanced PDF Reporting Options window.
(FYI C-H-M-L stands Critical-High-Medium-Low), similarly the pdf file generated should be 'OpenDefectStatus Report-datetimestamp' we also need also need Defect/Ticket Number after ID. for each row and We don't need description column for each row.
Also alignment we need center for all columns except Title which is need to be left align, hopefully this can be adjusted in pivot template, but not sure, please confirm.
Enhancement3:Can we have another consolidated pivot report 'Report by Open (C-H-M-L) Assigned Team' in Advanced PDF Reporting Options window, this report is almost similar to above except we need to have 'Assigned Team' on left hand side like we have Critical, High, Medium and Low categories and remaining columns structure same. also need Defect/Ticket Number after ID in the report
Also alignment we need center for all columns except Title which is need to be left align, hopefully this can be adjusted in pivot template, but not sure, please confirm.
We don't need description column.
Enhancement4:Also wondering if we can make Outside border for each category like all Critical show in thick line border and all High show in outside thick border and same for medium and low.
Similarly this thick border line adjustment applicable for enhancement3 categories with Assigned Team categories.
Enhancement5: At each pdf report footer on right hand side can we display Page X of Y format (Page 1 of 5)
please let me know on this otherwise also fine, good to have.
Enhancement6:
After Import, as-is now manually we are editing the sa_sheets tables with wscode, env and ws source columns which is fine.
To make it more easier and to avoid errors with input values, can we save existing sa table values in a temp worksheet or some where and when you call it with Save / Replace WS Table button, we should have pop up with two options (Optionbutton1 - Save & Optionbutton2 - Replace) to ask choose,
If user selects Save option, simply save the structure in ws_sasheetref (a new worksheet, can be invisible).
If user selects to Replace option, it should bring all corresponding structures probaby with option buttons like we have in Advanced Reporting Popup form. (For example we have two different structures, first structure with 10 rows and second structure with 6 rows etc) based on structure selection option, the saved sa table values structure should just replace existing sa_sheets table (an example below for reference).
This will make it easier when ever you want same table to be replaced to avoid input values for WS Code, ENV, WS Source.
The condition still correct, Publish will only works if the WS Name matches with tab name and all other columns with filled already in this case. This will eliminate to manually enter the values in sa_sheets majority of the time if the sheets are same.
Also user will have choice after import if still want to manually enter/edit instead of going with Save / Replace WS Table
Structure1 Example:
Structure2 Example:
Similarly we should be able to save another table structure with for example only few rows in above table.
As soon as we click on Replace WS Table button we should have option buttons with each of above table value references.
As soon as user chooses one the sa table values needs to be replaced.
Please let me know if this is not clear, I can write more detail with mockup if required.
Attaching the new draft which has Replace WS Table button.
Thanks for all the graet support!
Regards,
M
Mahendra S
Member
Luke, if above enhancements takes more time no worries, we can still make one by one, the last one might be complex, it might need some form building, appreciate your time!
Re: Enhancement1
Having no source sheet in the workbook causes numerous problems, as you have seen. I propose that instead of disabling the cancel, let the user cancel on the import, and then just have a blank sheet be created (from the template). This is equivalent to starting with a blank tool. That way, the tables on SA still have at least 1 row, and shouldn't crash. User can either select a new file to import, or start with blank sheet. Let me know what you think.
Re: Enhancement2
Report name, titles and column order have been changed.
Re: Enhancement3
I've got the 2nd report built. May need to adjust the layout depending on your preference.
Re: Enhancement4
General PivotTable formatting macro written to add border, alignments.
Re: Enhancement5
Page numbers have been added to footer of reports
Re: Enhancement6
Still working on this
Saving individual structures could get quite complicated, as there are infinite possibilities. I would suggest keeping a log/record book of sorts for every sheet name, keeping track of WS Code, Env, and WS Source, and user can choose to import last record for that sheet name. The sheet names I would prefer to have controlled through the macro currently, as that ensures that they match with what is existing. Let me know what you think.
Having no source sheet in the workbook causes numerous problems, as you have seen. I propose that instead of disabling the cancel, let the user cancel on the import, and then just have a blank sheet be created (from the template). This is equivalent to starting with a blank tool. That way, the tables on SA still have at least 1 row, and shouldn't crash. User can either select a new file to import, or start with blank sheet. Let me know what you think.
Re: Enhancement2
Report name, titles and column order have been changed.
Re: Enhancement3
I've got the 2nd report built. May need to adjust the layout depending on your preference.
Re: Enhancement4
General PivotTable formatting macro written to add border, alignments.
Re: Enhancement5
Page numbers have been added to footer of reports
Re: Enhancement6
Still working on this
Saving individual structures could get quite complicated, as there are infinite possibilities. I would suggest keeping a log/record book of sorts for every sheet name, keeping track of WS Code, Env, and WS Source, and user can choose to import last record for that sheet name. The sheet names I would prefer to have controlled through the macro currently, as that ensures that they match with what is existing. Let me know what you think.
Mahendra S
Member
Thanks for the update Luke.
Re ENH1, I am in line with you and did not verify all scenarios but a quick validation, with new version I can see new row with blank in WS Name which is good, how ever if you cancel on Import still getting below err
Re ENH6: The setup with macro is ok, but atleast I need to save four or five standard structure formats these are one time defined and will not change often which is the reason we need to have these five defined structures to be choosen if user would like to replace the sa_sheets table.
Regards,
M
Re ENH1, I am in line with you and did not verify all scenarios but a quick validation, with new version I can see new row with blank in WS Name which is good, how ever if you cancel on Import still getting below err
Re ENH6: The setup with macro is ok, but atleast I need to save four or five standard structure formats these are one time defined and will not change often which is the reason we need to have these five defined structures to be choosen if user would like to replace the sa_sheets table.
Regards,
M
Mahendra S
Member
Thanks Luke, let us re group, I will send more details on ENH6.
Have a good weekend!
Regards,
M
Have a good weekend!
Regards,
M
Mahendra S
Member
Hi Luke, hope you had good weekend! just FYI, I haven't changed anything for ENH6.
Regards,
M
Regards,
M
Hi Mahendra,
I think I got something working pretty well now. Created a new, regularly hidden workhseet called WS Table Records. This contains a table with all the different record lines. Each saved table is given a unique Record number (hidden in col G).
Upon pressing button, you can first choose to save current table layout to records. Then, given ability to choose record number and preview in real-time what the chosen table looks like. Can choose to accept new table, pick a new table, or cancel. Cancel reverts back to original table layout when button was pushed, regardless if it was saved or not.
The 3 new macros are stored within the TableEdits module. Feel free to unhide the WS Table Records tab and setup your layouts, or input them in the SA and save the tables. There's a few junk records in there currently, feel free to change/delete them.
I think I got something working pretty well now. Created a new, regularly hidden workhseet called WS Table Records. This contains a table with all the different record lines. Each saved table is given a unique Record number (hidden in col G).
Upon pressing button, you can first choose to save current table layout to records. Then, given ability to choose record number and preview in real-time what the chosen table looks like. Can choose to accept new table, pick a new table, or cancel. Cancel reverts back to original table layout when button was pushed, regardless if it was saved or not.
The 3 new macros are stored within the TableEdits module. Feel free to unhide the WS Table Records tab and setup your layouts, or input them in the SA and save the tables. There's a few junk records in there currently, feel free to change/delete them.
Mahendra S
Member
Thanks for the update Luke, a quick validation when you open the new draft it is throwing compile err
Will further verify the sa_table save and let you know.
Regards,
Mahendra
Will further verify the sa_table save and let you know.
Regards,
Mahendra
Hmm. That's a new one. First, I didn't do anything in that module...that module contains the code to handle dropdown choices, copying icon shapes. Second, error is a compile error based on XL compatability.
I'm running Office 2010 on Windows 7. Do you have a different version?
Would be curious to see what the help button says. File opens fine on my end.
Will re-attach, just in case something got messed up in upload.
I'm running Office 2010 on Windows 7. Do you have a different version?
Would be curious to see what the help button says. File opens fine on my end.
Will re-attach, just in case something got messed up in upload.
Last edited:
Mahendra S
Member
Hi Luke, I believe I have office 2010 on win7, File -> Help shows as below
How ever with new draft today it is still throwing the same err, and also noticed from code Debug, Compile VBA Project, giving this err
click ok gives missing properties as below
I will further investigate, but DB and DP dropdown's list donot show any values now.
Even Publish is throwing similar to first err
(will paste err in next thread....)
How ever with new draft today it is still throwing the same err, and also noticed from code Debug, Compile VBA Project, giving this err
click ok gives missing properties as below
I will further investigate, but DB and DP dropdown's list donot show any values now.
Even Publish is throwing similar to first err
(will paste err in next thread....)