| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Commissioning Issues Report Template | |||||||||||||||||||||||||
2 | Six charts for reporting Cx issues, plus a deep dive on who owns what. Native formulas only. No scripts, no add-ins. | |||||||||||||||||||||||||
3 | ||||||||||||||||||||||||||
4 | ||||||||||||||||||||||||||
5 | How to use it | |||||||||||||||||||||||||
6 | 1 | Look around first. The Issues tab is loaded with 150 made-up data center issues so you can see every chart working. | ||||||||||||||||||||||||
7 | 2 | Clear the sample data on Issues (columns A through L only). Leave the grey columns alone. They're formulas. | ||||||||||||||||||||||||
8 | 3 | Paste your issues into columns A through L. If you're pasting an export from your Cx software, paste values and line the columns up with the headers here. | ||||||||||||||||||||||||
9 | 4 | Go to Setup. Every status, priority, system, equipment type, issue type, party, and Cx level in your data shows up there automatically. Fill in the yellow cells: pick Open, Closed, or Ignore for each status, and enter a Rank and Target days for each priority. Anything still missing turns red. | ||||||||||||||||||||||||
10 | 5 | Clear the Report date override on Setup so aging is measured to today. | ||||||||||||||||||||||||
11 | 6 | Open Dashboard for the six charts. Open Deep Dive and play with the yellow filters. | ||||||||||||||||||||||||
12 | ||||||||||||||||||||||||||
13 | Color key | |||||||||||||||||||||||||
14 | Blue header | You fill it in (Issues columns A through L). | ||||||||||||||||||||||||
15 | Grey header | Calculated. Don't type over these. | ||||||||||||||||||||||||
16 | Yellow cell | An input or filter you can change. | ||||||||||||||||||||||||
17 | ||||||||||||||||||||||||||
18 | Issues columns | |||||||||||||||||||||||||
19 | Issue ID | Any unique ID. Text is fine. | ||||||||||||||||||||||||
20 | Date Opened | Date the issue was logged. Required. Rows without it are ignored. | ||||||||||||||||||||||||
21 | Cx Level | Which level of commissioning found it (L2 installation through L5 integrated, or whatever you call them). | ||||||||||||||||||||||||
22 | System | The system the issue belongs to (chilled water, UPS, controls, etc.). | ||||||||||||||||||||||||
23 | Equipment Type | The kind of equipment (chiller, CRAH, generator, PDU, etc.). Filter and heat map by this on Deep Dive. | ||||||||||||||||||||||||
24 | Equipment Tag | Optional. The specific unit. | ||||||||||||||||||||||||
25 | Issue Type | What kind of problem it is (installation deficiency, functional test failure, controls, documentation, etc.). Filter and heat map by this on Deep Dive. | ||||||||||||||||||||||||
26 | Description | Short description of the problem. | ||||||||||||||||||||||||
27 | Priority | Any naming works (P1, Critical, High, 1 - Urgent). You set the rank and target days on Setup. | ||||||||||||||||||||||||
28 | Responsible Party | Who owns the fix. | ||||||||||||||||||||||||
29 | Status | Any naming works. On Setup you mark each one Open, Closed, or Ignore (voided or duplicate issues). | ||||||||||||||||||||||||
30 | Date Closed | Fill in when closed. Drives days-to-close and the weekly trend. | ||||||||||||||||||||||||
31 | Days Open | Calculated. Open issues: age as of the report date. Closed: days it took to close. | ||||||||||||||||||||||||
32 | Age Bucket | Calculated for open issues: 0-7, 8-14, 15-30, 31-60, 60+ days. | ||||||||||||||||||||||||
33 | Target Days / Past Target? | Calculated from the priority targets on Setup. | ||||||||||||||||||||||||
34 | Week Opened | Calculated. Monday of the week the issue was opened. Useful for your own pivots. | ||||||||||||||||||||||||
35 | Status Group | Calculated. Open, Closed, or Ignore. Every chart reads this, not the raw status. | ||||||||||||||||||||||||
36 | Data Check | Calculated. Flags rows with a missing or text date, a closed issue with no close date, or no status or party. Blank means the row is fine. The Dashboard shows a count at the top. | ||||||||||||||||||||||||
37 | ||||||||||||||||||||||||||
38 | Setup | |||||||||||||||||||||||||
39 | Captured lists | Grey lists fill in from your data, in the order each value first appears. Nothing to type. | ||||||||||||||||||||||||
40 | Your picks | Yellow cells are yours: Counts As for statuses, Rank and Target days for priorities. Red means a value is missing. | ||||||||||||||||||||||||
41 | Re-pasting | If you paste your data in a different order, the grey lists can reorder. Take a quick look that each pick still sits next to the right status or priority. | ||||||||||||||||||||||||
42 | ||||||||||||||||||||||||||
43 | The six charts (Dashboard) | |||||||||||||||||||||||||
44 | 1 | Open issues by priority | ||||||||||||||||||||||||
45 | 2 | Open issues by system, largest first | ||||||||||||||||||||||||
46 | 3 | Open vs. closed by responsible party | ||||||||||||||||||||||||
47 | 4 | Open issue aging, split by your top four priorities | ||||||||||||||||||||||||
48 | 5 | Opened vs. closed by week, with the open backlog line | ||||||||||||||||||||||||
49 | 6 | Average days to close vs. target, by priority | ||||||||||||||||||||||||
50 | ||||||||||||||||||||||||||
51 | Deep Dive | |||||||||||||||||||||||||
52 | Filters | Priority, system, equipment type, issue type, Cx level, status, and a date window. Every number on the page respects them. | ||||||||||||||||||||||||
53 | Where every row went | Reconciles the Issues tab to the Deep Dive total: rows with data, minus Ignore, minus anything your filters remove. | ||||||||||||||||||||||||
54 | Headlines | Who has the largest share, who is slowest to close, who has the most past target, and who has the oldest open issue. | ||||||||||||||||||||||||
55 | Leaderboard | Pick what to rank by. Speed rankings ignore parties with too few closed issues (you set the minimum). | ||||||||||||||||||||||||
56 | Heat map | Party by system, equipment type, issue type, or Cx level (15 most common values). Shows where each party's problems are concentrated. | ||||||||||||||||||||||||
57 | ||||||||||||||||||||||||||
58 | Notes | |||||||||||||||||||||||||
59 | Capacity | Up to 5,000 issues. Setup captures up to 30 statuses, 15 priorities, 20 Cx levels, 150 systems, 250 equipment types, 50 issue types, and 100 responsible parties. Each list shows how many values it found and warns you if there are more than it can hold. | ||||||||||||||||||||||||
60 | Top-N views | Charts and tables show the biggest values first (top 12 systems and parties on the Dashboard, top 20 parties on Deep Dive). Everything else rolls into an All Others row, so totals always add up. | ||||||||||||||||||||||||
61 | Google Sheets | Upload to Google Drive and open with Google Sheets. All formulas are native to both. Chart styling may shift slightly on import. | ||||||||||||||||||||||||
62 | Excel | Works in Excel 2019, 2021, and Microsoft 365. | ||||||||||||||||||||||||
63 | Text dates | If you paste from an export, dates sometimes come in as text. They look normal but the Data Check column will flag them. Fix by re-entering them, or in Excel use Data > Text to Columns on the date column. | ||||||||||||||||||||||||
64 | Spelling counts | 'Controls Contractor' and 'Controls Contractor ' (trailing space) are two different parties. If Setup shows near-duplicates, clean them up in the Issues tab. | ||||||||||||||||||||||||
65 | ||||||||||||||||||||||||||
66 | Made by CxLens | |||||||||||||||||||||||||
67 | ||||||||||||||||||||||||||
68 | ||||||||||||||||||||||||||
69 | ||||||||||||||||||||||||||
70 | ||||||||||||||||||||||||||
71 | ||||||||||||||||||||||||||
72 | ||||||||||||||||||||||||||
73 | ||||||||||||||||||||||||||
74 | ||||||||||||||||||||||||||
75 | ||||||||||||||||||||||||||
76 | ||||||||||||||||||||||||||
77 | ||||||||||||||||||||||||||
78 | ||||||||||||||||||||||||||
79 | ||||||||||||||||||||||||||
80 | ||||||||||||||||||||||||||
81 | ||||||||||||||||||||||||||
82 | ||||||||||||||||||||||||||
83 | ||||||||||||||||||||||||||
84 | ||||||||||||||||||||||||||
85 | ||||||||||||||||||||||||||
86 | ||||||||||||||||||||||||||
87 | ||||||||||||||||||||||||||
88 | ||||||||||||||||||||||||||
89 | ||||||||||||||||||||||||||
90 | ||||||||||||||||||||||||||
91 | ||||||||||||||||||||||||||
92 | ||||||||||||||||||||||||||
93 | ||||||||||||||||||||||||||
94 | ||||||||||||||||||||||||||
95 | ||||||||||||||||||||||||||
96 | ||||||||||||||||||||||||||
97 | ||||||||||||||||||||||||||
98 | ||||||||||||||||||||||||||
99 | ||||||||||||||||||||||||||
100 |