Zoho Reports (@VGSoM) - A Tutorial

Tutorial

I. About Zoho Reports

Zoho Reports is a cloud-based Online Reporting and Business Intelligence service that helps to easily analyze business data and create insightful reports & dashboards for informed decision-making.

It is easy to use, provides a user-friendly drag-and-drop user interface. It also offers a wide variety of reporting options like dashboards, charts and tabular reporting options that help you do your business intelligence analytics easily. It also provides advanced features such as - Pivoting (or Crosstabs), querying using SQL in all major database dialects, filtering, drill-down and extensibility with Application Programming Interface.

Being a cloud-sourced application, it requires no hardware or software installation. Also, it can easily be accessed with a browser and an internet connection, anytime, anywhere.

I. a.      Why Use Zoho Reports?

• Online Reporting & Business Intelligence service accessible anytime and anywhere.
• Its interface is easy to adopt and 'Spreadsheet-like' for data addition and report creation.
• Provides drag & drop based reporting features for quick report creation and publishing.
• Supports data import from variety of file formats including Excel, Open Office, CSV, TSV and HTML. Also supports periodic scheduling of import.
• Enables real-time collaboration by sharing data and reports in read or read-write modes with your friends and colleagues.
• Reports can be exported and printed in variety of formats.
• Reports can be embedded within a Websites, Web Applications and Blogs.
• Zero maintenance cost.

    I. b.     Comparison with Excel and other spreadsheet

Excel and other desktop based applications lack some features which Zoho reports have. Some of them are:

·         Collaboration: Inherently designed for personal use and for single-user access at a time. As a spreadsheet gets emailed along, each user tend to have their own duplicate & possibly conflicting copies.

·         Maintenance: Difficult to maintain if the data is collected from multiple sources. Manual data entry adds to the woes.

·         Data Integrity: As time passes, spreadsheets become error prone. It is impossible to see the 'single version of truth' (data integrity) realized, especially when multiple users are involved.

·         Accessibility: Being a desktop-based application, anytime/anywhere access is not possible.

·         Functionality: Static reports with no way to drill down means that the potential to do a complete data analysis is lost.

·         Data Security: Being available locally in user machines, spreadsheets are prone to theft and data loss. Also, different access privilege can't be set for different users.

·         Querying Capability: Merging data across spreadsheets is a complex task. It is not easy to create and maintain.

·         Relating Data: Very hard to associate relationship between data which can naturally be linked. It becomes more complex if the data is from multiple sources.

·         Scalability: Working with large datasets will mean sluggish responsiveness, since all the processing is done by your local desktop.

·         Publishing Data: Publishing of data and reports in your intranets, blogs, websites or applications is not possible.

II. Getting Started With Zoho Reports

II. a.      Creating a Login ID

Signing up with Zoho Reports is free of cost. There is a simple form that helps you to select a username and a password for your every login and provide a valid Email ID for activating your account. Upon successful activation you will be able to login to all of the Zoho apps (such as – Creator, CRM, Docs, Notebook and others) with your account.

You can also choose to login to Zoho Reports using your accounts on these websites:

·         Facebook

·         Google

·         Google Apps

·         Yahoo

II. b.     Creating a new database

Zoho Reports organizes all the information created into logical entities called Databases. A database is a logical grouping of datasets (stored in entities called Tables) and all the reports created over the same. The database also contains structural information on how the data (table) is organized, and offers users ways for filtering, editing and reporting on that data.

You will be the administrator of the database created by you. Only you can grant others access to your databases or choose to keep it for private use.

The first page (Home page) of your Zoho Reports account shows several ways you can use to create a new reporting database. It also gives you a list of databases owned by you and shared to you.

Account Home Page

II. c.      Importing data from various sources

The Home page offers several means that can be used to create a database:

·         Creating a blank database

·         Manually entering data

·         Importing Excel, CSV, HTML content, Google Docs

·         Creating database from already available templates

·         Importing data from database local to the machine/network

Let us explore these options one by one:

(i)                  Creating a blank database

This option is used when you only want to create a blank database and would like to add tables later. Follow these steps:

o   Click on the Create Blank Database link.

o   Provide the database name (mandatory and should be unique)

o   Provide Description and Tags (optional)

o   Click Create to create the database.

Creating a Blank Database

(ii)                  Manually entering data

Click “Enter data right away” to create a new reporting database by directly entering data.

·         First, you will be prompted to Enter Database Name, its Description (optional) and Tags( optional).

·         Provide this information and click on “Create”.

