An article on some Zoho ANSI-SQL (in other words, queries that work in Zoho Analytics) as I couldn't find any examples online on how to do this.
Why?
I've been tasked with generating a radar/spider/web chart based on the results from a survey. Apparently this will be done in Zoho Analytics.
The Brief:
We would like the survey data to be generated as a chart and embedded in a PDF brochure we send out to our customers...
How?
Well fulfilling the brief (whether it's the right tool for the job is a different issue), let us synchronize Zoho Surveys into Zoho Analytics. I found I need to edit the setup of the sync every time a new survey is added... but at least it lets me synchronize every hour.
Then I'll write the SQL queries to get the individual responses into a table with labels and columns that can help me group them (and identify which responses belong to which survey attempts).
Then I'll write the SQL to sum up the responses per page per survey attempt.
Finally, I'll see if analytics can be used to generate the radar chart.
ZohoSurveys - Individual Responses Query
copyraw
You can take out the order by clause. I just have it here to preserve my sanity when viewing the output. Note that "Page Number" is my custom sorting mechanism so you would have to refer to the output without this column before modifying the query to include the page names of your survey. GENERAL was the first page asking for lead/contact name when I have this prepopulated from a ZohoCRM integration. Also note that the question number comes from it prefixing the question per page that the client did when setting up her survey (eg. 1. question 1, 2. next question, 3. ...).SELECT concat('REF-', left_pad(to_integer(substring(r2."ID", 8)), 6, '0')) AS "Attempt ID", s."Title" AS "Survey", r2."End Date" AS "Response Date", CASE p."Title" WHEN 'DEMOGRAPHICS' THEN 2 WHEN 'AWARENESS' THEN 3 WHEN 'ENGAGEMENT' THEN 4 WHEN 'SATISFACTION & FEEDBACK' THEN 5 WHEN 'USABILITY' THEN 6 WHEN 'FEATURES' THEN 7 WHEN 'PRICING' THEN 8 WHEN 'SUPPORT' THEN 9 WHEN 'TRUST' THEN 10 WHEN 'PERFORMANCE' THEN 11 END AS "Page Number", p."Title" AS "Page", p."Description" AS "Description", to_integer(substring_before(q."Text", '.')) AS "Question Number", q."Text" AS "Question", r."Text" AS "Answer", r2."Status" AS "Status", r3."Variable Value" AS "Respondent Email" FROM "Responses (Zoho Survey)" r LEFT JOIN "Surveys (Zoho Survey)" s ON r."Survey ID" = s."ID" LEFT JOIN "Questions (Zoho Survey)" q ON r."Question ID" = q."ID" LEFT JOIN "Respondents (Zoho Survey)" r2 ON r."Respondent ID" = r2."ID" LEFT JOIN "Response Variables (Zoho Survey)" r3 ON r2."ID" = r3."Respondent ID" LEFT JOIN "Pages (Zoho Survey)" p ON r."Page ID" = p."ID" LEFT JOIN "Rows (Zoho Survey)" r4 ON q."ID" = r4."Question ID" WHERE p."Title" 'GENERAL' ORDER BY "Response Date" DESC, "Attempt ID", "Page Number", "Question Number"
- SELECT
- concat('REF-', left_pad(to_integer(substring(r2."ID", 8)), 6, '0')) AS "Attempt ID",
- s."Title" AS "Survey",
- r2."End Date" AS "Response Date",
- CASE p."Title"
- WHEN 'DEMOGRAPHICS' THEN 2
- WHEN 'AWARENESS' THEN 3
- WHEN 'ENGAGEMENT' THEN 4
- WHEN 'SATISFACTION & FEEDBACK' THEN 5
- WHEN 'USABILITY' THEN 6
- WHEN 'FEATURES' THEN 7
- WHEN 'PRICING' THEN 8
- WHEN 'SUPPORT' THEN 9
- WHEN 'TRUST' THEN 10
- WHEN 'PERFORMANCE' THEN 11
- END AS "Page Number",
- p."Title" AS "Page",
- p."Description" AS "Description",
- to_integer(substring_before(q."Text", '.')) AS "Question Number",
- q."Text" AS "Question",
- r."Text" AS "Answer",
- r2."Status" AS "Status",
- r3."Variable Value" AS "Respondent Email"
- FROM "Responses (Zoho Survey)" r
- LEFT JOIN "Surveys (Zoho Survey)" s ON r."Survey ID" = s."ID"
- LEFT JOIN "Questions (Zoho Survey)" q ON r."Question ID" = q."ID"
- LEFT JOIN "Respondents (Zoho Survey)" r2 ON r."Respondent ID" = r2."ID"
- LEFT JOIN "Response Variables (Zoho Survey)" r3 ON r2."ID" = r3."Respondent ID"
- LEFT JOIN "Pages (Zoho Survey)" p ON r."Page ID" = p."ID"
- LEFT JOIN "Rows (Zoho Survey)" r4 ON q."ID" = r4."Question ID"
- WHERE p."Title" 'GENERAL'
- ORDER BY "Response Date" DESC,
- "Attempt ID",
- "Page Number",
- "Question Number"
ZohoSurveys - Page Responses Query
This is a second query which queries the first query... Well it's for the radar chart and what it does is group all the responses per page (per survey attempt) so that given a survey reference, it will give me the sums per page.
copyraw
SELECT i."Attempt ID" AS "Survey Response", max(i."Survey") AS "Survey", i."Page Number", max(i."Page") AS "Page", max(i."Respondent Email") AS "Respondent", sum(to_integer(i."Answer")) AS "Answer Sum" FROM "ZohoSurveys - Individual Responses Query" i GROUP BY i."Attempt ID", i."Page Number"
- SELECT
- i."Attempt ID" AS "Survey Response",
- max(i."Survey") AS "Survey",
- i."Page Number",
- max(i."Page") AS "Page",
- max(i."Respondent Email") AS "Respondent",
- sum(to_integer(i."Answer")) AS "Answer Sum"
- FROM "ZohoSurveys - Individual Responses Query" i
- GROUP BY i."Attempt ID",
- i."Page Number"
The Analytics bit to set up a Web Chart


The final bit: Embed the radar chart in a Zoho CRM merge template
This is the easy bit... not really... not a clue how I'm going to do this nor if writer will just allow us to embed it... but let's give it a go:
The page responses query showing only the last survey attempt per respondent
I'm going to modify the analytics query above to only return the latest responses that a respondent has completed; this means that if they have completed the same survey twice, this only returns the latest one they completed.
copyraw
SELECT i."Attempt ID" AS "Survey Response", max(i."Survey") AS "Survey", i."Page Number", max(i."Page") AS "Page", max(i."Respondent Email") AS "Respondent", sum(to_integer(i."Answer")) AS "Answer Sum" FROM "ZohoSurveys - Individual Responses Query" i -- join to only return only the latest survey attempt answered per respondent LEFT JOIN "ZohoSurveys - Individual Responses Query" j ON LOWER(j."Respondent Email") = LOWER(i."Respondent Email") AND (j."Response Date" > i."Response Date" OR (j."Response Date" = i."Response Date" AND j."Attempt ID" > i."Attempt ID")) WHERE j."Attempt ID" IS NULL GROUP BY i."Attempt ID", i."Page Number"
- SELECT
- i."Attempt ID" AS "Survey Response",
- max(i."Survey") AS "Survey",
- i."Page Number",
- max(i."Page") AS "Page",
- max(i."Respondent Email") AS "Respondent",
- sum(to_integer(i."Answer")) AS "Answer Sum"
- FROM "ZohoSurveys - Individual Responses Query" i
- -- join to only return only the latest survey attempt answered per respondent
- LEFT JOIN "ZohoSurveys - Individual Responses Query" j ON LOWER(j."Respondent Email") = LOWER(i."Respondent Email")
- AND (j."Response Date" > i."Response Date"
- OR (j."Response Date" = i."Response Date"
- AND j."Attempt ID" > i."Attempt ID"))
- WHERE j."Attempt ID" IS NULL
- GROUP BY i."Attempt ID",
- i."Page Number"
Export the web chart from Analytics and attach as an image on the CRM record
So the survey is sent via the contact module but we actually only ask Analytics about the survey and the contact email. Here's a standalone function given a CRM contact record ID that will take the survey they completed, generate a radar/spider/web chart, and attaches it as a PNG file on the CRM record.
copyraw
string standalone.fn_Analytics_ExportChart(int p_ContactID) { /* ******************************************************************************* Function: string standalone.fn_Analytics_ExportChart(int p_ContactID) Label: Fn - Analytics - Export Chart Trigger: Standalone / On-demand Purpose: Extracting a chart from Zoho Analytics Inputs: The CRM contact record Outputs: - Date Created: 2025-09-25 (Joel Lipman) - Initial release Date Modified: ??? - ??? More Information: https://help.zoho.com/portal/en/community/topic/export-zoho-analytics-chart-with-api-in-deluge https://www.zoho.com/analytics/api/v1/data-api/export-data.html https://www.zoho.com/analytics/api/v2/bulk-api/export-data.html https://www.zoho.com/crm/developer/docs/api/v8/upload-files-to-zfs.html https://www.zoho.com/crm/developer/docs/api/v8/update-records.html https://help.zoho.com/portal/en/kb/writer/automation-guide/zoho-crm/articles/how-to-merge-subform-fields-and-image-field-via-zoho-writer-s-merge-apis#Step_5_Pass_Image_Field_Values_in_Merge_API ******************************************************************************* */ // // -------------------------------------------------------------------- // using Analytics API v1.0 (suprisingly still works in September 2025) /* v_OwnerEmail = "<the email of the app owner in analytics>"; v_WorkspaceName = "Zoho CRM Reports"; v_ViewName = "ZohoSurveys - Spider Chart"; // invalid oauth is check your TLD (eg. .com or .eu or ...) v_Endpoint = "https://analyticsapi.zoho.com/api/" + v_OwnerEmail + "/" + zoho.encryption.urlEncode(v_WorkspaceName) + "/" + zoho.encryption.urlEncode(v_ViewName); m_Params = Map(); m_Params.put("ZOHO_ACTION","EXPORT"); m_Params.put("ZOHO_OUTPUT_FORMAT","IMAGE"); m_Params.put("ZOHO_ERROR_FORMAT","JSON"); m_Params.put("ZOHO_API_VERSION","1.0"); f_ChartExport = invokeurl [ url :v_Endpoint type :POST parameters:m_Params connection:"zanalytics" ]; */ // // -------------------------------------------------------------------- // using Analytics API v2.0 (this time with criteria) // // get contact email and related surveys r_ContactDetails = zoho.crm.getRecordById("Contacts", p_ContactID); v_ContactEmail = r_ContactDetails.get("Email"); info v_ContactEmail; // // the following related list couldn't be found hence the change to the summary query in analytics // l_ContactSurveys = zoho.crm.getRelatedRecords("Zoho_Survey", "Contacts", p_ContactID); // info l_ContactSurveys; // // get the org id by going to the cog in the top right for org settings in Analytics and look at the last number in the URL v_AnalyticsOrgID = "123456879"; // // go to the web chart we made in analytics and look at the URL: // the workspace ID follows the /workspace/ // the view ID follows the /view/ part v_WorkspaceID = "1234567000008912345"; v_TableID = "9876543000002198765"; // // put the survey name that the web chart applies to v_SurveyName = "My Own Survey"; // // build the header for interacting with the API v2.0 m_Header = Map(); m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID); // // build up the config to send (we want an image back) m_Config = Map(); m_Config.put("responseFormat","image"); // // for the criteria, we need to get the exact survey we want to chart on // we also want the respondent to match the email on the contact record // note the labels correspond to the user filters in our web chart v_Criteria = "\"Survey\"='"+v_SurveyName+"' and \"Respondent\"='" + v_ContactEmail + "'"; // // build up the config and send it to the API m_Config.put("criteria",v_Criteria); m_Params = Map(); m_Params.put("CONFIG",m_Config.toString()); // // in analytics, browse to the target table and note the URL IDs after workspace and view v_Endpoint2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data"; info v_Endpoint2; // // do the deed f_ChartExport = invokeurl [ url :v_Endpoint2 type :GET parameters:m_Params headers:m_Header connection:"zanalytics" ]; // // change the filename f_ChartExport.setFileName(v_SurveyName + " " + zoho.currentdate.toString("dd-MMM-yyyy") + ".png"); f_ChartExport.setParamName("file"); info f_ChartExport; // // -------------------------------------------------------------------- // // declare apis domain v_ZohoApisDomain = "https://www.zohoapis.com"; // // upload the image to the file system or ZFS v_FileID = ""; r_ZfsUpload = invokeurl [ url :v_ZohoApisDomain + "/crm/v8/files" type :POST files:f_ChartExport connection:"zcrm" ]; l_ZfsData = ifnull(r_ZfsUpload.get("data"),List()); for each m_ZfsData in l_ZfsData { if(!isNull(m_ZfsData.get("code"))) { if(m_ZfsData.get("code").equalsIgnoreCase("SUCCESS")) { v_FileID = m_ZfsData.get("details").get("id"); } } } info v_FileID; // // upload to the image field on the contact record (using invokeurl and v8 cost short code wasn't working) m_ImageUpload = Map(); m_ImageUpload.put("File_Id__s",v_FileID); l_ImageUploads = List(); l_ImageUploads.add(m_ImageUpload); m_UpdateContact = Map(); m_UpdateContact.put("My_Image_Upload_Field",l_ImageUploads); l_RecordsToSend = List(); l_RecordsToSend.add(m_UpdateContact); m_Data = Map(); m_Data.put("data",l_RecordsToSend); m_Data.put("trigger",List()); info m_Data; v_Endpoint3 = v_ZohoApisDomain + "/crm/v8/Contacts/" + p_ContactID; r_UpdateContact = invokeurl [ url :v_Endpoint3 type :PUT parameters:m_Data.toString() connection:"zcrm" ]; info r_UpdateContact; // // now need to query to get the preview URLs needed to merge images into writer documents v_PreviewID = ""; r_ContactDetailsAgain = invokeurl [ url :v_Endpoint3 type :GET connection:"zcrm" ]; l_ResponseData = ifnull(r_ContactDetailsAgain.get("data"),List()); for each m_ResponseData in l_ResponseData { if(!isNull(m_ResponseData.get("id"))) { for each m_ReportImage in m_ResponseData.get("My_Image_Upload_Field") { if(m_ReportImage.get("File_Name__s").startsWith(v_SurveyName)) { v_PreviewID = m_ReportImage.get("Preview_Id__s"); break; } } } } // // build up URL if(v_PreviewID != "") { v_ImageURL = v_ZohoApisDomain + "/crm/v8/__attachment_preview/" + v_PreviewID; info v_ImageURL; // // specify template id of mail merge document (browse to it and check the URL) v_Template3 = "aaaa1111bbbb2222cccc3333dddd4444eeee5"; // // embed in a mail merge brochure PDF //get merge field names (for reference - not required later in this fuction) //r_Fields = zoho.writer.getMergeFields(v_Template3,"zwriter"); //info r_Fields; // // map data to these fields m_Data = Map(); m_Data.put("Radar_Chart",v_ImageURL); // m_MergedData = Map(); m_MergedData.put("merge_data",{"data":m_Data}); // // to generate and attach r_DownloadResponse = zoho.writer.mergeAndDownload(v_Template3,"pdf",m_MergedData,"zwriter"); r_AttachResponse = zoho.crm.attachFile("Contacts",p_ContactID,r_DownloadResponse); if(r_AttachResponse.get("code") == "SUCCESS") { info "Generated and attached file to CRM record. "; } } // // send to an email for testing /* sendmail [ from :zoho.adminuserid to :v_ContactEmail subject :"Radar Chart for " + v_ViewName message :"See attached" Attachments :file:f_ChartExport ] */ // return "The function executed. Doesn't mean it did what we wanted to do. Just that it executed."; }
- string standalone.fn_Analytics_ExportChart(int p_ContactID)
- {
- /* *******************************************************************************
- Function: string standalone.fn_Analytics_ExportChart(int p_ContactID)
- Label: Fn - Analytics - Export Chart
- Trigger: Standalone / On-demand
- Purpose: Extracting a chart from Zoho Analytics
- Inputs: The CRM contact record
- Outputs: -
- Date Created: 2025-09-25 (Joel Lipman)
- - Initial release
- Date Modified: ???
- - ???
- More Information:
- https://help.zoho.com/portal/en/community/topic/export-zoho-analytics-chart-with-api-in-deluge
- https://www.zoho.com/analytics/api/v1/data-api/export-data.html
- https://www.zoho.com/analytics/api/v2/bulk-api/export-data.html
- https://www.zoho.com/crm/developer/docs/api/v8/upload-files-to-zfs.html
- https://www.zoho.com/crm/developer/docs/api/v8/update-records.html
- https://help.zoho.com/portal/en/kb/writer/automation-guide/zoho-crm/articles/how-to-merge-subform-fields-and-image-field-via-zoho-writer-s-merge-apis#Step_5_Pass_Image_Field_Values_in_Merge_API
- ******************************************************************************* */
- //
- // --------------------------------------------------------------------
- // using Analytics API v1.0 (suprisingly still works in September 2025)
- /*
- v_OwnerEmail = "<the email of the app owner in analytics>";
- v_WorkspaceName = "Zoho CRM Reports";
- v_ViewName = "ZohoSurveys - Spider Chart";
- // invalid oauth is check your TLD (eg. .com or .eu or ...)
- v_Endpoint = "https://analyticsapi.zoho.com/api/" + v_OwnerEmail + "/" + zoho.encryption.urlEncode(v_WorkspaceName) + "/" + zoho.encryption.urlEncode(v_ViewName);
- m_Params = Map();
- m_Params.put("ZOHO_ACTION","EXPORT");
- m_Params.put("ZOHO_OUTPUT_FORMAT","IMAGE");
- m_Params.put("ZOHO_ERROR_FORMAT","JSON");
- m_Params.put("ZOHO_API_VERSION","1.0");
- f_ChartExport = invokeUrl
- [
- url :v_Endpoint
- type :POST
- parameters:m_Params
- connection:"zanalytics"
- ];
- */
- //
- // --------------------------------------------------------------------
- // using Analytics API v2.0 (this time with criteria)
- //
- // get contact email and related surveys
- r_ContactDetails = zoho.crm.getRecordById("Contacts", p_ContactID);
- v_ContactEmail = r_ContactDetails.get("Email");
- info v_ContactEmail;
- //
- // the following related list couldn't be found hence the change to the summary query in analytics
- // l_ContactSurveys = zoho.crm.getRelatedRecords("Zoho_Survey", "Contacts", p_ContactID);
- // info l_ContactSurveys;
- //
- // get the org id by going to the cog in the top right for org settings in Analytics and look at the last number in the URL
- v_AnalyticsOrgID = "123456879";
- //
- // go to the web chart we made in analytics and look at the url:
- // the workspace ID follows the /workspace/
- // the view ID follows the /view/ part
- v_WorkspaceID = "1234567000008912345";
- v_TableID = "9876543000002198765";
- //
- // put the survey name that the web chart applies to
- v_SurveyName = "My Own Survey";
- //
- // build the header for interacting with the API v2.0
- m_Header = Map();
- m_Header.put("ZANALYTICS-ORGID",v_AnalyticsOrgID);
- //
- // build up the config to send (we want an image back)
- m_Config = Map();
- m_Config.put("responseFormat","image");
- //
- // for the criteria, we need to get the exact survey we want to chart on
- // we also want the respondent to match the email on the contact record
- // note the labels correspond to the user filters in our web chart
- v_Criteria = "\"Survey\"='"+v_SurveyName+"' and \"Respondent\"='" + v_ContactEmail + "'";
- //
- // build up the config and send it to the API
- m_Config.put("criteria",v_Criteria);
- m_Params = Map();
- m_Params.put("CONFIG",m_Config.toString());
- //
- // in analytics, browse to the target table and note the URL IDs after workspace and view
- v_Endpoint2 = "https://analyticsapi.zoho.com/restapi/v2/workspaces/" + v_WorkspaceID + "/views/" + v_ViewID + "/data";
- info v_Endpoint2;
- //
- // do the deed
- f_ChartExport = invokeUrl
- [
- url :v_Endpoint2
- type :GET
- parameters:m_Params
- headers:m_Header
- connection:"zanalytics"
- ];
- //
- // change the filename
- f_ChartExport.setFileName(v_SurveyName + " " + zoho.currentdate.toString("dd-MMM-yyyy") + ".png");
- f_ChartExport.setParamName("file");
- info f_ChartExport;
- //
- // --------------------------------------------------------------------
- //
- // declare apis domain
- v_ZohoApisDomain = "https://www.zohoapis.com";
- //
- // upload the image to the file system or ZFS
- v_FileID = "";
- r_ZfsUpload = invokeUrl
- [
- url :v_ZohoApisDomain + "/crm/v8/files"
- type :POST
- files:f_ChartExport
- connection:"zcrm"
- ];
- l_ZfsData = ifnull(r_ZfsUpload.get("data"),List());
- for each m_ZfsData in l_ZfsData
- {
- if(!isNull(m_ZfsData.get("code")))
- {
- if(m_ZfsData.get("code").equalsIgnoreCase("SUCCESS"))
- {
- v_FileID = m_ZfsData.get("details").get("id");
- }
- }
- }
- info v_FileID;
- //
- // upload to the image field on the contact record (using invokeUrl and v8 cost short code wasn't working)
- m_ImageUpload = Map();
- m_ImageUpload.put("File_Id__s",v_FileID);
- l_ImageUploads = List();
- l_ImageUploads.add(m_ImageUpload);
- m_UpdateContact = Map();
- m_UpdateContact.put("My_Image_Upload_Field",l_ImageUploads);
- l_RecordsToSend = List();
- l_RecordsToSend.add(m_UpdateContact);
- m_Data = Map();
- m_Data.put("data",l_RecordsToSend);
- m_Data.put("trigger",List());
- info m_Data;
- v_Endpoint3 = v_ZohoApisDomain + "/crm/v8/Contacts/" + p_ContactID;
- r_UpdateContact = invokeUrl
- [
- url :v_Endpoint3
- type :PUT
- parameters:m_Data.toString()
- connection:"zcrm"
- ];
- info r_UpdateContact;
- //
- // now need to query to get the preview URLs needed to merge images into writer documents
- v_PreviewID = "";
- r_ContactDetailsAgain = invokeUrl
- [
- url :v_Endpoint3
- type :GET
- connection:"zcrm"
- ];
- l_ResponseData = ifnull(r_ContactDetailsAgain.get("data"),List());
- for each m_ResponseData in l_ResponseData
- {
- if(!isNull(m_ResponseData.get("id")))
- {
- for each m_ReportImage in m_ResponseData.get("My_Image_Upload_Field")
- {
- if(m_ReportImage.get("File_Name__s").startsWith(v_SurveyName))
- {
- v_PreviewID = m_ReportImage.get("Preview_Id__s");
- break;
- }
- }
- }
- }
- //
- // build up URL
- if(v_PreviewID != "")
- {
- v_ImageURL = v_ZohoApisDomain + "/crm/v8/__attachment_preview/" + v_PreviewID;
- info v_ImageURL;
- //
- // specify template id of mail merge document (browse to it and check the URL)
- v_Template3 = "aaaa1111bbbb2222cccc3333dddd4444eeee5";
- //
- // embed in a mail merge brochure PDF
- //get merge field names (for reference - not required later in this fuction)
- //r_Fields = zoho.writer.getMergeFields(v_Template3,"zwriter");
- //info r_Fields;
- //
- // map data to these fields
- m_Data = Map();
- m_Data.put("Radar_Chart",v_ImageURL);
- //
- m_MergedData = Map();
- m_MergedData.put("merge_data",{"data":m_Data});
- //
- // to generate and attach
- r_DownloadResponse = zoho.writer.mergeAndDownload(v_Template3,"pdf",m_MergedData,"zwriter");
- r_AttachResponse = zoho.crm.attachFile("Contacts",p_ContactID,r_DownloadResponse);
- if(r_AttachResponse.get("code") == "SUCCESS")
- {
- info "Generated and attached file to CRM record. ";
- }
- }
- //
- // send to an email for testing
- /*
- sendmail
- [
- from :zoho.adminuserid
- to :v_ContactEmail
- subject :"Radar Chart for " + v_ViewName
- message :"See attached"
- Attachments :file:f_ChartExport
- ]
- */
- //
- return "The function executed. Doesn't mean it did what we wanted to do. Just that it executed.";
- }
Category: Zoho :: Article: 910
Add comment