Hi there,
I'm trying to set-up a monthly Client Report Workbook where I can:
- export data reports from an online software
- Report on how much time has been used out of a block time purchase
- consolidate support ticket items and group according to Billable or Non-Billable time
- automate pdf report production and email delivery of the reports.
I have got Steps 1 and 3 sorted, where the 3rd step is a pivot table using the different company names as the filter. I would like to include another data point (Ticket Number) in this table as it relates to specific Tickets.
I'm having trouble with the best way to assemble the report top look like existing (very manual) reporting.
I have also seen the MOTH method of "Automating the report and emailing".
All a bit of fun!!
Hi Al,
Welcome to our forum!
If you can upload a sample Excel file we can try to help you with step 2. Please ensure the sample data covers all scenarios and provide an example of your desired result.
Mynda
P.S. I hope you're using Power Query for step 1 😉
Attached is a simplified workbook I have created. I have used PowerQuery to populate the Monthly Ticket Details, Company Block Hours Purchases, and the Reporting Contacts Sheets - linked to other files in my network drive.
I'm trying to represent the Consolidated Ticket Info table on the Client Report Sheet so that is changes with the Filter Change on Consolidated Ticket Info cell B1.
Once the Report populates correctly, I need to PDF and send to each contact on the Reporting Contacts page, where the Contact's Company alters the above mentioned filer and attaches the correct company report to the right email. (no breaches of privacy - please).
Thanks Mynda and Team.
Hi Al,
No file attached. You have to click on Start Upload to upload the file.
Phil
Attempt 2
PS. There can be anywhere from 0 up to 60+ lines in the Consolidated Ticket Info, which can make the presentation of the info tricky in the Client Report sheet.
Hi Al,
Implementing Step 4 isn't too difficult but I'm not sure who the emails should be sent to.
For example, if the PT filter on Consolidate Ticket Info is Hob Clique does an email get sent to all 3 of Patrick, Rebecca and Simon?
Regards
Phil
PS. I'd steer well clear of merged cells. They cause all sorts of issues. On your Client Report sheet you would be better to Centre Across Selection. After demerging the cells, select the cells you want the text centered in, then press CTRL+1 -> Alignment tab -> Horizontal (Drop Down List ) -> Centre Across Selection.
Good morning Philip,
You are correct - for each contact for a company (Patrick, Rebecca and Simon with Hob Clique in this case), they need a copy of the report relevant to their company.
Thanks for the tip on Merged Cells.
Cheers, Al.
PS. I'm also having issues with VBA coding to resize the Pivot Chart depending on the number of entries for that time period. For example, in a particular month, a Client can have anywhere between 0 and 75 service tickets. As you can imagine, setting the chart height to accommodate for the 75 tickets looks pretty silly when there are only 1 or 2 items, and very crammed in the reverse case.
Hi Al,
Attached is a file that creates a PDF report and it emails to the relevant people based on the company names and emails on the Reporting Contacts sheet.
On the PT sheet there's a green button to click that runs the macro.
Please check it all works as you want.
Regards
Phil
You're a GREAT Team @ MOTH. Thank you Phil for taking the time to do this.
Now to finesse the report so it is aesthetically pleasing for the Clients.
Cheers, Al. 😉
You're welcome Al 🙂