·         You will be taken to a blank table in your database where you can start entering data in rows and columns.

(iii)                Import Excel, CSV, HTML content, Google Docs

This option is ideal when you already have a database in specified formats that you want to use with Zoho. You only need to upload the files in order to create your database which will be exactly similar to the one you have in your file. The interface is quite intuitive and easy to use.

There are three broad steps to importing data using this option:

·         Step 1: Choose the File Type to Import

·         Step 2: Specify the Import Settings

·         Step 3: Import and Import Summary

(a)    Step 1: Choose the File Type to Import

·         Provide a Database Name (mandatory), its Description and tags.

·         Select the type of file that you want to import from the “File Type” drop-down.

Importing supported file types

·         Select the file location. You can choose from three options –

o   1. Paste the data

o   2. Provide a location from local drive

o   3. Provide a Web URL.

·         Adhere to the notes and data size restrictions provided and click “Next”.

(b) Step 2: Specify the Import Settings

In this step, you can provide the necessary settings for Zoho Reports to properly understand the data being imported.

·         You can specify whether or not the first row of imported data contains column headers.

·         In the “Preview” section, you can visualize how your data will look like upon being imported.

·         Here, you can also (de)select columns in your data that you may or may not want to import.

(c) Step 3: Import and Import Summary

Once Zoho Reports imports the input data provided, it will bring up an Import Summary Dialog box, capturing the status of the Import process just completed.

(iv)                  Create a database from already available templates

Use this option when you want to use some of the database templates already available with Zoho Reports. You’ll be prompted to provide a Database Name (mandatory) and to choose a template based on your requirements. The template description will help you select a template of your choice. You may also select to populate the selected template with sample data.

(v)                Importing data from database local to the machine/network

Use this option in case you wish to import data from your locally hosted MS Acccess, MySQL, Microsoft SQL Server,  Sybase, PostgreSQL and Oracle database. An easy to use Upload Tool (Graphical User Interface) is provided that can be used to upload data from the supported databases to Zoho Reports. The GUI is available for Windows and Linux platforms. The Upload Utility GUI looks like:

Upload Utility GUI

           II. d.     Chart Creation

Once you have the data in the Zoho Reports, charts of various kinds can be created. Zoho Reports provides a easy to use drag and drop interface to create charts fast & easy. You can create various types of charts including Bar, Line, Pie, Stacked, Scatter, Funnel, Web and 3D charts by simply dragging and dropping the required columns in to the respective Shelves(X-axis, Y-axis, Color, etc.) in the design area. It is also easy to change the chart type any time dynamically without recreating the chart. You can also apply the appropriate aggregate functions on the columns to calculate and summarize data the way you want it.

Zoho Reports allows you to apply aggregate/categorical functions like Sum, Count, Average, Min, Max, etc., on the data columns to group and summarize data in charts. When you apply a function on a column, a single value will be returned, derived based on the values in the column. The default function for a Numeric (including Currency) data type is Sum and for a Date data type is Year. If the data type of the column is a string (Categorical/Dimension column) and not numeric, the default function applied is Actual Values.

To change the default function applied, after dropping the column select the required function (aggregate/categorical) from the drop-down list present on the column. Drop-down list displays all the applicable functions based on the data type of the column, as shown in the screen-shots below:

II. e.     Sharing and Publishing results

Sharing: Zoho Reports offers a very easy and effective way to collaborate with your colleagues and friends. Any user to whom you share your data, reports and dashboards can access them online live from anywhere, anytime. You can also create groups for easier sharing and collaboration. 

Publishing: Zoho Reports offers a wide range of publishing options to enable easy distribution and collaboration. You can publish the reports and dashboards that you create in Zoho Reports as live embedded reports in your websites/webpages or as iGoogle Gadgets or as easy to access URLs to your users/colleagues. While using the publishing options you could still control the access privelages and permissions provided to the users who access the reports or dashboards published.

Advantages of Publish feature

·         You can publish any data, reports or dashboards that you create in Zoho Reports.

·         Embed Reports & Dashboards Anywhere:  Embed your live reports and dashboards across Websites, Blogs and Applications for wider consumption.

·         Access Reports & Dashboards from iGoogle home page: Publish your reports and dashboards as widgets into your iGoogle Home page for every day access.

·         Easy to Access Bookmarkable URLs: You can generate easy to access standalone URLs (Permalinks), which can even be bookmarked, for accessing the reports and dashboards by your users.

