Custom Report Filter Conditions

Understanding and Setting Filter Conditions on Custom Reports

Setting a Filter Default and a Filter Condition allows you to preset the filter for a particular column, and specify what data you want your custom report to return.  An example would be to set a filter default for a date column to return data only from 'This Month'.  

Setting Filter defaults and conditions is still quite complex in Centerbase custom reports.  The section below will provide some insight into using these for various filter types. The following filters will be  covered this this article

• Date Filters
• Lookup Filters
• Radio Filters

Remember:  setting a filter only works if the KB for that SP specifies that column is available as a filter and provides the SQL param name!

 

Filter Example

Date Filters

After adding a date column to your custom report, you may then check the Filterable checkbox, fill in the appropriate options specified in the SQL procedure help article, then paste the following text into the date column Filter Default textbox exactly as shown below.  

{"DateSettings":{"tabType":0,"period":"0","simpleValue":"10"}}

 

• tabtype: 0 = Simple Date, 1 = advanced dated, 3 = date range
• period: 0 = within, 2 = after, 4 = on or after, 3 = before, 5 = on or before
• simpleValue: Set values below when tab type set to 0

Various examples and parameters are shown below.

Using 'simpleValue' date filter parameter values

This Hour = 1
Today = 2
This Week = 3
This Month = 4
This Quarter = 5
This Year = 6

Last Hour = 7
Yesterday  = 8
Last Week = 9
Last Month = 10
Last Quarter = 11
Last Year = 12

First Quarter = 13
Second Quarter =14 
Third Quarter = 15
Fourth Quarter = 16

Date Range = 17     // Specific Date Range - manually entered
All = 18                // Wide date range from 1-1-1900 to Max Date
Wildcard = 19       // A specific date, month or year.

Next Hour = 20
Next Day = 21
Next Week = 22
Next Month = 23
Next Quarter = 24
Next Year = 25

Past =  26                         //All past dates not counting today
Future =  27                     // All future dates not counting today
Today And Future = 28     // Today and all future dates
Today And Past = 29

Example Usage:

{"DateSettings":{"tabType":0,"period":"0","simpleValue":"6"}}

Would specify to return all records with a column date of THIS YEAR

 

{"DateSettings":{"tabType":3,"period":"0","StartDate": "08/07/2018", "08/09/2018"}}

Would specify to return all records with a column date of Within 8/7-8/9

 

{"DateSettings":{"tabType":0,"period":"0","simpleValue":"4"}}

Would return values within this month.

 

Advanced Values

advancedPeriod: 0 = Past, 1 = next
advancedUnits: 0 = minutes, 1 = hours, 2 = days, 3 = weeks, 4 = months, 5 = quarters, 6 = years
advancedValue:  specify the value, ie, number of days/weeks/months, etc

Example Usage:

{"DateSettings": { "tabType": 1, "period": "0", "advancedPeriod": "0", "advancedValue": "30", "advancedUnits": "3" }}

Would return within the past 30 weeks

 

Lookup Filters

After setting a filter for a lookup column and setting the appropriate SQL Param Name, you simply need to enter the Item Type for the Lookup field in the Filter Conditions text box. 

Example for Timekeeper lookup filter (Timekeepers are Item type User = 1)

All Item Types are listed below with their corresponding numbers.

 

1	Users
2	Activity
3	Contacts
4	User Contacts
5	Groups
6	Opportunities
7	E-Mail Addresses
14	Notes
15	Calls
16	EMail
17	Documents
18	History
19	Merge Templates
30	Bills
31	Billing Codes
32	Billing Entries
33	Payment Distributions
35	Trust Transactions
36	Candidates
37	Employment Histories
38	Employers
39	Job Orders
40	Keywords
41	Matching Candidates
45	Projects
46	LinkedIn Locations
47	Credits
48	Payments
50	Credit Distributions
51	Refunds
52	Refund Distributions
53	Addresses
55	Vendors
56	Vendor Bills
57	Vendor Bill Payments
58	Phone Numbers
60	Vendor Bill Payment Distributions
61	Checks
63	Charts
69	Consolidated Bills
71	Bank Deposits
72	Trust Bank Transfers
100	Call Lists
101	Call List Members
1001	Matters
1002	Conflict Checks
1010	Potential New Clients
1014	Document Codes
1015	Referrals
1017	Conflict Votes
1021	Marketing Campaigns
1023	Mail

 

Radio Filters

Radio filters allow the user to select a single option amount several choices.  This filters requires setting both a Filter Default and the Filter Conditions.  The format of this filter is fairly self explanatory.  You simply create a Filter condition in the following format, and then place the default value in the Filter Default text box.

[Option number]:[Option name]|[Next Number]:[Next Option]

example:  '0:Payments|1:Applied Credits'

These option names are specific, and are defined in the stored procedure help article, filter section.

Hidden Filter Types

These are special filters used on specific reports.  Follow the directions on the specific stored procedure help article.