Difference between revisions of "Using Wildcard Criteria in Reports"
From SmartWiki
| (13 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| + | {{SeqReportPageHeader}} | ||
| + | |||
A [[Wildcard|wildcard]] character is used in criteria to replace one or more unknown characters in the field. For example, if you need to display only names beginning with the letter “A”, then you would need to use a [[Wildcard|wildcard]]. | A [[Wildcard|wildcard]] character is used in criteria to replace one or more unknown characters in the field. For example, if you need to display only names beginning with the letter “A”, then you would need to use a [[Wildcard|wildcard]]. | ||
* '''Like''' - the "like" operator is used to retrieve values for a field, where a comparison is made between the value specified, and the value for that field in the database. For example: '''Like 'A%''''. The percent sign - % matches any number of characters. It can be used in any position in the character string. Like ''''miss%ga''''. | * '''Like''' - the "like" operator is used to retrieve values for a field, where a comparison is made between the value specified, and the value for that field in the database. For example: '''Like 'A%''''. The percent sign - % matches any number of characters. It can be used in any position in the character string. Like ''''miss%ga''''. | ||
* '''_''' matches a single character. It can be used in any position in the character string. Like 'N_W%’ | * '''_''' matches a single character. It can be used in any position in the character string. Like 'N_W%’ | ||
| − | 1. Display the '''Report Builder''' window. | + | 1. Display the '''[[Report Builder]]''' window. |
| − | 2. Set the '''criteria''' for the first column to be '''Like | + | 2. Set the '''criteria''' for the first column to be '''Like <nowiki>'A%'</nowiki>'''. |
[[Image:Reps35.png]] | [[Image:Reps35.png]] | ||
| Line 24: | Line 26: | ||
<pre>LIKE concat('%',???,'%')</pre> | <pre>LIKE concat('%',???,'%')</pre> | ||
| − | + | :{| border="1" cellpadding="5" | |
| − | + | |+ | |
| − | + | !Field!!Criteria!!Meaning | |
| − | + | |+ | |
| − | + | |Last Name||Like '%son'||Last names ending in the string "son" | |
| − | + | |+ | |
| − | + | |Last Name||Not Like '%son'||All last names, '''except''' those ending in the string "son" | |
| − | + | |+ | |
| − | + | |Title||Like 'VP%'||Titles beginning with the string "VP" | |
| − | + | |+ | |
| − | + | |Title||='President'||Titles exactly matching the word "President" | |
| + | |+ | ||
| + | |City||Like 'N_w%'||Will return "New York", but not "Norwalk" | ||
| + | |} | ||
| − | |||
| + | {{PrevNextStart}} [[Setting Text Criteria – Logical Operators in a Report]] | ||
| + | {{PrevNextMid}} [[Using Sounds Like Criteria in Reports]] {{PrevNextEnd}} | ||
| − | [[Category:Reports]] | + | [[Category:Reports]][[Category:Criteria]] |
Latest revision as of 12:12, 30 August 2013
Click here to watch a video on SmartSimple's integrated reporting subsystem.
A wildcard character is used in criteria to replace one or more unknown characters in the field. For example, if you need to display only names beginning with the letter “A”, then you would need to use a wildcard.
- Like - the "like" operator is used to retrieve values for a field, where a comparison is made between the value specified, and the value for that field in the database. For example: Like 'A%'. The percent sign - % matches any number of characters. It can be used in any position in the character string. Like 'miss%ga'.
- _ matches a single character. It can be used in any position in the character string. Like 'N_W%’
1. Display the Report Builder window.
2. Set the criteria for the first column to be Like 'A%'.
3. Click the Build Query button.
4. Click the Save button.
5. Click the Preview button.
Two records are displayed matching the criteria.
6. Try each of the following criteria and note the results.
7. You can also try using the following syntax in the Criteria box in the report builder.
LIKE concat('%',???,'%')
Field Criteria Meaning Last Name Like '%son' Last names ending in the string "son" Last Name Not Like '%son' All last names, except those ending in the string "son" Title Like 'VP%' Titles beginning with the string "VP" Title ='President' Titles exactly matching the word "President" City Like 'N_w%' Will return "New York", but not "Norwalk"