·         Fine grained permission control: You can set fine-grained permissions while publishing the reports to users, such that they would be allowed to do only the permitted operations, like read, write, export etc., when they access the reports published. 

·         Highly Secure: While publishing the views you can specify whether it requires a user to login into his/her Zoho Reports account for access (private access) or can it be accessed without login into Zoho Reports (public access). This is apart from the fine grained permissions that you can provide for the view while publishing.

·         Single Version and the latest always: Any data or report or dashboard published when accessed by users, will always see the latest live version and nothing old.

III.      Live Examples – Showcase your Social Networking Quotient

In this section, we will take you through certain hands-on examples that demonstrate how you can transform your social network statistics into impressive and compelling charts in order to showcase your "Social Networking Quotient". We will demonstrate 3 examples show how all these can be linked up and presented in Zoho Reports:

III. a. Your LinkedIn Connection Statistics using Zoho Reports

LinkedIn stats can be presented in a powerful manner using Zoho Reports. Currently, LinkedIn does not provide option to export all contact related data in an easily usable format say csv. The basic ‘export connections’ would just extract the name of the person, current job and email ID into a csv file. So, we use a freeware available on the web called ‘LinkedIn Contact Manager’ (LICM) to get all contact related data into a single spreadsheet and then import this data into Zoho Reports for visualization

The following steps are followed:

Step 1: Export only the name and emails of all your contacts into a csv file by logging into your LinkedIn profile.

The LinkedIn address book export to a csv file can be done by visiting the following link:

http://www.linkedin.com/addressBookExport. Save the file as ’email_contacts.csv’

Step 2: Get the freeware app “LinkedIn Contacts Manager” from the following website: http://www.megaas.com/ Install it on the PC. Log in to your LinkedIn profile from the application.

Step 3: Follow the instructions on the app page to get all the contacts’ data except for the emails. Additionally, the application offers to merge the contents of an email csv. Use the ‘email_contacts.csv’ to merge the data to get a complete list as shown below:

Step 4: Copy all the data into a new Zoho Reports database by directly pasting the data into the new data chart. Exclude columns which are unnecessary and give appropriate titles to the columns. This is how the data would look like:

Step 5: Start creating charts with the data which are appropriate. For example: the ‘number of contacts by location’ is a very important statistic for any HR manager. For a consultant who wants to offer services, ‘the number of contacts by industry’ could be a very important metric.

Just drag and drop the columns in the relevant x-axis and y-axis to get the charts. One unique feature of Zoho reports is that the filters can be set both by the chart preparer as well as the end-user looking at the data. The chart will change dynamically based on the filters applied by the user.

Step 6: Once the desired charts are created, it can be shared with any users or published on a blog page and your websites.

III. b.      Your Twitter Follower Statistics using Zoho Reports

If you are a Twitter buff, you know how you can utilize its power to express your thoughts and interact with people and organizations you care about. The vibrancy of your Twitter network is often estimated by your follower count and your Tweet counts. These statistics of a Twitter user are readily available for anyone to see. What is often overlooked is the quality of your follower network which remains visible only to the user.

Following this hands-on example, you can create reports of your Twitter Followers’ statistics and publish it on the Web to showcase the strength of your Twitter network.

With this example, you can –

