Zoho Analytics: Create a Monitoring System with Images

: Joel Lipman; Zoho; 25 July 2024; Hits: 1386

What?
This is an article to remind me how I did this for a customer and images help visualize the health of the system.

Why?
The use-case here is to build a system which monitors a Zoho App for when records were last modified and if a synchronization process starts to fail, there should be images or icons warning or advising of failures.

How?
The key trick to use here the ability for Zoho Analytics to display different images based on a criteria or a value within a data table. As documented in an earlier article, put the URL of the image, then change it's format to show the image and not the URL text. In this example, we are using images uploaded in the Zoho Analytics image library but you would need images either uploaded in Analytics or a publicly accessible image without a user wall.

Pre-amble
Have all the images you want to use ready for uploading into the system stored on your device. A URL (as in an image hosted on another website) can be used so you might skip the first section here where I demonstrate how to upload photos to be hosted by the Zoho Analytics app.

Uploading the images to be hosted by Zoho Analytics
So first steps are login to Zoho Analytics and go into the workspace you want to create the monitoring system. Preferably use the workspace that has the data to check. For demo purposes, I'm going into the CRM Reports workspace > Click on "Create" > Then select "Dashboard":

Zoho Analytics: Create a Monitoring System with Images -

Then in the top right of the dashboard, I'm going to click on the "Widget" button:

