Compile Data and Produce a Dashboard
- Thread starter Mahendra S
- Start date
Mahendra S
Member
Thanks Luke, this gives great view of visual representation, need to verify and will get back later.
Mahendra S
Member
Hi Luke, the progress bar form sometimes getting disappeared, this happened twice randomly, and popped up after some time not sure what was the latency though..
A small enhancement to tweak,
Enhancement1:
Please increase the bar width till the end of the frame during run time as -is it is coming little less after 100% complete.
Below messages need to be changed.
Instead of Sheets copied, can we change to
'Worksheets Published:<WS Source>
for example this has to be shown as
'Worksheets Published:QVR Prod, ATR Prod ...
(the rule remains same as Publish = Yes, but to populate WS Source instead WS Name)
# of sheets copied to be changed to
# of worksheets published: <number based on Publish=Yes>
Snapshot record: 89 to be changed to
Total Snapshot Records Published(Snapshot Id#89): 133
attaching the latest template with few interface change to Progress and Search screens for reference.
Regards,
M
A small enhancement to tweak,
Enhancement1:
Please increase the bar width till the end of the frame during run time as -is it is coming little less after 100% complete.
Below messages need to be changed.
Instead of Sheets copied, can we change to
'Worksheets Published:<WS Source>
for example this has to be shown as
'Worksheets Published:QVR Prod, ATR Prod ...
(the rule remains same as Publish = Yes, but to populate WS Source instead WS Name)
# of sheets copied to be changed to
# of worksheets published: <number based on Publish=Yes>
Snapshot record: 89 to be changed to
Total Snapshot Records Published(Snapshot Id#89): 133
attaching the latest template with few interface change to Progress and Search screens for reference.
Regards,
M
Hi M,
Not completely sure what you meant by progress bar disappearing. Does that mean:
Re: EN1
Yes, looks like you increased the width of the form. As I mentioned before, the width of bar was set as a constant, so and so the wider form was causing problems. I've changed the constant to a variable, now set to be the width of the frame - 5 pixels, which seems to give a good look.
Captioning has been changed.
Checked out the search box, noticed that the Tab order had gotten messed up, and corrected that as well.
Not completely sure what you meant by progress bar disappearing. Does that mean:
- form never showed up
- Form showed up, then disappeared, then reappeared at end?
- Form showed up, but progress bar never showed?
Re: EN1
Yes, looks like you increased the width of the form. As I mentioned before, the width of bar was set as a constant, so and so the wider form was causing problems. I've changed the constant to a variable, now set to be the width of the frame - 5 pixels, which seems to give a good look.
Captioning has been changed.
Checked out the search box, noticed that the Tab order had gotten messed up, and corrected that as well.
Mahendra S
Member
Luke, Thanks for the update, second case is happening if you have many tabs running I believe,
Form showed up, then disappeared, then reappeared at end?
I will further troubleshoot with additional data set and see if we can re pro.
Form showed up, then disappeared, then reappeared at end?
I will further troubleshoot with additional data set and see if we can re pro.
Mahendra S
Member
just verified today's draft, the third case is happening now
Form showed up, but progress bar never showed.
The bar it self got disappeared now..
Form showed up, but progress bar never showed.
The bar it self got disappeared now..
Mahendra S
Member
To repro this happens when you open the draft and ran first time the bar is not showing up
and second time if you run the progress bar shows up
and second time if you run the progress bar shows up
Mahendra S
Member
Thanks Luke, will evaluate further, what has been changed with the fix, please let me know.
Mahendra S
Member
Thanks Luke, please see below enhancements and let me know what you think.
Enhancement1: The Progress bar color needs to be controlled by 'Color of the header' i.e by 'HeaderColor' (cell R9) from SA.
as-is we are displaying following in LabelDesc2 instead
Can we display following in three different labels but located with in same frame and label style as 1-frmBoderStyleSingle.
# of worksheets published: <number based on Publish=Yes>
'Worksheets Published:<WS Source>
Total Snapshot Records Published(Snapshot Id#89): <TotalSnapshotRecords>
Please give appropriate meaningful label names to reflect good quality standards.
lblNumberofWSpublished, lblActualWSpublished, lblTotalSnpRecords for above three display components.
I hope this is simple and possible, otherwise we can leave as-is, please let me know.
Requirement1:
When we return the value for ex as below,
Is it possible to make url kind of link for Published Record, as soon as user clicks on Published Record, it should take to corresponding record in worksheet and point to the row on CGR Prod Defects Tab and highlight the same row with 'ColorCode' i.e cell R8 from SA.
above is just an example to illustrate this requirement
Please let me know the feasibility implementation on this do you see any issues.
Issue1: If we have large title as you see in 'INGRES Prod Defects' and when you generate reports both C-H-M-L reports from Advanced Reports the the report title need to show full string
INGRES-ERR26 - Error conditions re occur and failing to bring SSRS code value to cube.
instead we are showing as
please let me know if we can correct this title display issue in reports and this applies to all reports across where ever we are showing title. title should not be shrinked or should not be stripped to show from middle or random.
template remains same.
Best,
M
Enhancement1: The Progress bar color needs to be controlled by 'Color of the header' i.e by 'HeaderColor' (cell R9) from SA.
as-is we are displaying following in LabelDesc2 instead
Can we display following in three different labels but located with in same frame and label style as 1-frmBoderStyleSingle.
# of worksheets published: <number based on Publish=Yes>
'Worksheets Published:<WS Source>
Total Snapshot Records Published(Snapshot Id#89): <TotalSnapshotRecords>
Please give appropriate meaningful label names to reflect good quality standards.
lblNumberofWSpublished, lblActualWSpublished, lblTotalSnpRecords for above three display components.
I hope this is simple and possible, otherwise we can leave as-is, please let me know.
Requirement1:
When we return the value for ex as below,
Is it possible to make url kind of link for Published Record, as soon as user clicks on Published Record, it should take to corresponding record in worksheet and point to the row on CGR Prod Defects Tab and highlight the same row with 'ColorCode' i.e cell R8 from SA.
above is just an example to illustrate this requirement
Please let me know the feasibility implementation on this do you see any issues.
Issue1: If we have large title as you see in 'INGRES Prod Defects' and when you generate reports both C-H-M-L reports from Advanced Reports the the report title need to show full string
INGRES-ERR26 - Error conditions re occur and failing to bring SSRS code value to cube.
instead we are showing as
please let me know if we can correct this title display issue in reports and this applies to all reports across where ever we are showing title. title should not be shrinked or should not be stripped to show from middle or random.
template remains same.
Best,
M
Mahendra S
Member
sorry attaching the template to see the Title issue as given large title in INGRES Prod Defects Id 26 for example..
Re: ENH1
Code now sets progress bar color = HeaderColor upon running macro.
Breaking up the output into multiple labels is not simple nor easy. As the number of sheets being copied is variable, we do not know how long (how many lines) we would need for the first bit. Thus, we would "somehow" have to make the other two labels float so that they have room for the first. This would also require additional lines calling out each block rather than just 1. If you can figure out a way to make a dynamic, floating label, then maybe we can progress. As is, I think it's good enough.
Re: Req1
First, I changed the search function to return 2 additional lines of text, the sheet name and cell address. I then feed those two values into some private variables in the Search form. The Label_click event uses those variables to go to the sheet desired (if there wasn't a value found, click does nothing).
Row gets highlighted. If user changes the search, or closes search form, highlighting is reversed.
Changed the search form to be non-modal, meaning that user can interact with the spreadsheet while form is visible.
Re: Issue1
I'm not sure what you want to happen here. The report body already takes up the full width of the page, so we can't increase column width. Do you want to decrease the font size, or trim all titles to be the first 30 characters, or train your employees to make shorter titles...?
If it's a width/page setup type change, you should be able to do that yourself. Unhide the Pivot sheets, and make the changes you think will work.
Code now sets progress bar color = HeaderColor upon running macro.
Breaking up the output into multiple labels is not simple nor easy. As the number of sheets being copied is variable, we do not know how long (how many lines) we would need for the first bit. Thus, we would "somehow" have to make the other two labels float so that they have room for the first. This would also require additional lines calling out each block rather than just 1. If you can figure out a way to make a dynamic, floating label, then maybe we can progress. As is, I think it's good enough.
Re: Req1
First, I changed the search function to return 2 additional lines of text, the sheet name and cell address. I then feed those two values into some private variables in the Search form. The Label_click event uses those variables to go to the sheet desired (if there wasn't a value found, click does nothing).
Row gets highlighted. If user changes the search, or closes search form, highlighting is reversed.
Changed the search form to be non-modal, meaning that user can interact with the spreadsheet while form is visible.
Re: Issue1
I'm not sure what you want to happen here. The report body already takes up the full width of the page, so we can't increase column width. Do you want to decrease the font size, or trim all titles to be the first 30 characters, or train your employees to make shorter titles...?
If it's a width/page setup type change, you should be able to do that yourself. Unhide the Pivot sheets, and make the changes you think will work.
Mahendra S
Member
Luke, Thanks for the prompt update,
ENH1: I anticipated and understand the complexity with new label instead new line in ENH1, how ever can we separate with a line for each of them please let me know or highlight the captions with bold and order as below
# of worksheets published:
'Worksheets Published:
Total Snapshot Records Published(Snapshot Id#<value>):
please let me know on above.
REQ1: Cool, one small adjustment, as soon as we display the row by clicking label (Published Record) it is going to corresponding ws with highlighted color which is good, how ever we need to automatically close the Search screen after displaying the row in related ws.
ISS1:Can we have first 30 characters shown in MD tab, this is not happening now, and reduce the font size in pdf report to show the full title. The title can be some times large based on issue atleaset to show first 50 characters if it is too long.
Please let me know on this.
ENH1: I anticipated and understand the complexity with new label instead new line in ENH1, how ever can we separate with a line for each of them please let me know or highlight the captions with bold and order as below
# of worksheets published:
'Worksheets Published:
Total Snapshot Records Published(Snapshot Id#<value>):
please let me know on above.
REQ1: Cool, one small adjustment, as soon as we display the row by clicking label (Published Record) it is going to corresponding ws with highlighted color which is good, how ever we need to automatically close the Search screen after displaying the row in related ws.
ISS1:Can we have first 30 characters shown in MD tab, this is not happening now, and reduce the font size in pdf report to show the full title. The title can be some times large based on issue atleaset to show first 50 characters if it is too long.
Please let me know on this.
Mahendra S
Member
Regarding REQ1: I think no need of doing anything further as we have flexibility to close the Search screen by clicking Close button which is good, I just over-sighted on this, no changes required further, well done.
Re: ENC1
We can certainly add in some extra spacing. With the bolding, I think you are saying you want the headers to be bold, but the text non-bold. This can't be done...with labels, you can make all of the caption bold, or none of it bold. Can't mix & match.
Re: Req1
Good to hear! The nice thing is that we can use the Close event to remove highlighting still then.
Re: Issue1
I've adjusted the formatting on MD to no longer try to do Wrap text, causing the first part of title to appear in cell. Adjusted formatting of PivotTables likewise, and reduced font size to try and make more of the title visible.
We can certainly add in some extra spacing. With the bolding, I think you are saying you want the headers to be bold, but the text non-bold. This can't be done...with labels, you can make all of the caption bold, or none of it bold. Can't mix & match.
Re: Req1
Good to hear! The nice thing is that we can use the Close event to remove highlighting still then.
Re: Issue1
I've adjusted the formatting on MD to no longer try to do Wrap text, causing the first part of title to appear in cell. Adjusted formatting of PivotTables likewise, and reduced font size to try and make more of the title visible.
Mahendra S
Member
Thanks Luke, that is fine just leave as -is with what we have for ENC1.
remaining will verify, I saw some weird issue when u Publish as-is with default set and with out even any import and following popped up(Added Sheets) which I believe is incorrect
Please let me know on this if u can today!
remaining will verify, I saw some weird issue when u Publish as-is with default set and with out even any import and following popped up(Added Sheets) which I believe is incorrect
Please let me know on this if u can today!
Mahendra S
Member
Thanks Luke, will further verify and let u know if any issues next week.
Have a good weekend!
Have a good weekend!
Mahendra S
Member
Luke, Thanks for checking, a few changes we might need to do.
I have modified the code to show the pdf report text and alignment and related.
will write the change in a while or eod.
I hope you are doing great!
Regards,
M
I have modified the code to show the pdf report text and alignment and related.
will write the change in a while or eod.
I hope you are doing great!
Regards,
M
Mahendra S
Member
Hi Luke,
How are you, hope this finds you well.
a few enhancements I would like to discuss as below.
ENH1: I have added a new status 'On Hold' in status table in SA.
The basic rule need to change in the code where we are converting if a Status is On Hold to Postponed.
Instead of above conversion we just need to implement the rule to verify and keep this status unique.
The Priority would be 'Medium' if the status is given as 'On Hold' for a defect.
In case if the Priority given is any thing other than Medium in source tabs, then when we run the Publish it should adjust to 'Medium'
Similarly a small change in status = 'Postponed' for which the priority would be always 'Low'.
I have included the icon for On Hold in the cell I17 for your ref.
Please let me know feasibility on this enhancement.
ENH2: Based on above ENH1 we have impact on DB for all the tables and charts. we need to populate the 'On Hold' row before Total for all the three tables in DB as well as we need to adjust the Charts to include On Hold.
I hope these two enh's are possible, but please let me know if you see any issues with implementation.
ENH3: Team PDF report need to have Header with Report Title as 'Report By Team' and the Data Refresh & Publish As of should be on right hand side to be consistent.
Can we display the Count number of total defects for each team
for ex in the pdf report the Team column need to
RN
(3)
RN-QA
(1)
if this is not possible we can live as is, but good to show like above to have clear visibility on total defects for each team in the pdf report.
I believe we need to create a formula or populate dynamically, let me know if you see any issues with this approach.
ENH4: Open By Status PDF report need to have Header with Report Title as 'Report By Open Status' and the Data Refresh & Publish As of should be on right hand side to be consistent.
Also in the pdf report we need to display the relevant symbols
like Critical and in next line cell need to display the symbol (what we have in I6 from SA) , High and below the symbol, Medium and below the symbol, Low and below the symbol
ENH5: In MD tab can we display in the ROW1 we have the large rectangle to show with first table we have from DP from rows (A4 -F6)
This is kind of bit extension to the DP however it will give summary on top and then we will have all defects as we are currently showing.
please see the latest template, I had just pasted the image in MD for ref.
I understand this is bit tricky and complex, let me know your thoughts on this ENH.
Issue1: I have added the pivoting format few lines of the code, to reflect all the columns in the pivot reports to show the property wrapTest = True
how ever this is not effective if we have larger text in title it is cutting off in the pdf report, is it possible to display the text accurate with cell height adjusted based on the title.
can we adjust this format when we generate the pdf report.
Also the Defect Id some times large and need to wrap but due to cell size it is getting off in the report I believe.
I have added the few records sample in RN Prod tab when you publish and generate the report you might see above discrepancy.
attaching the modified template for your ref.
Best,
M
How are you, hope this finds you well.
a few enhancements I would like to discuss as below.
ENH1: I have added a new status 'On Hold' in status table in SA.
The basic rule need to change in the code where we are converting if a Status is On Hold to Postponed.
Instead of above conversion we just need to implement the rule to verify and keep this status unique.
The Priority would be 'Medium' if the status is given as 'On Hold' for a defect.
In case if the Priority given is any thing other than Medium in source tabs, then when we run the Publish it should adjust to 'Medium'
Similarly a small change in status = 'Postponed' for which the priority would be always 'Low'.
I have included the icon for On Hold in the cell I17 for your ref.
Please let me know feasibility on this enhancement.
ENH2: Based on above ENH1 we have impact on DB for all the tables and charts. we need to populate the 'On Hold' row before Total for all the three tables in DB as well as we need to adjust the Charts to include On Hold.
I hope these two enh's are possible, but please let me know if you see any issues with implementation.
ENH3: Team PDF report need to have Header with Report Title as 'Report By Team' and the Data Refresh & Publish As of should be on right hand side to be consistent.
Can we display the Count number of total defects for each team
for ex in the pdf report the Team column need to
RN
(3)
RN-QA
(1)
if this is not possible we can live as is, but good to show like above to have clear visibility on total defects for each team in the pdf report.
I believe we need to create a formula or populate dynamically, let me know if you see any issues with this approach.
ENH4: Open By Status PDF report need to have Header with Report Title as 'Report By Open Status' and the Data Refresh & Publish As of should be on right hand side to be consistent.
Also in the pdf report we need to display the relevant symbols
like Critical and in next line cell need to display the symbol (what we have in I6 from SA) , High and below the symbol, Medium and below the symbol, Low and below the symbol
ENH5: In MD tab can we display in the ROW1 we have the large rectangle to show with first table we have from DP from rows (A4 -F6)
This is kind of bit extension to the DP however it will give summary on top and then we will have all defects as we are currently showing.
please see the latest template, I had just pasted the image in MD for ref.
I understand this is bit tricky and complex, let me know your thoughts on this ENH.
Issue1: I have added the pivoting format few lines of the code, to reflect all the columns in the pivot reports to show the property wrapTest = True
how ever this is not effective if we have larger text in title it is cutting off in the pdf report, is it possible to display the text accurate with cell height adjusted based on the title.
can we adjust this format when we generate the pdf report.
Also the Defect Id some times large and need to wrap but due to cell size it is getting off in the report I believe.
I have added the few records sample in RN Prod tab when you publish and generate the report you might see above discrepancy.
attaching the modified template for your ref.
Best,
M