Day 3 Session 22 SignIn Into Your Account To Signin to your Zoho Account navigate to the following link httpswwwzohocomreportsloginhtml Enter your user name and password and click the Sign ID: 776284
Download Presentation The PPT/PDF document " Introduction to Zoho Reporting" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1
Introduction to Zoho Reporting
Day 3 – Session 2.2
Slide2Sign-In Into Your Account
To Sign-in to your Zoho Account navigate to the following link:https://www.zoho.com/reports/login.html Enter your user name and password and click the “Sign Up Now!” button
Slide3considerations
To
access the
Zoho
Reporting website, you need the following items
:
Reliable internet
connection
Internet browser, such as Internet Explorer, Mozilla Firefox, Google Chrome
Slide4Zoho Reports Licenses - USERS
Zoho
reports is licensed based on the number of users and Rows(records
)
Who
is a User?
Anyone to whom you privately share your databases, tables(data), reports and dashboards for collaboration is considered a "User" in the
Zoho
Reports pricing model. A user is identified by his/her unique email address, with which their
Zoho
Reports account was created/registered.
Example:
Suppose
you subscribe to a
Zoho
Reports plan for 5
-
y
ou
can privately share the data/reports in your account and collaborate with 4 other
people.
When sharing reports within other organizations or
partners
, donors, etc
…
you have the ability to share reports without having to purchase licenses for these users
.
Slide5Zoho Reports Licenses - rows/records
What are rows/records?
In
Zoho
Reports, a row or record is defined in the same context as in a database. In simple terms, a database table consists of rows (records) and columns (fields). Each row in a table represents a set of related data and has the same structure. For example, in a table that represents "Employees", each row would represent a single employee record.
The number of rows shown in the pricing plan, is the sum of all rows/records stored across all your database tables in your
Zoho
Reports account. As a measure of size, you can assume 100,000 simple rows would be equivalent to 10MB or more in data size. However, this can vary based on the number of columns and the type of data.
Slide6Reporting challenges
A common challenge
of data
collection is how to aggregate, analyze and report on the data.
The most common
program used
is
MS Excel
,
which is not an ideal reporting tool;
if you need to link multiple sources of data it can take days if not weeks to organize data in a way that it can be reported.
Slide7ZOHO Database
Zoho
Reports organizes all the information into a logical collection of data called a Database. A database is a logical grouping of datasets (stored in Tables) and all the reports created. The database also contains structural information on how the data (table) is organized and offers users different ways to filter, edit and report on that data.
The user who creates a reporting database is the owner/administrator of the database. Only the administrator can delete the database created by him/her. Also he/she controls the access privileges of the database i.e., who can access, create reports and edit data in the database.
Slide8Importing Data
Click on “Import from File & Feeds” option
Slide9Importing Data LOCALLY
Specify the Table NameAdd a Description Select the File Type as “EXCEL”Select Data Location as “Local Drive”
Slide10OR… Get
the URL for a Data Feed
Select Form in iFormHold the “Shift” button on your keyboard and click on “JSON” for Feed type
Slide11Get the URL for a Data Feed
Paste the URL into a Text editorAdd a User Name and Password “&USERNAME=ICTuser##&PASSWORD=iForm@2015”Add Type of Import “&TYPE=TOP”
Note: USERNAME and PASSWORD URL parameters must be in Capital letters
Slide12&USERNAME=ICTuser##&PASSWORD=iForm@2015&TYPE=TOP
Slide13Importing Data from iFormBuilder
Now that we have all the necessary information we are ready to import data into Zoho Report tables. Click on “New Table” button as shown in screen shot below.
Slide14Importing Data
You should see a preview of the data you are importing
Slide15Importing Data
You can customize the Date format for Date type fields.Select “Retain Column Name” = Yes
Slide16Importing Data
Uncheck the columns which you do not require in your report
Slide17CREATE YOUR DATABASE!
Slide18CONFIRMATION
You should get a confirmation after successfully importing the data From this screen you can schedule a periodic import of the data. Click on “Would you like to schedule this import periodically?”
Slide19IFORM - Periodic Import
Select “How do you want to import?” “Add new, replace existing and delete missing records”.Select “ID” as the Key FieldSpecify the import frequency and the Notification frequency for failures.Click “Save”
Slide20Now you have imported a data feed
Note: Using these steps users should import all forms in a parent and child relationship and then re-join these when establishing relationships between tables later on
Slide21Reporting | FIVE Guiding Questions
Before
starting any report in
Zoho
try to answer these questions:
What indicators need to be reported on?
Best format
of
the report? [
C
harts/tabular ?]
What
are my
data sources
?
Do
I need parent
form only
or related
“child” files, as well?
What calculations
do I need?
Slide22Group work #1
Analyze the workshop registration data
How many participants per organization, broken down by sex
% breakdown of favorite colors in group
# of participants by sector, with the ability to filter by sex
Report
on # of support trips per region, broken down by organizations responding
Slide23Charts
Slide24CREATING A NEW CHART
Slide25CREATING A NEW CHART, Con’t
In Edit Design mode, you will notice all the columns of the selected table listed on the left side Column List pane. On the right hand side, you can see the Design Area with shelves to drop the columns and a preview area below to view the chart created.
Slide26CREATING A NEW CHART, Con’t
Drag and drop the required columns listed in the Column list pane into the respective shelves in the Graph tab. You can also Select the check box adjacent to each column listed to auto place the columns into the appropriate shelves. After dropping the columns click on the option Click here to Generate Graph to create the new chart.
Slide27Choosing Chart Types
Slide28Applying Filters
Slide29User Filters
Zoho
Reports allows you to include dynamic filtering
capabilities
in the reports view mode called
User Filters
.
User filters enables your users who access the report, to apply filters on the report data displayed using the filter columns exposed as part of User Filters. The filter columns included in User Filters can be displayed using a variety of display components like Drop Down boxes, Slider, Date range chooser etc., to suit your needs.
Slide30User Filters
To add a string (categorical) column as a User Filter, follow the instructions given below:Open your report (chart) in Edit Design Mode and then click User Filters tab in the Design Area.Drag and drop the required category (string) column which you want to add as a user filter in the User Filters tab as shown below.
Slide31User Filters
Hover your mouse over the dropped column and select Edit from the highlighted options.
Slide32User Filters
On clicking the Edit user filter option it opens a dialog box with options to edit the Filter Display Name and to Choose Component type for the user filter, as shown in the screen-shot below.
Slide33User Filters
Now when you publish your chart a user will have the ability to select the filter option that would be applied for this chart that dynamically refreshes data based on the user filter
Slide34Applying Functions on Columns
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 string (Categorical/Dimension column) and not numeric, then the default function applied is
Actual
Values
.
Slide35Pivot Tables
Pivot Tables allow you to dynamically rearrange, group and summarize data for easy analysis of large data sets. You can transform data in a table(s) into interactive and meaningful summaries easily by using intuitive drag and drop provided by
Zoho
Reports.
You
can also filter, sort and customize the appearance and content of your Pivot Table the way you want it, by using a wide range of options provided by
Zoho
Reports. For users who view/access the pivot tables,
Zoho
Reports offers a range of interactivity like Data Drill Down, User Filters, Sorting, Export etc.,
Slide36Creating a Pivot Table
To create a Pivot View, follow the steps given below:Open the reporting database containing the required data with which you want to create a Pivot Table.Click New Report button in the left top corner to open the Create New Report window.In the Create New Report window, click Pivot View (Matrix View). The Select Base Table dialog box opens as shown below.Use Select Base Table dialog box to choose the table on which you want to create the Pivot Table and then click OK.This opens a window in Edit Design mode:
Slide37Creating a Pivot Table
Slide38Creating a Pivot Table
Below is a brief description of each of the shelves in the Pivot tab
.
Columns:
Distinct data values present in the columns dropped in this shelf will appear as the column headers of your pivot table. You can also have multiple columns dropped in this shelf for summarization. When you add multiple columns, they will be displayed as categorized layers (group within a group) of data vertically.
Rows:
Distinct data values present in the columns dropped in this shelf will appear as the row headers of your pivot table. You can also have multiple columns dropped in this shelf for summarization. When you add multiple columns, they will be displayed as categorized layers (group within a group) of data horizontally.
Data:
Values of the columns dropped in this shelf will appear in the body of the pivot table. Values will be summarized based on the summary function that you select. Also it will be categorized to provide the appropriate summary value for each cell corresponding to the row and column value in the table. You can also have multiple columns dropped in the Data shelf for summarization.
Slide39EXAMPLE: Pivot Table
Lets use the following example: we want to create a pivot table showing the count of beneficiaries by Gender and Year of Birth.For Columns we added “sex” from the beneficiary members tableFor Rows we added “date_of_birth” and changed the function to show YearFor Data we want to count the number of individual beneficiaries
Slide40Dashboards
We have created a chart report and a pivot report. Lets put these two reports on a single page; we are going to create a Dashboard.
What is a Dashboard? A dashboard is a composition of multiple summary tables, charts, etc… that are accessible by a user without having to open each report separately.
Slide41Creating a Dashboard
To create a Dashboard follow the steps belowClick on “Explorer” tabClick on “New”Select “New Dashboard”
Slide42Creating a Dashboard
Slide43To add report(s) to a Dashboard, drag and drop your report from the left side into the design area.
Creating a Dashboard
Slide44To split columns into two sections click “Contract” option.
Creating a Dashboard
Slide45If you want to add free text or an image, add it by using “Add Text” or the “Add Image” options from the toolbar
Creating a Dashboard
Slide46You can also add “User Filter” options.To add User Filter for your dashboard click on “+Add User Filter” button
Creating a Dashboard
To add a specific column just drag and drop this column into the “Filter” section
Slide47You can customize your filter by hovering your mouse over the filter element and clicking “edit”
Creating a Dashboard
Slide48Relational Data Model
Now that we imported data from
iFormBuilder
we can start building reports and charts but before we can start
building
reports we want to setup relationships between our tables.
Slide49Relational Data Model
Why do we need to setup relationships between our tables?
Data in
iFormBuilder
is not stored in a flat format
We may want to relate Beneficiary Registration Records with Services Delivery records
Slide50Setting Up Relationships in Zoho Reports
Lets look at an example: we want to report on the members of a household. A household is composed of one or multiple member(s)
Located at
Household
Household Members
Belong to
Slide51Setting Up Relationships in Zoho Reports
In
a single Household you can have one or multiple member(s)The Parent_Record_ID informs you to which Household (Parent Table) the individual beneficiary (Child Table) belongs.
To join
tables
you will use “
ID
” from the parent table
(in this example “
Beneficiary
& Household Registration
”)
and join this to “
Parent_Record_ID
” in the “
Beneficiary Registration
”
subform
(e.g. dependent) table.
Slide52Setting Up Relationships in Zoho Reports
To setup relationships between tables in Zoho Reports follow the steps below:Open the Child table firstClick the “Edit Design” icon
Slide53Setting Up Relationships in Zoho Reports
Change the data type for Parent_Record_ID column to “Lookup Column”
Slide54Setting Up Relationships in Zoho Reports
Make sure to select the “Stop conversion and rollback the changes” option and click “OK”
Slide55Setting Up Relationships in Zoho Reports
Now select the Table and Column that this field is related to by double clicking under “Lookup Column”.
Beneficiary Household is related to the “Beneficiary & Household Registration”
parent
form.
Slide56Setting Up Relationships in Zoho Reports
Now a dialog box will come up as shown with the options to handle errors during conversion and to maintain data integrity.
Slide57Setting Up Relationships in Zoho Reports
Explanation of options
available in the
Design Modification Settings
dialog
box
:
If
error occurs on conversion?
Changing
a column to a Lookup could result in failures. There are a few options for actions to be taken in case an error occurs during Lookup column conversion. The options are
:
Set empty value for the column in the corresponding record: For every row that fails during conversion the corresponding lookup column value will be set to empty (NULL), if this option is selected
.
Stop conversion and rollback the changes: Choosing this option, if an error occurs during conversion the entire conversion will be aborted and the changes done will be rolled back. This way the table will retain the existing design & values without any lookup column being created and no changes done.
On Deleting the values in the Lookup (parent) column:
This
section provides options for action to be taken in the child table (table in which you define the lookup) when a record is deleted in the Parent table (the table which you are looking up).
Slide58Setting Up Relationships in Zoho Reports
After you set relationships in your table click on the “Save” button.
Slide59Setting Up Relationships in Zoho Reports
Once the relationship between parent and child table has been established, the contents of both tables should available for analysis when a new chart or table is built.
Slide60Practice: IN small groups or individually
Create a dashboard with the following information, with branding for CAFOD and Caritas Europa
How many support trips per disaster type, broken down by sex
Breakdown
of
support trips by region
# of participants by sector, with the ability to filter by favorite color