On the data column, I'll select any query and any field. It doesn't matter which one here, as long as I can get to the 3rd tab of the widget (it becomes enabled once you've completed enough on the 1st tab):

I'll click on the 3rd tab called "Settings" then on the sub-tab "Image", and click on the button labeled "Choose Image":

I'll select "My Library" and click on the square with a plus sign on it to select images from my device:

Now I can select the image(s) I want to upload (unfortunately only 1 at a time):

Create an Image Table
At this stage, I'm going to create a table that holds the URLs of the images I want to use but if the image is hosted elsewhere, then this step is completely optional. Click on "Create" and select "New Table / Import Data":

One of the last options on the "Import Your Data" page is "Enter Data Right Away":

You'll be presented with a table of about 10 columns called "Untitled-1". I enter some labels in the first column. These will be keys later on to link my report to so keep them short without any special characters. I then saved this table with the name "Joels Image Library". I think I closed it and reopened it but now I want a 2nd column. So I click on the "Add" button and select "Add Column" > I called the column "ZohoAnalytics Image URL" and gave it the data type of "URL":

Now to copy over the image URLs, I duplicated my web-browser tab (right-click on the browser tab if using Google Chrome and select "Duplicate"). I then arranged the two browser windows side-by-side so that I could copy the image address from the library to the 2nd column in my data table. So in the duplicate browser, go to modify a widget again and get to the "Choose Image" > "Image Library" (as mentioned in earlier steps), then right-click on the image and select "Copy Image Address", and paste it into the cell alongside the status key in the data table:

Done all those. Now again this is completely optional, but I want to have the URL of the image alongside this 2nd column so I'm going to add a 3rd column to my data table which is simply a copy of what was in column 2:

In the textarea / multi-line box, and to the right of it, I click on the "ZohoAnalytics Image URL" value to copy it into the textbox:

Great! Now the next step makes the value into an image. Even if this is filled with URLs for images stored on remote websites, it helps to preview the images. Right-click on the header of the 2nd column and select "Format Column":

I changed the alignment to center, changed "Display As" to "Image", and set the image dimension to "64 x 64":

I should end up with something like this:

Create a query table with images
Irrespective of where the images are hosted, you can now write a SQL (more precisely an ANSI-SQL / ZohoSQL) query which is something like:

copy raw

SELECT
		 "Modified Time",
		 if(to_date("Modified Time")  > sub_date(to_date(now()), 1), 
		 	'https://analytics.zoho.eu/clientapi/imageLibrary/fetchImage?ZDBFILEID=12345000012345678&ZSOID=20240725001', 
			if(to_date("Modified Time")  > sub_date(to_date(now()), 5), 
				'https://analytics.zoho.eu/clientapi/imageLibrary/fetchImage?ZDBFILEID=12345000023456789&ZSOID=20240725001', 
				'https://analytics.zoho.eu/clientapi/imageLibrary/fetchImage?ZDBFILEID=12345000034567890&ZSOID=20240725001'
			)
		) AS "Status"
FROM  "Accounts" 
ORDER BY "Modified Time" DESC 

/****
Pseudo:

Select
	Modified Time of Record
	If  Modified Time is greater than yesterday's date (today)
		Use URL of the image representing OK
		Else If Modified Time is greater than 5 days ago
			Use URL of the image representing Warning
			Else 
				Use URL of image representing a FAILURE
***/

SELECT
"Modified Time",
if(to_date("Modified Time") > sub_date(to_date(now()), 1),
'https://analytics.zoho.eu/clientapi/imageLibrary/fetchImage?ZDBFILEID=12345000012345678&ZSOID=20240725001',
if(to_date("Modified Time") > sub_date(to_date(now()), 5),
'https://analytics.zoho.eu/clientapi/imageLibrary/fetchImage?ZDBFILEID=12345000023456789&ZSOID=20240725001',
'https://analytics.zoho.eu/clientapi/imageLibrary/fetchImage?ZDBFILEID=12345000034567890&ZSOID=20240725001'
)
) AS "Status"
FROM "Accounts"
ORDER BY "Modified Time" DESC
/****
Pseudo:
Select
Modified Time of Record
If Modified Time is greater than yesterday's date (today)
Use URL of the image representing OK
else If Modified Time is greater than 5 days ago
Use URL of the image representing Warning
else
Use URL of image representing a FAILURE
***/

But if I want it to join to my images table and with a bit more foresight (I plan to do a mega query to rule them all which has all the data for my widgets), I have to write it as such:

copy raw

SELECT
		 'CRM Accounts' AS "DataSource",
		 date(t1."ModifiedTime") AS "Date",
		 t1."RecordID" AS "Record ID",
		 t1."RecordName" AS "Record Name",
		 t1."RecordOwner" AS "Record Owner",
		 img."ZohoAnalytics Image URL" AS "Status Image"
FROM (	SELECT
			 acc."Id" AS "RecordID",
			 acc."Account Name" AS "RecordName",
			 acc."Account Owner Name" AS "RecordOwner",
			 acc."Modified Time" AS "ModifiedTime",
			 if(to_date(acc."Modified Time")  > sub_date(to_date(now()), 1), 
             	'Data OK', 
                if(to_date(acc."Modified Time")  > sub_date(to_date(now()), 5), 
                	'Data Warn', 
                    'Data Failed'
                )
             ) AS "Status"
    FROM  "Accounts" acc 
) AS  t1
LEFT JOIN "Joels Image Library" img ON t1."Status"  = img."Status Value"

SELECT
'CRM Accounts' AS "DataSource",
date(t1."ModifiedTime") AS "Date",
t1."RecordID" AS "Record ID",
t1."RecordName" AS "Record Name",
t1."RecordOwner" AS "Record Owner",
img."ZohoAnalytics Image URL" AS "Status Image"
FROM ( SELECT
acc."Id" AS "RecordID",
acc."Account Name" AS "RecordName",
acc."Account Owner Name" AS "RecordOwner",
acc."Modified Time" AS "ModifiedTime",
if(to_date(acc."Modified Time") > sub_date(to_date(now()), 1),
'Data OK',
if(to_date(acc."Modified Time") > sub_date(to_date(now()), 5),
'Data Warn',
'Data Failed'
)
) AS "Status"
FROM "Accounts" acc
) AS t1
LEFT JOIN "Joels Image Library" img ON t1."Status" = img."Status Value"

This produces something as follows:

Create a widget that shows an image
This step here will create a widget that will show the last image (or first image if ordered by modified time in descending order) in this status table. For simplicity, I'm going to select the first type of widget under "1. Select Widget"; then under "2. Select Columns", I'm going to set "Data Column" to display the Maximum Date; then I want to "Group By" the image column, here labeled "Status Image":

Then on the 2nd tab, I'm going to filter by my dataSource, which I've selected here to be my "CRM Accounts":

Click on the 3rd tab "Settings" and the preview should automatically display the image alongside the data. I'm going to the sub tab "Values" and change the displayed label to a constant text set to "CRM Account Data":

I then click on the "image" sub tab and change the "Image Position" so that the image is under the text (personal preference here and completely optional):

So now I can go back to my query and expand on it a little to include the other datasources (CRM Tables):

copy raw

SELECT
		 'CRM Accounts' AS "DataSource",
		 date(t1."ModifiedTime") AS "Date",
		 t1."RecordID" AS "Record ID",
		 t1."RecordName" AS "Record Name",
		 t1."RecordOwner" AS "Record Owner",
		 img."ZohoAnalytics Image URL" AS "Status Image"
FROM (	SELECT
			 acc."Id" AS "RecordID",
			 acc."Account Name" AS "RecordName",
			 acc."Account Owner Name" AS "RecordOwner",
			 acc."Modified Time" AS "ModifiedTime",
			 if(to_date(acc."Modified Time")  > sub_date(to_date(now()), 1), 
             	'Data OK', 
                if(to_date(acc."Modified Time")  > sub_date(to_date(now()), 5), 
                	'Data Warn', 
                    'Data Failed'
                )
             ) AS "Status"
    FROM  "Accounts" acc 
) AS  t1
LEFT JOIN "Joels Image Library" img ON t1."Status"  = img."Status Value"  

UNION ALL

SELECT
		 'CRM Contacts' AS "DataSource",
		 date(t2."ModifiedTime") AS "Date",
		 t2."RecordID" AS "Record ID",
		 t2."RecordName" AS "Record Name",
		 t2."RecordOwner" AS "Record Owner",
		 img2."ZohoAnalytics Image URL" AS "Status Image"
FROM (	SELECT
			 con."Id" AS "RecordID",
			 con."Full Name" AS "RecordName",
			 con."Contact Owner Name" AS "RecordOwner",
			 con."Modified Time" AS "ModifiedTime",
			 if(to_date(con."Modified Time")  > sub_date(to_date(now()), 1), 
             	'Data OK', 
                if(to_date(con."Modified Time")  > sub_date(to_date(now()), 5), 
                	'Data Warn', 
                	'Data Failed'
                )
             ) AS "Status"
	FROM  "Contacts" con 
) AS  t2
LEFT JOIN "Joels Image Library" img2 ON t2."Status"  = img2."Status Value"  

UNION ALL

SELECT
		 'CRM Deals' AS "DataSource",
		 date(t3."ModifiedTime") AS "Date",
		 t3."RecordID" AS "Record ID",
		 t3."RecordName" AS "Record Name",
		 t3."RecordOwner" AS "Record Owner",
		 img3."ZohoAnalytics Image URL" AS "Status Image"
FROM (	SELECT
			 opp."Id" AS "RecordID",
			 opp."Deal Name" AS "RecordName",
			 opp."Deal Owner Name" AS "RecordOwner",
			 opp."Modified Time" AS "ModifiedTime",
			 if(to_date(opp."Modified Time")  > sub_date(to_date(now()), 1), 
             	'Data OK', 
                if(to_date(opp."Modified Time")  > sub_date(to_date(now()), 5), 
                	'Data Warn', 
                	'Data Failed'
                )
             ) AS "Status"
	FROM  "Deals" opp 
) AS  t3
LEFT JOIN "Joels Image Library" img3 ON t3."Status"  = img3."Status Value"  

UNION ALL

SELECT
		 'CRM Meetings' AS "DataSource",
		 date(t4."ModifiedTime") AS "Date",
		 t4."RecordID" AS "Record ID",
		 t4."RecordName" AS "Record Name",
		 t4."RecordOwner" AS "Record Owner",
		 img4."ZohoAnalytics Image URL" AS "Status Image"
FROM (	SELECT
			 mtg."Id" AS "RecordID",
			 mtg."Title" AS "RecordName",
			 mtg."Host Name" AS "RecordOwner",
			 mtg."Modified Time" AS "ModifiedTime",
			 if(to_date(mtg."Modified Time")  > sub_date(to_date(now()), 1), 
             	'Data OK', 
                if(to_date(mtg."Modified Time")  > sub_date(to_date(now()), 5), 
                	'Data Warn', 
                	'Data Failed'
                )
             ) AS "Status"
	FROM  "Meetings" mtg 
) AS  t4
LEFT JOIN "Joels Image Library" img4 ON t4."Status"  = img4."Status Value"  

UNION ALL

SELECT
		 'CRM Tasks' AS "DataSource",
		 date(t5."ModifiedTime") AS "Date",
		 t5."RecordID" AS "Record ID",
		 t5."RecordName" AS "Record Name",
		 t5."RecordOwner" AS "Record Owner",
		 img5."ZohoAnalytics Image URL" AS "Status Image"
FROM (	SELECT
			 tsk."Id" AS "RecordID",
			 tsk."Subject" AS "RecordName",
			 tsk."Task Owner Name" AS "RecordOwner",
			 tsk."Modified Time" AS "ModifiedTime",
			 if(to_date(tsk."Modified Time")  > sub_date(to_date(now()), 1), 
             	'Data OK', 
                if(to_date(tsk."Modified Time")  > sub_date(to_date(now()), 5), 
                	'Data Warn', 
                	'Data Failed'
                )
             ) AS "Status"
	FROM  "Tasks" tsk 
) AS  t5
LEFT JOIN "Joels Image Library" img5 ON t5."Status"  = img5."Status Value"

SELECT
'CRM Accounts' AS "DataSource",
date(t1."ModifiedTime") AS "Date",
t1."RecordID" AS "Record ID",
t1."RecordName" AS "Record Name",
t1."RecordOwner" AS "Record Owner",
img."ZohoAnalytics Image URL" AS "Status Image"
FROM ( SELECT
acc."Id" AS "RecordID",
acc."Account Name" AS "RecordName",
acc."Account Owner Name" AS "RecordOwner",
acc."Modified Time" AS "ModifiedTime",
if(to_date(acc."Modified Time") > sub_date(to_date(now()), 1),
'Data OK',
if(to_date(acc."Modified Time") > sub_date(to_date(now()), 5),
'Data Warn',
'Data Failed'
)
) AS "Status"
FROM "Accounts" acc
) AS t1
LEFT JOIN "Joels Image Library" img ON t1."Status" = img."Status Value"
UNION ALL
SELECT
'CRM Contacts' AS "DataSource",
date(t2."ModifiedTime") AS "Date",
t2."RecordID" AS "Record ID",
t2."RecordName" AS "Record Name",
t2."RecordOwner" AS "Record Owner",
img2."ZohoAnalytics Image URL" AS "Status Image"
FROM ( SELECT
con."Id" AS "RecordID",
con."Full Name" AS "RecordName",
con."Contact Owner Name" AS "RecordOwner",
con."Modified Time" AS "ModifiedTime",
if(to_date(con."Modified Time") > sub_date(to_date(now()), 1),
'Data OK',
if(to_date(con."Modified Time") > sub_date(to_date(now()), 5),
'Data Warn',
'Data Failed'
)
) AS "Status"
FROM "Contacts" con
) AS t2
LEFT JOIN "Joels Image Library" img2 ON t2."Status" = img2."Status Value"
UNION ALL
SELECT
'CRM Deals' AS "DataSource",
date(t3."ModifiedTime") AS "Date",
t3."RecordID" AS "Record ID",
t3."RecordName" AS "Record Name",
t3."RecordOwner" AS "Record Owner",
img3."ZohoAnalytics Image URL" AS "Status Image"
FROM ( SELECT
opp."Id" AS "RecordID",
opp."Deal Name" AS "RecordName",
opp."Deal Owner Name" AS "RecordOwner",
opp."Modified Time" AS "ModifiedTime",
if(to_date(opp."Modified Time") > sub_date(to_date(now()), 1),
'Data OK',
if(to_date(opp."Modified Time") > sub_date(to_date(now()), 5),
'Data Warn',
'Data Failed'
)
) AS "Status"
FROM "Deals" opp
) AS t3
LEFT JOIN "Joels Image Library" img3 ON t3."Status" = img3."Status Value"
UNION ALL
SELECT
'CRM Meetings' AS "DataSource",
date(t4."ModifiedTime") AS "Date",
t4."RecordID" AS "Record ID",
t4."RecordName" AS "Record Name",
t4."RecordOwner" AS "Record Owner",
img4."ZohoAnalytics Image URL" AS "Status Image"
FROM ( SELECT
mtg."Id" AS "RecordID",
mtg."Title" AS "RecordName",
mtg."Host Name" AS "RecordOwner",
mtg."Modified Time" AS "ModifiedTime",
if(to_date(mtg."Modified Time") > sub_date(to_date(now()), 1),
'Data OK',
if(to_date(mtg."Modified Time") > sub_date(to_date(now()), 5),
'Data Warn',
'Data Failed'
)
) AS "Status"
FROM "Meetings" mtg
) AS t4
LEFT JOIN "Joels Image Library" img4 ON t4."Status" = img4."Status Value"
UNION ALL
SELECT
'CRM Tasks' AS "DataSource",
date(t5."ModifiedTime") AS "Date",
t5."RecordID" AS "Record ID",
t5."RecordName" AS "Record Name",
t5."RecordOwner" AS "Record Owner",
img5."ZohoAnalytics Image URL" AS "Status Image"
FROM ( SELECT
tsk."Id" AS "RecordID",
tsk."Subject" AS "RecordName",
tsk."Task Owner Name" AS "RecordOwner",
tsk."Modified Time" AS "ModifiedTime",
if(to_date(tsk."Modified Time") > sub_date(to_date(now()), 1),
'Data OK',
if(to_date(tsk."Modified Time") > sub_date(to_date(now()), 5),
'Data Warn',
'Data Failed'
)
) AS "Status"
FROM "Tasks" tsk
) AS t5
LEFT JOIN "Joels Image Library" img5 ON t5."Status" = img5."Status Value"

Repeating the above for a few more widgets (or cloning the first widget and then changing the filter and heading label), I now have a dashboard that has the following: