<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><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><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><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>- Date Filters</strong></p><p><strong>- Text/Number Filters</strong></p><p><strong>- Special Characters</strong></p><p><br></p><p><br></p><p>When linking to a report you can include filters as URI parameters.</p><p><br></p><p><strong><span style="font-size: 14pt;">Date Filters</span></strong></p><p>When filtering on a date you can use the following parameters:</p><ul><li>reportingperiod : a number corresponding to the periods in the dropdown e.g. 0 for today</li><li>reportingperiodstartdate: a start date for the filter</li><li>reportingperiodenddate: an end date for the filter</li><li>reportingperioddatefield: the name of the date field to filter on if not filtering on all</li></ul><p>Reporting periods that can be used:</p><ul><li>case 0: //today</li><li>case 1: //yesterday</li><li>case 2: //last week</li><li>case 3: //last month</li><li>case 4: //last 3 months</li><li>case 5: //last 12 months</li><li>case 6: //all</li><li>case 7: //custom period</li><li>case 8: //this week</li><li>case 9: //this month</li><li>case 10: //last 3 months (inc this month)</li><li>case 11: //last 12 months (inc this month)</li><li>case 12: //Tomorrow</li><li>case 13: //next week</li><li>case 14: //next month</li><li>case 15: //next 3 months inc</li><li>case 16: //next 3 months</li><li>case 17: //next 12 months inc</li><li>case 18: //next 12 months</li><li>case 19: //this quarter</li><li>case 20: //last quarter</li><li>case 21: //next quarter</li><li>case 22: //next 6 months inc</li><li>case 23: //next 6 months</li><li>case 24: //last 6 months inc</li><li>case 25: //last 6 months</li><li>case 100: //this year</li></ul><p><span style="font-size: 11pt;"><span style="color: rgb(209, 72, 65);"><em><strong>NOTE: </strong></em><em><strong>You must change the URL from 'reports?' to 'report?' If this is not changed, the capitalisation will disappear from the URI parameters, which means the filters will not work.</strong></em></span></span></p><p><br></p><p><strong>Example:</strong> https://{yourhalo}.com/report?id=63&reportingperiod=7&reportingperioddatefield=dateoccured&reportingperiodstartdate=2024-04-01&reportingperiodenddate=2024-04-30</p><p><br></p><p>The above example uses the reportingperiod 7 (Case 7) which is used when specifying a custom period. The start and end dates have also been specified. The "report?id=" section of the URL, is referring to the unique ID of the report. The "&" sign symbolises the start of the next URI Parameter to include when the web page loads. </p><p><br></p><p><strong><span style="font-size: 14pt;">Text/Number Filters</span></strong></p><p>When filtering on a text or number field, you can include a filter as a URI Parameter. Appending the above example that uses a date filter, I can also include a user or agent filter:</p><p><br></p><p>The Column Name given on the report is the filtervalue that you have to use .</p><p><strong>i.e. </strong>https://{yourhalo}.com/report?id=122&reportingperiod=7&reportingperioddatefield=dateoccured&reportingperiodstartdate=2023-04-01&reportingperiodenddate=2024-04-30&filterfield=Assigned%20To&filtervalue=aidan%20kelly</p><p><br></p><p><em><strong>Note:</strong></em><strong><em> If the name of the field or the value contains a non-alphanumeric character e.g. a space or a slash, it will need to be URI encoded e.g. Halo User/Agent becomes Halo%20User%2FAgent%20</em></strong></p><p><br></p><p>When filtering on a text or number field you can include a filter of the format Filter_FilterType_FieldName=Filtervalue</p><p>e.g. to filter on users who's name starts with Halo you would add Filter_StartsWith_Username=Halo</p><p><br></p><p>https://{yourhalo}.com/report?id=122&reportingperiod=7&reportingperioddatefield=dateoccured&reportingperiodstartdate=2023-04-01&reportingperiodenddate=2024-04-30&mainview=reportgroup&Filter_StartsWith_username=Ben</p><p><br></p><p><strong><span style="font-size: 12pt;">The available filter type options are as follows:</span></strong></p><p><br></p><p><strong>Text:</strong></p><ul><li>Includes</li><li>DoesNotInclude</li><li>StartsWith</li><li>DoesNotStartWith</li><li>EndsWith</li><li>DoesNotEndWith</li><li>Contains</li><li>DoesNotContain</li><li>IsBlank</li><li>IsNotBlank</li></ul><p><strong>Number:</strong></p><ul><li>GreaterThan</li><li>LessThan</li><li>EqualTo</li><li>GreaterThanOrEqualTo</li><li>LessThanOrEqualTo</li><li>NotGreaterThan</li><li>NotLessThan</li><li>NotEqualTo</li><li>NotGreaterThanOrEqualTo</li><li>NotLessThanOrEqualTo</li></ul><p><br></p><p>This: /report?id=122&reportingperiod=7&reportingperioddatefield=dateoccured&reportingperiodstartdate=2023-04-01&reportingperiodenddate=2024-04-30&mainview=reportgroup&Filter_StartsWith_Assigned%20To=Aidan</p><p><br></p><p>Notice that the column name in the report shown below, is the filter that I use in the uri parameters. So the column name is what we input ("Assigned%20To") not the name in the database schema.</p><p><br></p><p>Entering the above URL produced this result:</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjhiZTg5NzM1LWE5ZDgtNGZlZi04YjBlLTA3OTgzMTc4OGNhNiJ9.ll1uv7aMbPm20CexcVmIleb-R1Jpaof9BRIoyN3pHQI" class="fr-fic fr-fil fr-dib" width="973" style="width: 975px; height: 373.949px;" height="374"></p><p><strong><span style="font-size: 10pt;">Fig 1. Example report</span></strong></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjAxNTNjYzVhLWQ3ZDAtNGQ0Yi05M2JhLWY4YzM5M2Y4ZmQ2YiJ9.vrReX9MntTmeieNCJAGPYnjoZJ6yTCu38bzY3Y1Cxg4" class="fr-fic fr-fil fr-dib" width="983" style="width: 985px; height: 359.039px;" height="359"></p><p><strong><span style="font-size: 10pt;">Fig 2. Result of the example filter in this report</span></strong></p><p><br></p><p><strong><span style="font-size: 14pt;">Special Characters</span></strong></p><p>Additionally if there are certain characters you are trying to use when searching, it is important to recognise that SQL may treat certain parameters in a specific way. Square brackets are wildcard characters in SQL and therefore need to be searched for differently when looking for the literal character. You would have to type the relevant character twice to ensure it is treated correctly in the search. See the below for the way to handle this in different filters:</p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6IjdhZjNmYmUzLTI2N2ItNDVlNS1hYTI5LTg1ZTc5ODBhYWYyMiJ9.pLngfmNU3zwfaQuPGNuJrHgMwahMq6rW31gwABCPtnI" width="637" height="313" style="max-width: 100%; height: 313.067px; text-align: left; width: 639px;" class="fr-fil fr-dib"></p><p><strong><span style="font-size: 10pt;">Fig 3. Example of filtering a special character</span></strong></p><p><br></p><p><img src="https://halo.haloservicedesk.com/api/attachment/image?token=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpZCI6ImM4N2RmMzFhLTE1MmUtNDc0Yy1iNDMxLWUxOTYwM2ZjMWM5YyJ9.M8YONCqMY7wgLSW3PiIp2rb9WLzQqyyOA0vM33-478U" width="534" height="263" style="max-width: 100%; height: 263.127px; text-align: left; width: 536px;" class="fr-fil fr-dib"></p><p><strong><span style="font-size: 10pt;">Fig 4. Another example of filtering a special character</span></strong></p><p><br></p><p>As shown in the two examples, the reports URI parameters are populated with double brackets, which makes the "Ends with" or "Starts with" inputs correctly identify the corresponding rows.</p>
