Solved: building custom timesheets with Zoho Analytics based on Zoho Sprint time tracking data structure

Solved: building custom timesheets with Zoho Analytics based on Zoho Sprint time tracking data structure

If you're using Zoho Sprints for agile projects, you're tracking time in it, and you want to use the data for performance billing, this tip is for you.

Three steps:

1. use Zoho Analytics.
Either bundle it with Zoho One or Zoho CRMPlus or use it as a standalone instance.
Besides reporting and dashboard functions, you get direct access to the data with the help of Cloud-SQL - to almost all Zoho applications and of course also to Zoho Sprints.

2. create a workspace in Analytics and connect to Zoho Sprints

3. after the initial data sync is complete, create a query view using the following SQL:

  1. SELECT
  2. date(substr(t."Log Date", 1, 10)) "Log Date",
  3. t."Description" "Description",
  4. t."Log Time in Minutes" "Log Time in Minutes",
  5. t."Log Time in Hours" "Log Time in Hours",
  6. t."Last Updated Time" "Last Updated Time",
  7. t."Billable Type" "Billable Type",
  8. ou."User Name" "Owner",
  9. au."User Name" "Approved By",
  10. p."Project Name" 'Project Name',
  11. s."Sprint Name" 'Sprint Name',
  12. s."Sprint Type" 'Sprint Type',
  13. i."Item No" 'Item No',
  14. i."Item Name" 'Item Name',
  15. i."Item Type Name" 'Item Type Name',
  16. i."Priority Name" 'Item Priority Name',
  17. e."Epic Name" 'Epic Name',
  18. e."Epic Type" 'Epic Type'
  19. FROM  "Timesheets (Zoho Sprints)" t
  20. LEFT JOIN "Users (Zoho Sprints)" au ON (au."ZSUser ID"  = t."Approved By") 
  21. LEFT JOIN "Users (Zoho Sprints)" ou ON (ou."ZSUser ID"  = t."Owner ID") 
  22. LEFT JOIN "Items (Zoho Sprints)" i ON (i."Item ID"  = t."Item ID") 
  23. LEFT JOIN "Sprints (Zoho Sprints)" s ON (s."Sprint ID"  = i."Sprint ID") 
  24. LEFT JOIN "Epics (Zoho Sprints)" e ON (e."Epic ID"  = i."Epic ID") 
  25. LEFT JOIN "Projects (Zoho Sprints)" p ON (p."Project ID"  = i."Project ID")  
  26. ORDER BY t."Log Date" 

