Compile Data and Produce a Dashboard

[Mahendra S]

Thanks Luke, There are some edge cases with Issue2, will analyze further and write later.
I appreciate all the effort with pdf report which is one another great feature on dashboard reporting, will look in to further and let you know the feedback.

Thanks again for all the great support!
Regards,
M

 

[Mahendra S]

Hi Luke, I think for time being let us leave as-is with independent tables in dashboard for Issue2 from yesterday.

You are correct there are chances user might think table2 is dependent on table1.
but table3 gives combination results which are correct.

Also if source and group are different the combination value gives 0 in table3 and this should be true always.
please note we will have Group name which is same as Source, this condition occurs
(For ex, Source = CRM, Group = CRM and also other scenario
Source = CRM and Group = RS in this case table 3 shows 0 which is correct (in reality this combination wouldn't occur, since the value is 0 we are ok.)

Let us see if we come across any other combination which can potentially result negative impact, then we need to revisit the independent table logic.

One another combination I am thinking is dashboard report based on WS Source.(remember in the past we attempted to build this when we created this column in DP, but left that b'cos Source is always Primary)

Requirement1: Based on Generate WS Source Report button click from SA, need to generate pdf report very similar to DB Source Report, but in this case you will not have a table and chart existing in dashboard.

Is it possible to generate this report dynamically with same table format and chart, hopefully possible but not sure,
If we are able to create then we will have complete set of reports. please let me know on this otherwise also fine.

Issue1: Please see the latest attachment for this issue.
If I try to insert another worksheet based on command bar ply control right click (Move or Copy...) from another spreadshet,

for example Make a copy and insert Oracle Prod Defects from old draft sheet where we dont have table names.
(of course before inserting I have deleted existing Oracle Prod Defects in current draft and inserted from old draft)
In this scenario, you will see table name as Table_owssvr_153. when you try to run WS-H sync in this case it doesn't create based on our naming convention tbl_OracleProdDefects.

This is not a major issue but wondering how the logic and proc is built.
Please clarify, not sure if this issue can be resolved or not for consistency purposes.

Regards,
M

 

[Mahendra S]

Hi Luke, I hope you are doing good and hopefully if any updates on above please let me know.
I understand Requirement1 might be complicated, will work with you.

Have good long weekend!

Regards,
M

 

[Luke M]

Hi M,

Long weekend for me, so I'll take a look at this tomorrow.

 

[Luke M]

Re: Requirement1
We can certainly do this. I actually ended up creating an additional table and chart on the Dashboard sheet, and then hid the rows. Then, because of versatility of PDF report macro, it was fairly easy to make another WsSourcePDF report.

Re: Issue1
Before, the code was written where we checked if a table exists, and if not, add one and change the name. I've rearranged the code to (psuedocode)

If table exists then
      create table
end if
Change table name

This will then make sure all the tables follow same naming convention.

 

[Mahendra S]

Thanks Luke, I hope you had very good long weekend!
I believe the hid rows are after row39, one q how many rows are getting hidden, please let me know.
It looks like the report is coming based on WS Source, will verify and let you know if any issues.

Regarding Issue1, need to verify again with more tables, let us see if any impacts.

Regards,
M

 

[Luke M]

Currently set to hide rows 40:70, but this can be changed if needed.

 

[Mahendra S]

Hi Luke, Thanks for the update.

Please let me know the feasibility of following enahncement.
Enhancement1: Can we show (un hide)the rows (40:70) when user selects Enable Ribbon in SA tab, similarly hide when selects Disable Ribbon.
I hope this is possible.

Issue1: In the latest draft in DP Tab, Env Filter is showing in correct values, instead of env values (All, Prod, QAT) it is showing WS Source values I believe, is this because of in correct calling of column values, please let me know on this.

Issue2: In the PDF reports can we reduce the space gap between table and chart to just two or three lines so that values can be compared easily between table and chart, please let me know on this adjustment.

Issue3: can you please verify the values of WS Source report values between Table and Chart to make sure the formula's are correct, I will also verify (once we have above enhancement) since this need to match with records that gets shown in DP for Critical and High.

Regards,
M

 

[Luke M]

Re: Enchancement1
This option has been added to the enable/disable ribbon macros. Also, only hides rows 45:70 now. Realized that row 40 was part of last table, should not have been hidden.

Re: Issue1
When I added the new dropdown for lastest PDF report, I forgot to clear the list before moving to next dropdown. Code in UpdateCBs has been updated, problem resolved.

Re: Issue2
PDFReport macro modifed, chart is now placed 2 rows below table. I agree, this looks much nicer.

Re: Issue3
Table appears to be accurate, formulas are looking at correct column.

 

[Mahendra S]

Thank you so much for prompt response Luke. I will verify today later with additional load and let u know if any issues.
Have a good weekend!

Best,
M

 

[Mahendra S]

Hi Luke, I hoe you had good weekend.

The new draft is working pretty cool with few minor issues which I will write later which can resolve bit later.
How ever I have created a new button Advanced Reporting Options in SA tab.
Need your thoughts on following requirements, sure you may like the user interface and requirement.

Requirement1: By clicking Advanced Reporting Options in SA tab a new user interface usrFrmReports need to be popped up. (Please see this new UI in Forms)

This form has two option buttons as we start with.
Option1: Report By Source Summary, By selecting this option and click Ok button need to generate a PDF report in snipshotarchive folder with following content.
Basically I have given the sample in AdvReports tab, this report need not required to be in the tab, for reference on content and design I have included.

Otherwise by selecting this option from usrFrmReports the pdf report should get generated.
The Ok button I took from other form which need not be greyed out but u can decide on this. Cancel should just get back to SA tab.

Requirement2: This requirement is familiar to you as we attempted in the past but due to design constraints we left and I hope we can implement this one.
By clicking Report By Source + Group option from usrFrmReports in run time need to generate a pdf report in snipshot archive .

Here the key design note is very similar to Dashboard first two filters (Source and Group)
the difference in this report is Source is always parent and Group is child of the source.
Which means the report content is very similar but only groups associated with the source need to be generated.

I hope this requirement is clear, please let me know on this

Attaching the latest template.(some reason not getting attached, send it seperately)

I appreciate all the great support.
Regards,
M

 

[Mahendra S]

for some reason the file is not getting attached

the Adv Reports tab I referred above looks like below



and UI as below


Luke, please send me your emailid, will attach the template if above screenshots are not helpful, for some reason the attachment is not going here.

 

[Mahendra S]

adv reports tab design:

 

[Mahendra S]

Luke, Finally able to attach the template in zip file, please take a look.

Thank you.

Regards,
M

 

[Luke M]

Hi Mahendra,

Several changes in this one. I moved all the charts/tables that aren't immediately visible in the Dashboard to the Adv Report worksheet. I like the idea of using this like a formula sheet, where we do all the behind the scenes type stuff to build objects, and then pdfReport is where we display them.

There was some sort of internal error going on with the form you had originally built, where XL kept crashing. I had to rebuild it, but it seems to work the same.

Both options on the Adv Reporting button now produce reports.

 

[Mahendra S]

Thank you so much Luke! yeah I have noticed the circular ref err with my spreadsheet, I just copied some rows for design purpose which was the reason, but it was great to see first summary report. second one I need to look into still, will get back later.

Thank you very much again,
Regards,
Mahendra

 

[Mahendra S]

Hi Luke, The Summary Report is great, but need few adjustments as below.

Issue1: the title of the chart need to be "Defect Summary Report By Priority Trend" and the chart to be inclined format like as below, please let me know on this.



Issue2: In Dashboard Tab, the Display bar charts button is throwing system err, I believe this might be b'cos of adv reports not sure.

Issue3: AdvReports tab is getting added to the Publish table in SA when u click on PublishDefectSet, where as this AdvReports should not get added to the Publish table in both conditions click on PublishDefectSet & click on worksheet synch buttons. For time being I just gave Publish=No.
I hope we can exclude this in code with select case or some other way please let me know.

Issue4: Source + Group Report from Advanced Reports.

I believe this original requirement is meant to be bit different as I would like to re state here.

Here the key design notes we need to consider for this report.

• The difference in this report is Source is always parent and Group is child of the source.Which means the report content is very similar but only groups associated with the source need to be generated.
• We don't need to generate Source=All in this report.
• Important factor is in PDF report we should see as below,

For example, Based on Publish table any way we will identify sources for Publish that means, Source= TBI then only Groups associated with TBI = TBI-BI related data should be shown in table and chart.

and second table we will show Source = BEI, so all Groups only associated with BEI will get displayed in table and charts.​

The recordset is very similar from Master Data if you see column M for Groups based on how u r populating with Source in column A.​

I hope this is clear, otherwise please let me know.


Please see the attached template for reference.

I appreciate the great support.

Regards,
M

 

[Mahendra S]

In other way to make it more simple we just need to display only single table as below for Issue4
(but Source and Group relation as Parent and Child is critical for this report)



The only thing is please just consider above table as design element, I just took screenshot.
However, if we can give an empty row between each source related that would be great.

for example in above we need to have a empty row after TBI-BI. since remaining all belongs to source=BEI.
similarly if we have third row as Oracle all oracle related groups data to be shown in the same table.

Not sure if we can generate chart but if we can good otherwise also fine.

I hope this is clear, please let me know on this.

Regards,
M

 

[Luke M]

Re: Issue1
Chart title has been corrected. It appears you are requesting a 3D, cylindrical bar chart with a 3D rotation in both X and Y axis. With all the Sources listed, that chart appears like this:


As you can see, due to the rotation taking up more of the chart space, half the series labels aren't showing. The Low bar for the 2nd series appears to be slightly less than 5 (you can see the line behind it), and yet, it's value is actually 5. It's very hard to even see that different bars in that have smaller values, due to them behing rotated "into the screen". In short, 3D charts are 99.9% dilute the clarity of the date, and 100% of the time take up more space than a simple chart does. Here's the same date, in simple bar form:

I've left the chart in the simple form for now. If you insist, I will change the format, but it would be my strong recommendation to NOT make it a 3D chart.

Re: Issue2
Correct, this was because I moved the chart. I have ammended the code to no longer try to change a chart that is not there.

Re: Issue3
AdvReport listed to the 'ignore' list. No longer apperas in WS table.

Re: Issue4
Thanks for showing the table, that helped me understand what you were after. I think I've got this going the way you want it, and I was even able to include a chart. Becase the pdfReport macro starts with "ALL" for everything else, this report also includes it. Is it a strong requirement NOT to include All, or was it just not necessary?

 

[bobhc]

Luke M you missed a chance of earning with this thread, should have put youself forward as the consultant...

 

[Luke M]

Luke M you missed a chance of earning with this thread, should have put youself forward as the consultant...

When Chandoo gets the Consultancy forum/thread open, I'll probably put a link to this thread as a sort of portfolio.

 

[Mahendra S]

Thanks much Luke, I will evaluate further on Issue4, it is not a hard requirement to have All, but thought it is optional, if we have more sources then the report will spread so many pages which the concern, but need to validate and see how this looks, will get back on this.


Regards,
M

 

[Mahendra S]

Also the Issue1, yes it is meant to 3d but that is fine to have with simple chart as you have.

Thank you,
Regards,
M

 

[Mahendra S]

Hi Luke, The Source+Group report in Advanced is really cool. Only few cosmetic issues I believe these can get resolved.

Issue1: The PDF report borders in the table are not same, we just need thin inner border for all rows and columns and thick outside border in table when we show in pdf (pl see in below 3 to 6 rows are thin where as others are thick with in table, it would be nice to have consistent, not sure is this formatting issue when it capture as snapshot.


Issue2: Some times the names on left is getting squished is it possible to have the chart table as same size in the pdf so that names will have sufficient space on left side, may be increase the space for name with in table.
(for ex I took screenshot from source+gr report as below)


I hope you definitely like this requirement.
Requirement1: I have added a new button Multi WS Import button in SA tab.
When you click on you will see usrFrmMultiImp form as popup.

The functionality is simple by clicking Browse... button you should be able to select an excel file and and click on Import button it should insert tabs from imported spreadsheet.

The tricky part is whether we can insert/import all the tabs to template or not need to know.
If this is thru then I believe rest may not be complex, however I would like to call out design needs as below

There are some design conditions we need to make sure.

- Only the XLSX files should be imported.

- In case if import file has tabs (super Admin, DashBoard, DefectsPriority, Master Data by chance then we should not let them impoted or inserted in to template.

- If Import file has same tab as already in template then it should insert all other tabs first and then ask for overwrite or not at end, the similar functionality we implemented for one of the sync feature dont remember though..

- As soon as new tabs imported the Publish table should be updated with new values by default as
Publish=No and WSNmae = Tab Name and remaining values as NA.

- Here one more assumption is all imported tabs will have columns (col a to col n).
-There are possible chances the import file may have WSRowId some times and sometimes may not have,
The functionality we have WS-H Sync we can enforce when we download tabs.

later we can automate this as well for time being let us go with this assumption.​

Please let me know on this requirement as this feature becomes important to import and execute.
One way to verify this functionality is remove te existing tabs from template and have previous template save as XLSX and import the file any way we have tabs in import file so that you dont have to create all tabs.

Attaching the updated template with new form (usrFrmMultiImp) for your reference.
I appreciate the great ideas and support!

Best,
M

 

[Luke M]

Re: Issue1
This is actually an illusion of sorts. If you zoom further into the document, you'll notice that different line appear bolded, and eventually (as you zoom), all the lines appear (as they really are) to be the same thickness. If you print the report, they would also be the same thickness. I'm not sure what causes the illusion, just that it has to do with the current monitor setup + zoom level + mood of XL.
With that, I can't "fix" it, as it's not really something that's broke from the report's perspectice.

Re: Issue2
I formatted the y-axis to show every label, and made the chart bigger to give them more space. I also noticed that the Total row was getting labelled incorrectly, and have corrected that.

Re: Requirement1
This will take awhile to work through, as lots of parts to implement and check. Will post back when I have more, but wanted to update you one above Issues.