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.

    • 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
      • Recent Topics

      • How to edit form layout for extension

        I am working on extension development. I have created all the fields. I want to rearrange the layout in Sigma platform. But there is no layout module in Sigma. How can I achieve this for extensions other than Zet CLI and putting the fields into widget
      • Employees not Users

        Hello, We are a construction company that has +180 employees and most of them are in remote location working onsite with no access to internet. Is it possible that we have data stored for all employees but have only 5-10 users who will be in charge of entering employees data? or do we have to pay for all +180 employees? even though they won't be using the system?
      • Zoho people generatimg pdf

        Hello , now i want to make a customm button in zoho people that is inside a deduction module , that fetches all the records and generate a pdf with a template that i have done in the mail merges template , i was told that i have to upload template on
      • Ability to Filter Alias Mailboxes in Zoho Recruit

        Dear Zoho Recruit Team, I hope you are doing well. We would like to request a feature enhancement regarding the handling of alias mailboxes in Zoho Recruit. Currently, when we connect an alias mailbox (e.g., jobs@domain.com) from our Zoho One account
      • zohorecruit.com career form postcode bug

        Dear, When I select a postcode from the drop down on a zohorecruit.com career form, the street text field is automatically filled with the name of the city, which should not happen. Any idea how I can fix this? Thanks, Bart
      • Office-365-agenda and Microsoft Teams Integration

        Dear, I have a trial version of Zoho Recruit and trying to evaluate the Microsoft Teams Integration in Zoho Recruit. After registering with my Office 365 account and checking the result of the registration/sign-in at https://mysignins.microsoft.com/ (which
      • Delegate Access - Mobile iOS/iPad

        We’re over the moon that delegate access is now available in Zoho Mail as we were nearly ready to switch platforms because of it! Is there a timeline on when delegate mailboxes will be accessible from the iOS and iPad OS applications? Thanks, Jake
      • How to add Connector in developer platform zoho?

        Hi, I am working on creating an Extension, and part of the development is to retrieve Email templates. In my CRM instance I can invokeURL by creating Zoho OAuth connection and get the template. But developer platform does not provide Zoho OAuth or any
      • How to archive Lost/Junk Leads so sales reps don’t see them, but keep them for reporting?

        Hi everyone, In our Zoho CRM we have two Lead Status values: Lost Lead and Junk Lead. What I want to achieve is: When a lead is marked as Lost or Junk, it should disappear from my sales reps’ Lead views (so they only see active leads). At the same time,
      • Zoho CRM Canvas Copy Original Layout

        Hello all, I want to use Canvas to make small changes to certain views, not to make huge changes. Is it possible to copy the original Zoho layout and set-up and start from there? I checked and all I can find are some templates which are far from the original
      • Is there a way to request a password?

        We add customers info into the vaults and I wanted to see if we could do some sort of "file request" like how dropbox offers with files. It would be awesome if a customer could go to a link and input a "title, username, password, url" all securely and it then shows up in our team vault or something. Not sure if that is safe, but it's the best I can think of to be semi scalable and obviously better than sending emails. I am open to another idea, just thought this would be a great feature.  Thanks,
      • Revenue Management: #5 Revenue Recognition in SaaS

        If you're building or running a SaaS business, you've probably encountered this. You get paid upfront for a subscription and a one-time onboarding fee, but you end up with confusion about when to consider it revenue. Can I book all of it now? Should I
      • MS Teams for daily call operations

        Hello all, Our most anticipated and crucial update is finally here! Organizations using Microsoft Teams phone system can now integrate it effectively with Zoho CRM for tasks like dialling numbers and logging calls. We are enhancing our MS Teams functionality
      • Zoho Learn Course Access Issue

        One of the learners in a specific course can't see any lessons. They are registered as both a user and learner for this course in Zoo Learn. What could be the reason?
      • ZOHOLICS Japan 2025 開催のお知らせ(再投稿)

        【コミュニティユーザーの皆さまへお知らせ】 Zoho 最大のユーザーイベント「ZOHOLICS Japan 2025」を9月19日(金)に開催します。 AI活用に関する特別講演、ユーザー事例、Zoho 製品の活用例のご紹介など、Zoholicsならではのセッションをご用意しています。 Zoho コミュニティ開催のMeetupとはまた違った雰囲気のイベントです。 ご都合のつく方はお気軽にご参加ください✨ 詳細はこちら https://events.zoho.jp/zoholics2025#/?affl=forumpost2
      • Phone Number format for Bulk Upload via csv for Zoho Sign

        What is the phone number format that we need to use for a bulk recipient upload via csv in zoho sign, should the country code be included for ex if its US should it be +18889007865 ior 18889007865 or without country code 8889007865? the sample csv provided
      • Filter Page Elements By Selectable Date Range

        I have created a basic Page that will serve as a client dashboard with elements that will provide simple counts & sums of data in reports. There are no reports or forms added to the page, just elements. It currently functions as needed, but shows the
      • Can’t Enter my Notebook is Locked

        I’ve been using Notebook for taking notes at my college for a month and I never signed in and I never established a password. Today I tried to enter the app after the update and it asked me for a password. I need to access to my notes urgently and I can’t
      • Send Email Directly to Channel

        Hi, We are coming from Slack. In Slack each channel has a unique Email address that you can send emails too. I currently forward a specific type of email from my Gmail InBox directly do this channel for Verification Codes so my team doesn't have to ask
      • Secure your external sharing process with OTP Authentication

        For any business, it's crucial to share files externally in a way that is both secure and controlled. Let's say you want to share confidential data with your partners and vendors. You must ensure that only your intended recipients can access the shared
      • Items attribute questions

        Many of my items have attributes, such as size and color. How can I add new fields to the "New Items" screen to capture that in my Purchase Orders, Items, and Sales Order pages? I only see these attribute fields when adding an Item Group. Also, on the
      • Is there a way to search mail for items you haven't yet responded to?

        I'm trying to create a search to show emails that haven't been responded to and that have also been assigned a tag or label. Is there a way to search for the inverse of replied?
      • Zobot and Sales IQ

        What will happen to the Zoho Sales IQ being integrated to the website after creating the Zobot on the website too
      • Subtotals per Header

        Hey, we would like to display subtotals per header in our invoices:
      • upgrade storage

        how to upgrade my storage
      • Linking items from books and subscription

        Linking books and subscription It is a pity that it is not possible to associate with a product an article present in ZohoBooks This would make it possible to manage products composed of a nomenclature of articles of books
      • Add the ability to view number of hours planned for a workorder while in the Dispatch Console

        Hi Zoho, When dispatching a work order through the Dispatch Console, there's currently no way to view the number of planned hours for the service tasks associated with that specific work order. Having to click into the details each time to check planned
      • Uploading Files from uploads api

        I tried uploading the image from the API call but its giving me unauthorized error even i have given valid token in header. After uploading i need that id to pass in create ticket api uploads but its not uploading that file. Please help me out in th
      • Categorize Items with Item Headers

        Hello customers, Did you ever want to classify items based on specific categories to help your customers understand your invoice better? With the new Item Header feature, you can easily categorize items in your invoices and estimates and give them a common title.  Item Headers are available in the Invoices, the Recurring Invoices and the Estimates module. It can be carried forward from estimates to invoices at the time of converting the estimates.  To add an item header: Go to the Estimates, Invoices
      • URLs being masked despite disabling tracking

        Hey, We had disabled click tracking from an email update we are sharing with our users. Despite this, the URL the end user is receiving is masked, and looks like "https://qksyl-cmpzourl.maillist-manage.net/click/1d8e72714515cda6/1d8e72714515ca70" instead
      • Change format of quantity format

        Hi,  I would like to change the qunatity format from 1,00 to 1.  Is this possible?   thanks!
      • square up and Zoho books

        Since we set up the app connection between square up and Zoho books we have realised that when the amounts are getting transferred Zoho is adding VAT again making the numbers 20% more than they should be. We have checked our settings and they VAT/ Tax
      • How to provide Access rights to specific Bank accounts

        We have several bank accounts. I want to provide certain members access to only 2-3 accounts. It appears that when I give permissions, they are for ALL bank accounts. How do I do that?
      • Errors Getting a Bank Transaction

        Using Postman(for testing), I am receiving errors when attempting to get a single bank transaction. I am able to receive the list of bank transactions with https://www.zohoapis.com/books/v3/banktransactions/?organization_id={org_id} but when I try to
      • Subscriptions Plans and CRM Products Integration?

        Is there any way to set up plans and pricing in Zoho Subscriptions and have those available as Products in the CRM? We are trying to set up the CRM and the Products seem to be more geared toward selling products that are not subscription based.  So if we sell annual or monthly contracts which are $10/seat/month, I can set this up as a plan in Subscriptions, but how can I set the same thing up in the CRM for the salespeople to sell/quote? What is the best practice for setting up the CRM Products to
      • Mapping Zoho Subscription Plans and Products to CRM and Books

        We have products that are one time products and subscription products.   1) We would like to have the ability to create an opportunity in CRM that includes both one-time charge products and subscription products.  Currently the only way of creating a subscription product is by defining a "Product->Plan" hierarchy in Zoho Subscription.  It appears that the only SYNC between Subscription and CRM is around contacts and the Product->Plan hierarchy doesn't sync to CRM, thus, you would need a duplicate
      • Zoho People Attendance Module - Split Shifts

        Hello, Our business employs a number of college students who often split their workdays between work hours and class schedules, resulting in split shifts. For example: Employee 1 works 9am to 11:30am and then leaves for class, returning to work at 3pm
      • Bulk update fields based on date

        Hi! I need all the quotes with "Quote Stage" set to "Next Year" to update to "Draft" every January 8 (every year). Can you help?
      • Target for a campaign

        Hi, Hope you can help me. I need to create a report (for a marketing campaign) that combines Deals and Contacts to export all the contacts with closed deals. I can easily do that, what I cannot do is to remove from the target the contacts that have more
      • Zoho Desk + Jira integration - Email notifications and comments posted by administrator instead of real user

        Dear All, I set up the integration under my admin account, and now when users leave comments in Jira (to created tickets in Zoho Desk), the email notifications show that the ‘Administrator’ left a comment, not a real user. The same happens in the ticket
      • Next Page