<style>p { margin: 0; }span.fr-emoticon.fr-emoticon-img { background-repeat: no-repeat !important; font-size: inherit; height: 1em; width: 1em; min-height: 20px; min-width: 20px; display: inline-block; margin: -0.1em 0.1em 0.1em; line-height: 1; vertical-align: middle; } span.fr-emoticon { font-weight: normal; font-family: "Apple Color Emoji", "Segoe UI Emoji", "NotoColorEmoji", "Segoe UI Symbol", "Android Emoji", "EmojiSymbols"; display: inline; line-height: 0; } blockquote { border-left: solid 2px #5e35b1; color: #5e35b1; margin-left:0; padding-left:5px;}blockquote blockquote{ border-color: #00bcd4; color: #00bcd4;}blockquote blockquote blockquote{ border-color: #43a047; color: #43a047;} table.grid{ border-collapse: collapse;} table.grid td, table.grid th { border: 1px solid #ddd;} .fr-fic.fr-dib{ display: block; margin: 5px auto;}.fr-fic.fr-dib.fr-fir{ text-align: right; margin: 5px 0 5px auto;}.fr-fic.fr-dib.fr-fil{ text-align: left; margin: 5px auto 5px 0;}.fr-fic.fr-dii{ float: none; margin: 5px auto;}.fr-fic.fr-dii.fr-fil{ float: left; margin: 5px auto;}.fr-fic.fr-dii.fr-fir{ float: right; margin: 5px auto;}img.fr-dib.fr-fir { margin-right: 0; text-align: right;}img.fr-dib.fr-fil { margin-left: 0; text-align: left;}img.fr-dib { margin: 5px auto; display: block; float: none;}img.fr-bordered { box-sizing: content-box; border: solid 5px #CCC;}img.fr-shadow { box-shadow: 10px 10px 5px 0px #cccccc;}img.fr-rounded { border-radius: 10px; -moz-border-radius: 10px; -webkit-border-radius: 10px; -moz-background-clip: padding; -webkit-background-clip: padding-box; background-clip: padding-box;}</style><p><strong>In this guide we will cover:</strong></p><p><strong>- What is a Statistics Table?</strong></p><p><strong>- Creating a Statistics Table</strong></p><p><strong>- Reporting on Statistics Tables</strong></p><p><br></p><p><br></p><p><strong>Related Guides:</strong></p><ul><li style="font-weight: bold;"><a href="https://usehalo.com/haloitsm/guides/2638" target="_blank" rel="noopener noreferrer"><strong>Ticket Volumes and Forecasting</strong></a></li></ul><p><br></p><p><br></p><p id="isPasted"><strong><span style="font-size: 14pt;">What is a Statistics Table?</span></strong></p><p id="isPasted">Statistics tables are type of Custom Table which allow you to take a snapshot of data based on a query which is run at regular intervals.</p><p><br></p><p id="isPasted">These can be used for:</p><p><br></p><ul><li>Capturing point-in-time data for reporting purposes - e.g. current open ticket count at the end of every day, or sales pipeline data every month.</li><li>Simplifying complex reports. Resource intensive calculations and CTEs can instead be pushed into a table with the data already calculated and formatted and then read from there</li><li>Preparing a dataset for Forecasting.</li></ul><p><br></p><p><strong><span style="font-size: 14pt;">Creating a Statistics Table</span></strong></p><p id="isPasted">Setting up a Statistics table involves writing a SQL query to pull data from the database, defining the columns in the Custom Table to insert the query data into, defining at what interval you want to capture the data (Daily, Weekly or Monthly), and setting up a schedule to automatically populate the table.</p><p><br></p><p id="isPasted">Navigate to Configuration > Custom Objects > Custom Tables, and create a new Custom Table. Name your Custom Table, and then set the highlighted fields below.</p><p><br></p><ul><li>"Link to another entity" to "None".</li><li>"Type" to "Statistics Table".</li></ul><p><br></p><p id="isPasted">Further configuration options to create the Statistics Table will then show.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjA3MzA5N2Q3LWQzYTAtNDczOC05MmVhLTc3MTAwZDQ4MDNhOCJ9.hR_PUU2xhpoFuikttGm60eLoKmY-PRn8e4cDyMox0eg" class="fr-fic fr-fil fr-dib" width="1219" style="width: 1221px; height: 668.05px;" height="668"></p><p><strong><span style="font-size: 10pt;">Fig 1. Creating a Statistics Table Custom Table.</span></strong></p><p><br></p><p>Scrolling down, here is where you can write the SQL query. @startdate and @enddate can be used here to filter data based on the period that the data is being captured for, but are not required for most use cases.</p><p><br></p><p>You will then need to define the schema based on the columns used in the SQL query. Click the "Add" button on the "Schema" table above the query. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjBmYTRjM2YyLTk5MjEtNDViNy04OWQwLTc3ZTBhOGNiZWY5OSJ9.3H64Ng3aTMHVMnx1rlXV501kFYUA49UUX9Y-6Bp7mcw" class="fr-fic fr-fil fr-dib" width="1218" style="width: 1220px; height: 564.681px;" height="565"></p><p><strong><span style="font-size: 10pt;">Fig 2. Writing the report.</span></strong></p><p><br></p><p>A popup will show to type the column name, and select its data type from a dropdown. Click "Save", and repeat this process for each column in the query.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjQzMDBhNzA1LTBmNjAtNDg2My04OWVhLTQwOWM5ZjEwMTRlMSJ9.YK6ZEJqK6Kj_PiRfnVBLufyf9Y0zE1eC91eco4V7ifk" class="fr-fic fr-fil fr-dib" width="708" height="532"></p><p><strong><span style="font-size: 10pt;">Fig 3. Setting the schema for the query.</span></strong></p><p><br></p><p>At the bottom of the page, you can then choose the reporting frequency shown in the data - Daily, Weekly or Monthly. This sets the "Period" column which will be added to the data outset. For example, "Daily" will show an exact date, whereas "Weekly" will show as "WB" (week beginning) a date.</p><p><br></p><p>Then, choose whether running the data capture now means it is reporting on the current period or the previous period.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImE4ZmU3MzQzLWM2ZjktNGYxYy05MjdhLTU3MjVlODMwMzVjMiJ9.tM2loURnevpDZXCcvi0e5CYlmlnwFg2by-4pFeEyefI" class="fr-fic fr-fil fr-dib" width="1221" style="width: 1223px; height: 301.058px;" height="301"></p><p><strong><span style="font-size: 10pt;">Fig 4. Setting the period used in the data.</span></strong></p><p><br></p><p>Save your Statistics Table, and scroll back down to the bottom. The "Set Schedule" button will now no longer be greyed out, and can be clicked on to configure a schedule for when to automatically capture the data. </p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjQ2MzJkYTAxLTdlNjUtNDI4OC1hY2I1LTA3YWNhYzk1MjRhZiJ9.Ox95YUUS8nkVkRyL7o36SNLeRRAVEvEQ1tk-AmYSTpM" class="fr-fic fr-fil fr-dib" width="1221" style="width: 1223px; height: 220.874px;" height="221"></p><p><strong><span style="font-size: 10pt;">Fig 5. Set Schedule button.</span></strong></p><p><br></p><p>This schedule would normally be inline with the frequency but can be delayed - e.g. you may want the schedule to run a day later if the data isn't ready to insert immediately. In this case, you can set the period to capture as the previous period and have the schedule run a day after your data is ready to capture.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6Ijg3NjRhMDc5LWQ0MzEtNGYxNC05ZmUzLTc1NDZjMDhiNTIyOSJ9.DcnHVBWcMg_j2Dk-d4ASaDmOFkJ4Xq9dkVWdPX2uK58" class="fr-fic fr-fil fr-dib" width="1222" style="width: 1224px; height: 581.806px;" height="582"></p><p><strong><span style="font-size: 10pt;">Fig 6. Setting a capture schedule.</span></strong></p><p><br></p><p>Upon saving, your Statistics Table is complete. To test, click the "Run now" button at the top of the page. The "Data" tab will then populate with the first set of data, where you can check if this seems correct.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjFiNWU3MWRhLWNlNDItNGM5NS1iZjAyLWQ1OTQ1ZTI4ZTNlMyJ9.oCjSiqXiwn1he_NFR7snX1g6l-XAgo1wioZYryh6uGo" class="fr-fic fr-fil fr-dib" width="1220" style="width: 1222px; height: 470.848px;" height="471"></p><p><strong><span style="font-size: 10pt;">Fig 7. Data tab after first run.</span></strong></p><p><br></p><p id="isPasted"><strong><span style="font-size: 14pt;">Reporting on Statistics Tables</span></strong></p><p>This data can then be reported on easily in the Reports area by running "SELECT * from CTTCSnapshot". The data is also formatted in a way which allows you to plot the data over time on a chart easily.</p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjljMjU5MWYxLTc0YzAtNDE1NS1hNTQ0LWY4NmUyYTA4NDUyNCJ9.c85blHXhzLBZ_ZrVYPsNfMngCtT4NM87a4LxAgjetxU" class="fr-fic fr-fil fr-dib" width="693" height="357"></p><p><strong><span style="font-size: 10pt;">Fig 8. Reporting on the table.</span></strong></p><p><br></p>
