Knowledgebase articles
- Welcome to the Knowledge Base
- Introduction
- Training
- Getting Started
- Preferences
- Activities
- Cases
- Importing Data
- Leads
- Marketing
- Introduction to Marketing
- Marketing Campaigns
- Mailing Lists
- Products
- Mailshots
- Upload Library
- Templates
- Event Management
- Compliance Records
- Force24
- Spotler Integration
- What is Spotler?
- Navigating your Spotler homepage
- GatorMail
- GatorLeads / Web Insights
- Tracking Code
- Setting up the Plugin
- Viewing Web Insights Data on your Form Layouts
- Domain Names and Online Activities
- Reporting incorrect Leads created through Web Insights
- Reporting on Web Insights data
- Using UTM Values
- Why aren’t Online Activities being created in the database?
- Why is GatorLeads recording online activities in a foreign language?
- GatorSurvey
- GatorWorkflow
- GatorPopup
- Opportunities
- Projects
- Integrations
- Mapping
- Electronic Signing Tools
- Creditsafe Integration
- Zapier
- Introduction to Zapier
- Available Triggers and Actions
- Linking your Workbooks Account to Zapier
- Setting up Zaps
- Posted Invoices to Xero Invoices
- Xero payments to Workbooks Tasks
- New Case to Google Drive folder
- New Case to Basecamp Project
- New Workbooks Case to JIRA Ticket
- Jira Issue to new Case
- 123FormBuilder Form Entry to Case
- Eventbrite Attendee to Sales Lead and Task
- Facebook Ad Leads to Sales Leads
- Wufoo Form Entry to Sales Lead
- Posted Credit Note to Task
- QuickBooks Online
- Survey Monkey responses to Tasks
- Multistep Zaps
- Email Integrations
- Email Dropbox
- Workbooks Exchange Server Sync
- Workbooks Outlook Connector
- RevenueGrid Intelligence and Engage
- Event & Webinar Integration Tools
- GoToWebinar
- ON24
- Microsoft Office
- Outreach
- Installation
- Outreach Authentication
- Sync People to Outreach Prospects
- Sync Organisations to Outreach Accounts
- Sync Workbooks Opportunities to Outreach
- Sync Tasks/Activities from Workbooks to Outreach
- Sync Outreach Sequences to Workbooks
- Sync Outreach Sequence States to Workbooks
- Sync Outreach Sequence Step Numbers to Workbooks
- Sync Prospects/Accounts/Opportunities from Outreach to Workbooks
- Sync Outreach Tasks/Calls/Meetings to Workbooks
- Scribe/Workbooks Connector
- RingCentral
- Auditing
- Comments
- People & Organisations
- Reporting
- Introduction to Reporting
- Using Reports
- Introduction to Charts
- Exporting Reports
- Advanced Reporting
- Report Snapshots
- Dashboards
- Transaction Documents
- Introduction to Transaction Documents
- Displaying & Adding Transaction Documents
- Copying Transaction Documents
- Transaction Documents Fields Help
- Transaction Documents Line Items Help
- Printing & Sending Transaction Documents
- Managing Transaction Document Currencies
- Managing Transaction Document Statuses
- Setting a Blank Default Currency on Transaction Documents
- Credit Notes
- Customer Orders
- Invoices
- Quotations
- Supplier Orders
- Contract Management
- Sagelink
- Introduction to Transaction Documents
- Configuration
- Introduction to System Administration
- Users & Security
- Database
- Accounting
- Email & Integration
- Customisation
- Creating & Modifying Picklists
- Theme
- Record Types
- Creating Custom Fields
- Report-based Custom Fields
- Linked Fields & Reference Fields
- Record Templates
- Form Layouts
- Customising relationships between parties
- Opportunity Stages
- Custom Records
- Sign In Customisation
- Automation
- Contact Support
- Releases & Roadmap
Reporting Tips
Last updated : 19th September 2024
This page is dedicated to providing calculated column formulae with some worked examples. This will enable you to insert these into your Reports and hopefully achieve the outcome you desire in your Summary views.
| Formula | What does it do? | Use Case | Worked Example |
|---|---|---|---|
| IF( ) | Test if conditions are true or false | I want to display the value True on the rows of the report that have Activities of type “Phone Call”.
|
IF(activity_type LIKE ‘Phone Call‘, ‘True’, ‘False’) |
| CASE | Compare a value to a series of values and return a specified value | I want to a column to show “Not Started” for any Case with a Status value of Open, “Finished” if Closed and anything else: “In Progress” | CASE status_name WHEN ‘Open’ THEN ‘Not Started’ WHEN ‘ Closed’ THEN ‘Finished’ ELSE ‘In Progress’ END |
| CASE | Compare multiple values to a series of values and return a specified value | I want to show a value of Escalated for any Case with a Status of Open as well as Assigned To being “Management”, any Case with a Status of “Bug” or “Enhancement” = Requires Engineering, any Case with a Status of “Closed” = Finished. Anything else will be “In Progress”. | CASE WHEN status_name = ‘Open’ AND assigned_to_name = ‘Management’ THEN ‘Escalated’ WHEN status_name = ‘Bug’ OR status_name = ‘Enhancement’ THEN ‘Requires engineering’ WHEN ‘ Closed’ THEN ‘Finished’ ELSE ‘In Progress’ END |
| count(DISTINCT PARENT(‘Field Name’) | Count the number of unique values in a Summary report | I want to count the unique number of Opportunities in the Details report. Due to joining my Opportunities to Line Items and Activities I am seeing multiple rows for the same Opportunity reference. | count(DISTINCT PARENT(‘Opportunity Reference’)). |
| GROUP_CONCAT(column_name SEPARATOR ‘, ‘) | Summarise a column into a string | I want to show all the Campaigns that a Person is a member of, separated by a comma. | GROUP_CONCAT( campaign_membership.campaign_name SEPARATOR ‘, ‘) |
| DATEDIFF(first_column_name, second_column_name) | Calculate the difference in days between two dates | I want to show how long it has been since my Activities were created. (the difference between when it was created and today) | DATEDIFF(CURDATE(), created_at) |