The Custom Doc Criteria Setting Screen allows Administrators to create additional criteria fields to show on the Report Criteria Form.
Criteria Name - This should be all text - no spaces or symbols. The unique name to identify the Criteria
Criteria Control Type - This defines what type of control to show on the screen. Options are: TextBox or SearchBox
Criteria Data Type - This defines the data type for the answer. Similar to the Bookmark Data Type but restricted to the following:
Text - defined as nvarchar(max) bookmark data type - For text
Currency - defined as money - For currency
Numeric - defined as numeric bookmark data type - For numbers
Percentage - defined as percent - For percentages
Calendar No Time - defined as date bookmark data type - For dates with no time
Calendar With Time - defined as datetime - For dates with time
Administrators should have experience with T-SQL and test all queries in SSMS before adding them to the search box
Three queries are required:
Search SQL - The SQL that will return the list of items to show in the search box when the magnifying glass is clicked. This should return two columns. First an ID and second a description column. The ID column should have the same exact column name as the Criteria Name in the Document. For example, the CustomDocCreditorGroupID custom criteria is supposed to be used with a document criteria called CreditorGroupID. Therefore the Search SQL should return CreditorGroupID as the first column.
From Key To Display - The SQL that runs to convert the ID column to the description or name column. The SQL should return one row and one column that will contain the description or name of the record from the ID passed. The ID is passed via the {KEY} bookmark. For example, the CustomDocCreditorGroupID custom criteria has the following SQL:
select top 1 Name
from creditors
where creditorid = '{KEY}' and CreditorGroupIsParent = 1
From Display To Key - The SQL that runs to convert whatever the user types in the textbox of the search box into the ID. The bookmark '{DISPLAY}' would contain what the user typed. The SQL should use that bookmark to return one row and one column with the ID that best matches the description or name typed. The ID column name should match the Criteria Name in the Document. For example, the CustomDocCreditorGroupID custom criteria has the following SQL:
select top 1 CreditorID as CreditorGroupID
from creditors
where CreditorGroupIsParent = 1 and name like '{DISPLAY}%'
order by name
Notice that the SQL SELECT above aliases the CreditorID as CreditorGroupID so the column name matches the Criteria Name in the Document.
** if using multiple fields in the WHERE clause the {KEY} and {DISPLAY} bookmarks must be the first part of the WHERE clause
Example:
Search: select ClientID, WholeName from Clients where active = 1
From Key: select WholeName from Clients where clientid = '{KEY}'
From Display: select ClientID from Clients where wholename like '%{DISPLAY}%' and active = 1