At CEYOND, we use Zoho Analytics in close to all Zoho projects - starting from initial data import to ongoing automated reports and dashboards. We use Zoho Sprints in all our customer projects. We work according to Scrum and map our requirements, goals and activities in the form of Epics, Stories and Tasks.


      Zoho Campaigns Resources


        • Desk Community Learning Series


        • Digest


        • Functions


        • Meetups


        • Kbase


        • Resources


        • Glossary


        • Desk Marketplace


        • MVP Corner


        • Word of the Day


        • Ask the Experts


          • Sticky Posts

          • Tip #28 - Plan less and deliver more using WIP limit in Zoho Sprints

            Hello, It's been a while since we met with a quick, interesting tip. As the saying goes, "Too much of anything is good for nothing", today the focus is on delivering your outcomes with the right amount of planning. Your plan should be practical, calculative, and achievable for driving a qualitative success.    Laura's plan   Laura has a habit of planning her project deliverables before assigning work to her team-mates. Once the plan is finalized, she schedules a general meeting with her team and
          • Tip # 3- Working on the Scrum board

            Continuing from our Tip #2 on leading to a sprint, let's see how to manage the work items on the Scrum board.  Once you start the sprint your work items are automatically displayed on the Scrum board where you will actually manage the work items. It is a snapshot of the backlog items identified for the current sprint.    The layout of the Scrum board Simply put, the scrum board is just like a physical board with sticky notes on which the work items of the active sprint are displayed.  The scrum board
          • Tip#2- Leading you to a Sprint

            Product Backlog After the user stories are written and finalized, they are sorted to create the Product Backlog for the project during the Backlog Grooming meeting. This is a master list of all the work items that have been identified for the project and sorted by priority. Requirements are not constant during this period.The Product Backlog is dynamic and is an ongoing process. Every user story in the Product Backlog is customer centric. The Product Backlog includes: User centric stories based on
          • Tip #1- Why swimlanes?

            Lanes define a clear path to reach your destination. In Agile, swimlane is one such concept that sets a simple and clear process of the work that you do.   The concept of swimlane can be related to the pool, where the swimmers gather in their respective lanes to start off. Similar to the pool, work items are grouped in different categories. Each category is referred as a lane and is displayed in horizontal format. Swimlanes are effective in categorizing the work items with respective to specific

          Zoho CRM Plus Resources

            Zoho Books Resources


              Zoho Subscriptions Resources

                Zoho Projects Resources


                  Zoho Sprints Resources


                    Zoho Orchestly Resources


                      Zoho Creator Resources


                        Zoho WorkDrive Resources



                          Zoho CRM Resources

                          • CRM Community Learning Series

                            CRM Community Learning Series


                          • Tips

                            Tips

                          • Functions

                            Functions

                          • Meetups

                            Meetups

                          • Kbase

                            Kbase

                          • Resources

                            Resources

                          • Digest

                            Digest

                          • CRM Marketplace

                            CRM Marketplace

                          • MVP Corner

                            MVP Corner




                            Zoho Writer Writer

                            Get Started. Write Away!

                            Writer is a powerful online word processor, designed for collaborative work.

                              Zoho CRM コンテンツ






                                ご検討中の方

                                  • Recent Topics

                                  • Zoho CRM - Client Portal - Hide Notes Related List

                                    Hi Community, I'm building a customer portal and I can't find a way to hide the notes related list. I don't want the client to see the notes I have about them. Is there a way to do this as it is no bin/trash icon when I hover over.
                                  • "Pivot Table" Conditional Formatting

                                    Team, I there a way to use conditional formatting a "Pivot Table"  report? Thanks, Arron Blue Pumpkin Hosting | Solutions Made Simple
                                  • How many clients can be added to Zoho Practice?

                                    How many clients can be added to Zoho Practice without having their zoho app?
                                  • Stage History

                                    when viewing a ticket , and you look at stage history tab (kanban view) and look at the stage duration column in days, it shows the current stage of the ticket as " current stage ". Should it not rather show the amount of days it has been in that current
                                  • Send Automated WhatsApp Messages and Leverage the Improved WhatsApp Templates

                                    Greetings, I hope all of you are doing well. We're excited to announce a major upgrade to Bigin's WhatsApp integration that brings more flexibility, interactivity, and automation to your customer messaging. WhatsApp message automation You can now use
                                  • Automating Ticket Responses Using Zoho Desk's AI Features

                                    We’re looking to set up an automation within Zoho Desk that can analyze incoming emails or tickets and automatically respond with relevant knowledge base articles based on the content of the request. Could you please guide us on how to configure this
                                  • Optimising CRM-Projects workflows to manage requests, using Forms as an intermediary

                                    Is it possible to create a workflow between three apps with traceability between them all? We send information from Zoho CRM Deals over to Zoho Projects for project management and execution. We have used a lookup of sorts to create tasks in the past,
                                  • Service locations are tied to contacts?

                                    Trying the system out. And what I discovered is that it seems that the whole logic of the app is, I'd say, backwards. There is a Customer - a company. The company has contact persons and service locations can be associated with different contact persons.
                                  • Enhancements to Zoho Maps integration tasks

                                    Hello everyone, We're excited to announce enhancements to the Zoho Maps integration tasks in Deluge, which will boost its performance. This post will walk you through the upcoming changes, explain why we're making them, and detail the steps you need to
                                  • Bug in Total Hour Calculation in Regularization for past dates

                                    There is a bug in Zoho People Regularization For example today is the date is 10 if I choose a previous Date like 9 and add the Check in and Check out time The total hours aren't calculated properly, in the example the check in time is 10:40 AM check
                                  • Narrative 12: Sandbox - Testing without the risk

                                    Behind the scenes of a successful ticketing system: BTS Series Narrative 12: Sandbox - Testing without the risk What is a sandbox environment? A sandbox environment is a virtual playground that allows you to test freely and experiment with various elements
                                  • Dynamically catching new file creations

                                    I have a team folder with many subfolders, and in those folders we add new documents all the time. I'd like to have a workflow or script to notify me (and then take other actions) when a file is added anywhere in that structure that ends in "summary.txt".
                                  • Announcing new features in Trident for Mac (1.27.0)

                                    Hello everyone! Trident for macOS (v1.27.0) is here with new features and enhancements to improve scheduling and managing your calendar events. Let's take a quick look at them. Stay aligned across time zones. Both the scheduled and original time zones
                                  • Branding of native system emails

                                    Make system emails adjustable in terms of branding. We want our system to be completely white label, because it is not a CRM anymore, it's way more than that. We are following the strategy of "CRM for everyone" to use the CRM in all departments, not only
                                  • Slow uploads of large files

                                    I'm wanting to use Workdrive for transferring large images and video (we're talking things from 100MB-5GB). I'm running solo on a 500MBit/sec fiber connection. I'm getting upload speeds to Workdrive of no more than about 1-3Mbytes/sec when going through
                                  • Migrate Your Notes from OneNote to Zoho Notebook Today

                                    Greetings Notebook Users, We’re excited to introduce a powerful new feature that lets you migrate your notes from Microsoft OneNote to Zoho Notebook—making your transition faster and more seamless than ever. ✨ What’s New One-click migration: Easily import
                                  • need to upload from airtable to google drive

                                    I have a zapier zap that automates between airtable and google drive. When a customer uploads a new file into airtable via a client portal interface, zapier uploads that file into a folder linked to that customer's project record. I need to replicate
                                  • Can't delete functions that are associated with deleted workflow rules

                                    We have a handful of functions that were once associated with a workflow rule, but the rule has been deleted. The function still thinks it is associated so I can't assign it to a new rule. It is starting to get really messy because we have a list of functions
                                  • Default Sorting on Related Lists

                                    Is it possible to set the default sorting options on the related lists. For example on the Contact Details view I have related lists for activities, emails, products cases, notes etc... currently: Activities 'created date' newest first Emails - 'created
                                  • Credit Management: #1 Credit You Owe vs Credits Owed to the Business

                                    Think about the last time you ordered food online. You might have paid in advance through your card, but you received a $20 refund because your order got delayed or cancelled. In most apps, refunds don't go into the bank account directly; instead, they're
                                  • Tip #46- Turn Every Session into an Insight with Zoho Assist survey report- 'Insider Insights'

                                    Delivering exceptional remote support isn’t just about resolving issues, it’s about understanding how both customers and technicians experience each session. That’s where Survey Report in Zoho Assist come in. You can configure and customize survey questions
                                  • Enhancing Zia's service with better contextual responses and article generation

                                    Hello everyone, We are enhancing Zia's Generative AI service to make your support experience smarter. Here's how: Increased accuracy with Qwen One of the key challenges in AI is delivering responses that are both contextually accurate and empathetic while
                                  • Allow the usage of Multi-Select fields as the primary field on "Layout Rules"

                                    We want to force our users to enter some explanation strings when a multi-select field has been utilized. I can't understand the reason for the usage restriction of Multi-Select fields as a primary field. This is a simple "Display hidden mandatory field
                                  • CRM/Bookings integration edits Contact names

                                    Hi there, I've installed the extension that connects Zoho CRM and Zoho Bookings. When we get a new appointment from Bookings from an existing Contact, that Contact's record shows this: First Name was updated from asd to blank value Last Name was updated
                                  • Domain Change

                                    “Please update my Email-in domain from @biginmail.biginmail.in to @biginmail.zoho.com. Messages to the .in domain are bouncing.”
                                  • Webhooks Limit Exceeded

                                    Today, I received an error message saying, 'Total number of Webhook call exceeded', but when I look at Manage > Billing, it doesn't look like any of my invokeURL calls are being logged. Following the advice from this thread: https://help.zoho.com/portal/en/community/topic/webhooks-daily-limits-in-zoho-creator
                                  • Auto select option in CRM after Zoho Form merge

                                    Hi, I have a dropdown field in Zoho CRM that is filled with a Zoho Form. The data is filled but not automatically shown. After selecting the right value in the dropdown the information a second field is shown. So the question is; how can I make the dropdown
                                  • Bring your CRM data straight into your presentations in Zoho Show

                                    Let's say you are working on a presentation about your team's sales pipeline for an upcoming strategy meeting. All the information you need about clients and leads is in Zoho CRM, but you end up copying details from the CRM into your slides, adjusting
                                  • Improved RingCentral Integration

                                    We’d like to request an enhancement to the current RingCentral integration with Zoho. RingCentral now automatically generates call transcripts and AI-based call summaries (AI Notes) for each call, which are extremely helpful for support and sales teams.
                                  • Introducing New APIs in Zoho Contracts

                                    We are excited to announce the release of new APIs in Zoho Contracts to help you automate and manage every stage of your contract lifecycle more efficiently. Here’s a quick overview of what’s new: 1. Complete Contract Draft You can use this API to complete
                                  • Vimeo

                                    For me Vimeo is the most important video social channel for media and filmmakers. Would others agree and like it added to Zoho Social.
                                  • Delete a department or category

                                    How do I delete a Department? Also, how do I delete a Category? This is pretty basic stuff here and it's impossible to find.
                                  • Organization Emails in Email History

                                    How can I make received Org Emails to show up here?
                                  • Deleting Fields in CRM Deletes Views in Analytics

                                    Hey friends! I'm having some issues when we modify some fields within ZohoCRM. There are times where we need to sunset a field and eventually completely remove it. In these instances, it seems like a lot of views are removed in Analytics. This ranges
                                  • How to setup pricing in Zoho

                                    Hi everyone, I am relatively new here and have just moved from my old inventory system to the Zoho one. I am trying to get my head around how it all works. I am mostly setup connected to a shopify store, but I do manual sales also For manual invoicing,
                                  • Prefilled Date fields auto-changed and then locked when using “Edit as new”

                                    If a document out for signature has date fields (not SignedDate fields) that were pre-filled before sending, and then you use “Edit as new” to create a new version of the same document, the value of those date fields gets automatically changed to today
                                  • Is there a way to update all the start and end dates of tasks of a project after a calendar change?

                                    Hi! Here's my situation. I've built a complete project planning. All its tasks have start dates and due dates. After completing the planning, I've realized that the project calendar was not the right one. So I changed the project calendar. I now have
                                  • Access Phone Field Components (Country Code) Directly

                                    Hello everyone, I'd like to propose an enhancement for the Phone field in Zoho Creator. The Problem: The Phone field captures the country code and local number separately, but stores them as a single string (e.g., +1234567890). To get the country code,
                                  • Send mass messages through WhatsApp from the Tickets module

                                    Hi Everyone! Effective communication is key to delivering prompt and reliable customer support. Because WhatsApp is one of the most widely used and familiar messaging platforms, it's an effective channel for agents to reach customers who have submitted
                                  • Lead Owner Signature Merge Field

                                    I want to automatically insert a signature (i.e. contact info usually found at the bottom of an email) into an email template, depending on who the lead owner is. What is the merge code for the Signature from a Users profile? CRM > Settings > Customization > Templates There is a popup near the bottom of the edit screen which says: "You can insert a Signature, which is available as a merge field in the users section." It is also referenced on this page: https://help.zoho.com/portal/en/kb/crm/customize-crm-account/customizing-templates/articles/template-builder#Merge_Fields
                                  • Next Page