Kaizen 255 Building a Real-Time Operational Dashboard with Zoho CRM Queries
Hello Everyone,
Welcome back to another edition of the Kaizen series, where we uncover powerful ways to extend and customize Zoho CRM.
In the previous Query Kaizens, we explored how Queries can retrieve CRM data, invoke REST APIs, and even update CRM records. In Kaizen #250, we demonstrated how a Kiosk used a Query to identify the most suitable Field Engineer and automatically assign that engineer to a Service Request during a Blueprint transition.
In this Kaizen, we'll use a Query serializer to transform the raw Query response into enriched business data by calculating additional fields at runtime. This demonstrates that serializers can do much more than rename columns. They can derive new values from existing CRM data and present actionable insights that go beyond what a standard report typically provides.
Business Scenario
Zylker Home Services receives hundreds of installation, maintenance, and repair requests every week. The Service Manager reviews ongoing Service Requests to monitor work progress, identify overdue jobs, and ensure Field Engineers are assigned effectively.
While Zoho CRM reports can display the available data, the manager often needs additional operational insights that are not stored in CRM. For example:
- How many days has a request been overdue?
- Which requests should be treated as high priority?
- Has the service request breached its SLA?
- Does the request require immediate escalation?
- Is the target completion date falling on a weekend?
These insights are derived by evaluating multiple fields, rather than by simply displaying existing CRM fields.
Instead of exporting data or manually interpreting reports, the Service Manager wants a single dashboard that not only displays Service Request information but also computes these decision-making indicators in real time, enabling quicker and more informed operational decisions.
Solution Overview
The dashboard is powered by a Query that retrieves CRM data and a serializer that computes operational indicators dynamically for every execution.
Architecture Overview
Presentation Layer
A single Kiosk is embedded on the Service Manager's CRM Home page. The Kiosk displays a tabular operational dashboard containing all Service Requests.
Data Access Layer
The Kiosk is powered by a Module Query on the Service Applications module.
The Query retrieves live CRM data, while the serializer transforms and enriches the response before it reaches the Kiosk by:
- Renaming fields
- Computing overdue duration
- Deriving SLA status
- Determining request priority
- Identifying requests requiring escalation
- Flagging weekend target dates
- Replacing missing values with meaningful messages
Data Layer
The dashboard reads live data directly from the Service Applications module and its related Field Engineer records, ensuring managers always view the latest CRM data without synchronizations or exports.
Implementation
Step 1: Create the Query
Navigate to: Setup → Developer Space → Queries
Configure the Query using the following settings to retrieve Service Application records for the operational dashboard.
Source: Zoho CRM → Module
Module: Service Applications
Fields:
- Name
- Service Request Type
- Zone
- Target Completion Date
- Reason For Delay
- Field Engineer Name
- Field Engineer Status
- Field Engineer Specialization
- Field Engineer Phone
No additional filtering is applied so that both assigned and unassigned Service Requests are returned.
Serializer
The serializer calculates these additional fields dynamically whenever the Query executes. They are recalculated each time the Home page loads rather than being retrieved as stored CRM fields. The serializer not only renames fields but also enriches the response by calculating additional operational information.
It derives:
- Overdue By
- Priority
- SLA Status
- Escalation Required
- Weekend Warning
const today = new Date(); return result.map(record => { let overdueDays = null; let priority = "Not Available"; let slaStatus = "Unknown"; let escalationRequired = "Cannot Determine"; let weekendWarning = "N/A"; if (record.Target_Completion_Date) { const targetDate = new Date(record.Target_Completion_Date); overdueDays = Math.max( 0, Math.floor((today - targetDate) / (1000 * 60 * 60 * 24)) ); // Priority if (overdueDays >= 15) { priority = "🔴 Critical"; } else if (overdueDays >= 7) { priority = "🟠 High"; } else if (overdueDays > 0) { priority = "🟡 Medium"; } else { priority = "🟢 On Track"; } // SLA Status if (overdueDays === 0) { slaStatus = "🟢 Within SLA"; } else if (overdueDays <= 3) { slaStatus = "🟡 Approaching SLA"; } else { slaStatus = "🔴 SLA Breached"; } // Escalation Required escalationRequired = overdueDays > 7 ? "🔴 Escalate Immediately" : "No"; // Weekend Warning const day = targetDate.getDay(); weekendWarning = (day === 0 || day === 6) ? "⚠ Weekend Target" : "Working Day"; } return { "Service Request": record.Name, "Service Request Type": record.Service_Request_Type, "Zone": record.Zone, "Target Completion Date": record.Target_Completion_Date || "Date Not Provided", "Overdue By": overdueDays !== null ? (overdueDays > 0 ? overdueDays + " day(s)" : "Not Overdue") : "Date Not Provided", "SLA Status": slaStatus, "Priority": priority, "Escalation Required": escalationRequired, "Weekend Warning": weekendWarning, "Reason For Delay": record.Reason_For_Delay || "Not Provided", "Field Engineer Name": record["Field_Engineer.Name"]|| "Not Assigned", "Field Engineer Status": record["Field_Engineer.Status"]|| "Engineer Not assigned", "Field Engineer Specialization": record["Field_Engineer.Specialization"]|| "Engineer Not assigned", "Field Engineer Phone": record["Field_Engineer.Phone"] || "Phone Not Available" }; }); |
The following table summarizes the additional business insights derived by the serializer, describing how it is calculated.
Derived Field | How the Serializer Calculates It | Example Output |
Overdue By | Calculates the difference between the current date (today) and the Target Completion Date. If the request is not overdue, it displays Not Overdue. If the target completion date is unavailable, it displays Date Not Provided. | 12 days(s) Not OverdueDate |
Priority | Determines the priority based on the calculated Overdue By value. Requests overdue by 15 or more days are marked Critical, those overdue by 7–14 days are marked High, those overdue by 1–6 days are marked Medium, and requests that are not overdue are marked On Track. | 🔴 Critical 🟠 High 🟡 Medium 🟢 On Track |
SLA Status | Uses the calculated overdue duration to determine whether the request is Within SLA, Approaching SLA, or SLA Breached. If the target completion date is unavailable, the serializer returns Unknown. | 🟢 Within SLA 🟡 Approaching SLA 🔴 SLA Breached |
Escalation Required | Checks whether the request has been overdue for more than 7 days. If so, the serializer flags it for escalation; otherwise, it indicates that escalation is not required. | 🔴 Escalate Immediately |
Weekend Warning | Determines the day of the week from the Target Completion Date using getDay(). If the date falls on Saturday or Sunday, the serializer displays a warning; otherwise, it indicates a normal working day. | ⚠ Weekend Target Working Day |
Null Value Handling | Replaces missing values with meaningful messages. | Not Provided Phone Not Available. |
Step 2: Associate Queries with Kiosks
Navigate to Setup → Customization → Kiosk Studio and create a Kiosk.
- Add the screen state.
- Choose Get data via Queries as the data element.
- Select the corresponding Query.
- Configure the table columns using the serializer output.
- Save and publish the Kiosk.
Step3: Associate the Kiosk to Manager's Home Page
Navigate to Setup -> Customization -> Customize CRM Home.
- Edit the Service Manager Home dashboard.
- Click Kiosk from the Dashboard Components.
- Drag and Drop the Kiosk created in Step2. Arrange the Kiosk according to your preferred layout.
- Save the dashboard.
Service Manager Home Page
The Service Manager Home page displaying the Service Request Summary dashboard powered by Zoho CRM Queries and serializers.
Queries are more than a mechanism for retrieving CRM records. Combined with serializers, they become a lightweight presentation layer capable of enriching, transforming, and deriving business insights from live CRM data before it is displayed to users.
In this Kaizen, we built a real-time operational dashboard powered by a single Query. Rather than simply displaying stored CRM fields, the serializer calculated metrics providing Service Managers with actionable information the moment they log into CRM.
This demonstrates that Queries can go beyond traditional reporting by not only retrieving data but also preparing it for consumption in a way that supports faster and more informed operational decision-making.
We hope you found this post useful.
We would love to hear from you! Write to us at support@zohocrm.com or let us know in the comments section.
Happy Querying!
Previous Kaizen Post : Kaizen #254- Building a Temporal Lead Score Decay System in Zoho CRM| Kaizen Directory
Related Links:
Access your files securely from anywhere
Zoho FSM Video Tutorials
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Athira Rajan
Sticky Posts
Announcing the new SKILL.md for Zoho CRM and the updated OAS repository!
We are introducing a new zoho-crm skill to make working with Zoho CRM Developer tools (like APIs, functions, widgets, client scripts, queries etc) easier and faster, with the help of AI in your preferred AI harness like Claude Code, Codex, Cursor, VSCodeKaizen #261 : Using Zoho CRM Skill to generate complex COQL queries
Hello everyone, In the previous Kaizen posts, we introduced the zoho-crm SKILL.md and discussed how it can help you when you are working with Zoho CRM APIs and Client Script. In this Kaizen, we will focus on using the Zoho CRM `SKILL.md` with GitHub CopilotKaizen #256 - Build an Arrival Readiness Web Tab in Zoho CRM
Hi everyone! Welcome back to the Kaizen series! In the post, we discuss a use case in hospitality industry: how an Arrival Readiness web tab widget can be used to let reception staff identify and resolve issues before arrival of guests. Use case In theKaizen #198: Using Client Script for Custom Validation in Blueprint
Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.Kaizen #226: Using ZRC in Client Script
Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In this
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Billing Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
How to Manage Two GST Numbers / Two Companies in a Single Zoho Inventory Organization?
Hi Team, We currently have two separate legal entities: A Proprietorship Firm with its own GSTIN A Private Limited Company with a different GSTIN However, operationally, both companies deal with the same products, and we would like to manage the physical10/22 15時〜 オンラインもくもく会のお知らせ | Zoho ワークアウト
ユーザーの皆さま、こんにちは。コミュニティグループの中野です。 10月22日（木）開催の「Zoho ワークアウト」をご案内します。 Zoho ワークアウトは、参加者それぞれが取り組みたいテーマを持ち寄り、ユーザー同士で交流しながら、 設定・検証・運用改善を進めるオンラインの「もくもく会」です。 「試してみたい設定があるけれど、なかなか時間を取れない」 「一人で進めていると、途中で手が止まってしまう」 「他のユーザーの活用方法や工夫を聞いてみたい」 そんな方は、Zoho ワークアウトで一緒に取り組んでみませんか？Reporting tags associated to Project in Zoho books?
Hi, I know you can add reporting tags to contact, articles and transactions. Is it possible to add reporting tags to Project. We Zoho reports I would be able to generate reports by tags. Sometimes tags for a contact is different for projects (ie ProjectsReporting Tags in Profit and Loss Reports Analysis
Hello Community and Zoho Books Product Team, I would like to highlight a limitation I encountered with Reporting Tags in Zoho Books. Reporting Tags Not Applying to Automated Inventory Journal Postings from Product Invoices. For inventory product invoices,Spam Quarantine: Manage suspicious emails in Zoho Mail
When an email is quarantined by spam processing, it need not always be blocked right away. Some emails may be marked incorrectly, or may need to be reviewed before a final decision is made. The Spam Quarantine feature in the Zoho Mail Admin Console givesWhat is the difference between "Reply" and "Public Comment" in a ticket?
Hey, my company is thinking about switching to Zoho Desk but we do not understand the difference between the "Reply" and the "Public Comment" feature. When should we use which? And why? What is the difference between them? Thank you for your help! KindestComments Vs. Replies
I'm curious as to the difference between a "Reply" and a "Comment" on a ticket. It appears that "Replies" are what's used to determine response time SLA's and there are also used to automatically re-open tickets. I'm just trying to understand the key differences so I can educate both our clientele and our back-end users on which function/feature to use to better improve the ticket lifecycle. If anyone has any insight it would be appreciated. Thanks!What's New in Zoho Inventory | July & August 2026
Hello users, The July and August 2026 releases bring a range of new features and improvements across the product. From the Nigeria Edition and Super Admin enhancements to Sri Lanka IRD-compliant invoicing, Loyalty Program Management, and improvementsForms data export bug
The export csv with a password has changed for the worst. It is less secure by not allowing complex passwords. Just last week and the last year (at least) I was able to use a complex, secure password. Export CSV moreover doesn't even work if I use a simpleFax and email questions
Does Zoho offer faxing capability? If so, how to do it? And, why is has my email inbox stopped in 2019? There are no more emails since then. Also if I delete all of those emails, will they delete it from my gmail account (I'm linked to a gmail email address).Create a single record from multiple images with ICR
Hello all, Zia's Intelligent Character Recognition can now create a single record from multiple images based on the configured ICR rule. Users can upload up to three images at once, such as an ID, a passport, and a business card, and Zia will extractHow to present an Analytic on a CRM dashboard and filter it on loginuser id
How to present an Analytic on a CRM dashboard and filter it on loginuser id? I want to display an Analytics pivot table (as a widget) on a CRM dashboard and have the pivot filtered on the loginuser. I want additional user-filters available, such as date,CNIL - Suivi de Pixel
Bonjour à tous, À la suite de la nouvelle recommandation de la CNIL sur les pixels de suivi dans les e-mails, savez-vous si Zoho Campaigns permet : de conditionner le suivi des ouvertures au consentement de chaque contact ; de proposer un lien permettantLive connection to DS files in Zoho Creator / ChatGPT
As an idea, we are using ChatGPT to build with Zoho Creator (outside of Zia), which is works brilliantly to assist write deluge, provide test suggesitons etc. Currently we are uploading the DS files which are very point in time, it would be great to haveSetting default From address when replying to request
At the moment, if I want to reply to a request, the From field has three options, company@zohosupport.com, support@company.zohosupport.com, and support@company.com. The first two are really internal address that should never be seen by the customer andZoho Payroll: Product Updates - Q3, FY2026
Over the past quarter, Zoho Payroll has added several updates to make payroll easier to process, configure, and manage. The most notable improvements cover bank integrations, reimbursement support, payslip enhancements, employee exit documentation, payrollConvert OLM to PST on Mac and Windows Using Free Methods
When your Outlook for Mac data is stored in an OLM file and you want to use it with Outlook for Windows, the simplest approach to transfer your emails and other mailbox information is to convert the OLM file to a PST file. OLM is primarily associatedAdd Payment Information (like Bank details)
I am using the Free plan of Zoho Books. I need to add the bank details/ UPI details on the invoice. How can I do that. I have added the details in the Bank Section.Placeholders for Multi-Level Discount
I am using custom templates for Invoices and all other modules. Since i have activated multi-level discount (an early-access feature from zoho) "discount at line-item and transaction level", I need placeholders for using them in my custom templates. WithRunning Balance
Please add automatically Running balance in ledger view. Taking lot of time, every time to add manuallyAccounting on the Go Series-47: Effortless GSTIN Management- Auto Populate TaxPayer Details in Zoho Books Mobile App
Hello everyone, Welcome back! Today, we're focusing on a feature specifically designed for our Indian users in the Zoho Books-Indian edition, particularly those who deal with GST compliance regularly. We understand the importance of accurate and efficientURGENT: Stalled DC Migration >72 Hours - Records Locked [Ticket #: 167813877
The DC migration was started on 1st October at 6 pm and we were informed migration period as 48 hours but even after more than 72 hours, the migration seems still in progress. We sent two emails to check the status but no responses. We need the migrationZoho Webinar - Sharing System Audio (NOT AVAILABLE)
Hi, We are having a serious problem with Zoho Webinar. In the webinars we run, we very often share the audio from a video we are streaming directly from YouTube or other applications. Until recently we were using Zoom, but as we use other Zoho applicationsCash Recipt
You can not receive cash more than 10000 from one party in a day and two lakh in a year. In statement you can't differentiate cash or bank receipt. zoho does not support thisNeed to set workflow or journey wait time (time delay) in minutes, not hours
Minimum wait time for both Campaigns workflows and Marketing Automation journeys is one hour. I need one or the other to be set to several minutes (fraction of the hour). I tried to solve this by entering a fraction but the wait time data type is an integerConcern Regarding Stock Validation in Inventory Management
Hi Zoho Team, We would like to highlight a concern regarding stock validation in the inventory system. As per standard inventory management practices, if the stock level is zero, the system should not allow users to create a shipment, packing slip, orChange from OST to PST on Windows 10/11 Using Free Method
There is an advantage in converting OST files to PST format if you need to access your Outlook data on a different computer, want to keep a local copy, or are trying to recover items from an old Outlook profile. The good point is that users of WindowsImportant: Whitelist new proxy outgoing IPs for IN, SA, and UAE data centers
Hello everyone, We're expanding the pool of proxy outgoing IP addresses for our IN, SA, and UAE data centers by introducing new IPs alongside the existing ones. This helps ensure high availability, optimal load distribution, and redundancy for outgoingZoho Flow Custom Function Issue
I have a use case where I will be needing to post data to a wordpress site. Specifically to a custom post type with a bunch of ACF fields. I have setup trigger event correctly for Tookan and can receive data from the webhook. In order to post the dataHow to Open .mbox file in Gmail with Attachments?
Gmail offer users to backup INBOX folder in .mbox file format via Google Takeout Feature. Howver there is no such option to Restore Gmail MBOX. Thus I would like to suggest you to choose an alternate approach i.e. MBOX to Gmail Wizard. This utility will open .mbox file in Gmail with attachments. Steps to open MBOX file in Gmail are; Run MBOX to Gmail Wizard Click Add File and add .mbox file. Enter your Gmail login credentials. Click Convert button. Finished! This is how you can open MBOX file inCustom Validation for On Duty
Hi Team, We would like to have custom validation support for the On Duty form in the Attendance module. We need to apply some restrictions, such as: Limit the number of On Duty requests per month. Restrict On Duty requests on selected days. Allow employeesIntegrating FaceBook Ads into MarketingHub
I would like to track the clicks from a FB Ad campaign into my website via marketinghub. I cannot currently add a MH form directly to my FB page, as I receive an error telling me I need at least 2000 subscribers on FB to be allows to do that (yikes!) What is the best way to set up MH so I can track which FB are converting well? I know I can reach the FB reports directly, but these do not track conversions from the MH forms on my website. Best regards, JoeMigration from GoDaddy to Zohomail
Hi All, I am trying to migrate all the emails from Go Daddy to zohomail account using the migration tool. i tried with imap method, its not working. when i try it with pop method, then only the emails from the go daddy account inbox ( not other folders), is getting transferred, how can i transfer all the folders in the email account from go daddy to zoho. and also the emails which are coming from go daddy are like new unread emails ( is there any where i can differentiate from read and unread emails)How do I migrate from Office 365 to Zoho Mail?
Check out Advik Email Migration Wizard, this software is specially developed to move mailboxes from Office 365 to Zoho Webmail. In addition you can migrate from Gmail, Yahoo, Rediffmail and 80+ webmail servers to ZOHO MAIL. Isn't it amazing? This is an all in one email migration solution. Steps to export emails from Office 365 to Zoho Webmail are as follows; Run Advik Email Migration Tool in your system. Select Office 365 as source and enter its login credentials. Select mailbox folders and chooseHow to Backup Zoho to PST?
I'm looking for a simple way to backup Zoho Mail emails to PST format. I tried the IMAP method with Outlook, but it seems slow and complicated for large mailboxes. I need a solution that can: Export Zoho emails to PST Preserve attachments and folder hierarchyShopify connector – product cost ("Cost per item") not synced, so COGS/gross margin can't be calculated
Hi Zoho Analytics team, I use the native Shopify connector in Zoho Analytics (cloud) to build reporting dashboards for Shopify stores. As far as I can tell, the connector does not sync product cost, so there's no way to calculate Cost of Goods Sold (COGS),Zoho Sprints - Q3 Updates for 2026
Another quarter, another set of productive features. This time our focus was mainly to simplify in-app navigations and control all your work related activities within Zoho Sprints. We delivered 7 major features and 9 enhancements, which include Zoho SprintsThe Social Wall: August 2024
Hi everyone, We're thrilled to bring you the latest updates, features, and improvements to help you get the most out of Zoho Social. Let's see what was new for us at Social in August. Post documents on LinkedIn You can now reach a lot more people andFree PST Password Recovery Tool
Recoveryfix PST Password Recovery is a specialized standalone utility designed to instantly unlock encrypted Microsoft Outlook PST files . It provides an efficient way to recover, reset, or entirely remove passwords when access credentials are lost orCash Flow Statement : Direct Method
I can not see how can I see the cash flow statement using direct method. AS 3 recommends two formats of cash flow statement. The indirect method is there on Zoho but there report in Direct method. How can I see that by Direct method.Next Page