1.       Assemble data regarding your Twitter followers (using a service called http://www.twittersave.tk/)

2.       Import this data into Zoho Reports and create insightful reports to demonstrate your Twitter Followers, Followers’ followers, Followers’ Tweet Count.

3.       Publish these reports on the Web. The reports will be dynamic and will change with updates in your Twitter follower statistics.

There are 4 broad steps to achieving this:

         i.            Generate your Twitter Follower Data

       ii.            Import the data into Zoho Reports

      iii.            Create impressive reports

     iv.            Publish the reports on your Webpage (Blog/iGoogle/Website) for branding

Step – I: Generate your Twitter Follower Data

We will use an application called http://www.twittersave.tk/ to generate data about your Twitter followers. The application is available free of cost.

Follow the steps given below to generate your follower data:

·         Go to: http://www.twittersave.tk/

·         In the “Download details of your twitter followers” section, provide your Twitter username and click “Submit”. You can provide any username, whose follower data you want. Currently, only 5000 followers’ data can be downloaded.

·         You will be prompted to click on a download link.

·         Clicking on it will download a CSV file to your computer.

·         The CSV file contains the Twitter Followers’ data regarding his/her: Name, Location, Profile Image Link, Follower’s follower count, statuses count (Tweets’ count), Latest status.

   Step – II: Import the data into Zoho Reports

In this step, use the CSV file downloaded in Step-I and import it in a Zoho Reports database.

·         Follow the steps given in the Section 2.c (iii) to import the CSV file into your Zoho Reports database. Let’s name it as: “Twitter Follower Statistics”

·         In the preview section, select only the columns : name, location, follower_count and statuses_count and click “Create”.

·         Select “No” in Auto-generate reports pop-up.

Step – III: Create impressive reports

In this step, you have to create the reports that will be used further to publish on the Web.

You may want to follow the steps given in Section 2(d), for instructions to create Chart View reports. Below are some examples to generate some remarkable views:

·         On your Twitter Follower Statistics database page, go to: New > New Chart View.

·         For X-Axis, select the field “name” and for Y-Axis, select the field “followers_count”.

·         Change the Graph summary for “followers_count” to “Actual Values (Measure)” using the drop-down.

·         Click the link “Click here to generate graph”. Your graph will be displayed.

·         If some follower_count values are exceptionally high, you may want to filter them out as they make other followers data look insignificant. For this, go to “User Filter” tab, drag-and-drop “followers_count” field and select an appropriate range of followers that you wish to display. For this example, let’s take “followers_count” range of 100 to 1000.

·         You may wish to create more Charts combinations of your Follower statistics.

Step-IV: Publish the reports on your Webpage

In this step, you can publish the reports generate by you in the previous step on your Blog/iGoogle widget or Website.

An example report generate using these steps is displayed here: http://bit.ly/IcIstm.

The chart looks like:

III. c. Your Facebook Page insights using Zoho Reports

Facebook pages provide insights of the particular page to any admins of the page which is a nice visualization tool in itself. However, since this is limited only to the admins, these very same reports can be used by Zoho Reports to build insights which can be viewed by all or flaunted on the webpage of the product.

Step 1: Go to facebook page where you are the admin and import the insights data into a csv file. This can be done by setting the filter and using the ‘Export data’ button at the top right of the page.

Step 2: If the data seems very complex during inspection, you can clean up the data before importing it into Zoho Reports

Step 3: Create new database in Zoho Reports and import the data.

Step 4: Start creating charts with the data which are appropriate. Once all charts are created, a dashboard can also be created where all the charts can be dragged and dropped. A dashboard is a more powerful way to visualize data than individual charts as it gives interaction effects of various variables.

III. d.      Prospective Business Applications

The three examples shown in the previous sections can be used together to create 

There are several prospective business applications of the hands-on examples shown in this tutorial:

1.       For Self-Branding: This example is an excellent way to Self-Branding on your Blog/Website. You can use Zoho Reports in conjunction with other simple tools described here to generate impressive and insightful reports demonstrating your social network acumen. It may be of particular interest to job-seekers who want to showcase their network strength in order to have a favourable image in front of their prospective employers. You can also keep a tab on most active network connections (on the basis of their follower counts and status counts).

2.       For Human Resource Managers/Agents: This particular example can be a very handy tool for Personnel Managers/agents/consultants, who normally have thousands of professional and personal contacts over social networks. These professionals can utilise the techniques given here to exhibit their network strength in front of prospective clients.

3.       For Marketing Managers: Marketing Managers, especially those who are actively involved in executing their company/brands’ social media campaigns may find this tutorial really helpful.The Facebook Insights of a company page, Twitter and LinkedIn connection stats can be used to impressively market a company brand. Also, they can be used by middle-level managers to keep track of their social media campaigns and as dashboards for upper management.

IV. Other features

 1.       Scheduled Emails: This Zoho Report feature allows you to Email reports immediately or schedule them for periodic mailing.

2.       Scheduled Imports: You can schedule imports from Web URLs. Alternatively, you may even schedule imports from your local machine databases by using Zoho’s upload tool and setting up “Scheduled Tasks” using Windows’ Wizards.

3.       Multiple database owners: In a basic plan, upto two users can collaborate on the same database. For Enterprise users, this limit can go upto 50.

4.       Rebranding Zoho Reports with your own logo: In premium usage, you can use your company’s logo to brand the tables and reports with it instead of Zoho’s default logos.

V. Online References

1.     Zoho Reports Help Documentation: https://reports.wiki.zoho.com/

2.     Hands-on example output results: http://zohoreportoutput.blogspot.in

3.     Using Zoho Reports to analyze any RSS Feed: http://ibm.co/2nlgLC

4.     Twitter Follower Data Extraction Tool: http://www.twittersave.tk/

5.     LinkedIn Connection Manager: http://www.megaas.com/