Standard & Poor's publishes the Case-Shiller® Home Price Indices on the last Tuesday of every month. The indices are calculated from data on repeat sales of single family homes. The indices are normalized to have a value of 100 in the first quarter of 2000. It is calculated monthly using a three-month moving average and published with a two month lag. We imported the data released today into Zoho Reports and created a few charts to see how the house prices have performed over the years.

There's also a separate chart that plots the Composite 10 and Composite 20 (an aggregate of 10 and 20 cities respectively) index values.


Inferences from the above charts

• West
  • Los Angeles, followed by San Diego are the cities where the prices peaked much (indices of around 275 and 250 respectively)
  • In Seattle and Portland, the prices peaked almost a year late when compared to other cities in the West (around Sep 2007 as against Sep 2006)
  • There was a mini bubble in Los Angeles in 1989-1990
    
• Midwest
  • Chicago and Minneapolis have seen the maximum rises
  • In Detroit, prices have fallen the most (given the state of the auto industry there)
    
  • Prices in Cleveland have almost remained level (just went up by 20 points)
    
• Northeast
  • During the bubble years, New York's prices rose at a faster rate than Boston's (from 1999 to 2004, prices rose almost at the same rate in both the cities)
  • In the 1987-1996 time period, prices haven't varied much in the two cities
    
• South
  • Prices rose the most in Miami, followed by Washington DC and Tampa
  • Like with Cleveland in the Northeast, prices in Charlotte, Dallas & Atlanta didn't rise by much
  • There was a brief bubble in 1989-90 in Washington, DC
• General
• The indices for many of the cities are showing a small uptick for April 2009
• Almost every region in the US has got uniformly affected by the current housing bubble
  

Do let us know your own inferences from the above. The data is available here. Play with it and create as many charts as you like. For example, the below is an interactive chart of how the indices in the Midwest cities have been since Jan 2001.


We will be updating the data on the last Tuesday of every month. The charts embedded above being dynamic will change automatically.

Related Links

• Screencast - How to Import data into Zoho Reports
  
• Screencast - How to Create Charts in Zoho Reports
  

We posted about enhancements done to the pivot table recently in Zoho Reports. Here's more. Pivot tables (and charts, filters) now support quarterly and weekly intervals. This adds to yearly, monthly intervals before. This can be best explained in the sales context. What if you want to know how you did in 1Q 2009 Vs 4Q 2008 (previous quarter) or 1Q 2008 (previous year, same quarter)? Or what if you want to know how you did this week compared to last week or see the trends of the earlier weeks? It is easy now to have reports for such scenarios.



The screenshot above is for the Actual Values - Quarter & Year, sales in various quarters of each year. Seasonal/Cyclic - Quarter option differs from this as it will give the sum of Quarterly Sales summed across years.



Similarly, the Weekly option is available too. Sales figures of different regions for the various weeks in 2008 shown as a Pivot table.



The same quarter & week concepts are available in Charts and Summary Views.




You can also Filter the data using Quarter and Week timelines.



Try the new Quarterly & Weekly options in Zoho Reports and let us know your feedback in the comments.

Related Links

• Screencast : How to create a Pivot in Zoho Reports
• Screencast : How to create a Chart in Zoho Reports
• Sales Analysis with Zoho Reports
  

As you may know, you can share the reports and tables with your colleagues & contacts in Zoho Reports, your effective online reporting & business intelligence service. And this makes collaboration a lot easier. Zoho Reports now provides fine-grained permission options while sharing. When you share a report or a table, you now have the choice to specify what can be done with the table/report by the user to whom its shared. Let's illustrate this with an example.

The following example consists of US GDP data (in actual and chained year 2000 dollars) over the years. For sharing the chart, click on Share -> Share this View to New Users.


In the dialog box that opens, give the email addresses of those you want to share the report with.


Now comes the exciting part. In the Permissions tab, you can specify what the shared users can do with the report you share with them. Whether they can Export the report's data and whether they can view the underlying data etc.


Similarly, there are permission options when sharing a table too. Like you can specify whether a table's data can be exported or modified (appending or modifying rows etc).


Supposing you want to share a number of tables/reports, you can do so from the Share option in the Explorer view. Check the Tables and Reports that you want to share, click on Share -> Share this View to New Users and choose the appropriate permission options.


Whether the sharing notification email is to be sent can be specified too.


Also, such fine-grained permissions can be provided when sharing with the Database Group. You can define a Database Group consisting of a set of email addresses. This feature comes in handy when sharing reports & tables to the same set of users (than sharing to the individual email addresses each time).


Try the new fine-grained sharing permission options in Zoho Reports & do let us know your feedback.

Related Links

1. Dynamic Chart Filtering and URL Data type in Zoho Reports
2. Zoho Reports : Pivot Table Enhancements
3. Quickly Generate Similar Reports in Zoho Reports
   

The latest update of Zoho Reports brings in dynamic filter in live charts and support for URL (HyperLink) as a datatype.

Dynamic Filtering in Live Chart     

Say you are analyzing how the various campaigns of your Google Adwords account have performed using your Adwords Reporting database in Zoho Reports. Let's say you have three campaigns for mountain bikes, one each for the Europe and the US and one for other countries/continents. And you want to look at the data from Europe and the USA alone. You can mouse over on the chart legend and uncheck that data alone.


Zoho Reports hides that particular data and will dynamically regenerate the chart with the remaining data.



You can click on the same checkbox again to see the hidden data back.



You can hide multiple data lines too. For example, you can see the data for the US alone (hiding Europe and Others) by clicking on the yellow checkbox as shown below.





The dynamic filter functionality is available on charts that are embedded in any of your web applications or blogs too. Embedding the chart used in the above screenshots below. Go ahead, click on the checkboxes and try the functionality yourselves.

URL Data Type

Zoho Reports now supports URL (hyper links) data type. You can now set a column data type as URL and store accessible web URL addresses in those columns. When you view the data, Zoho Reports automatically places a HTML link sign besides URLs, clicking on which will open the URL in a new browser tab.



Like what you see and interested in knowing more about how Zoho Reports can help you analyze your business data? Contact us for a personalized demo.

Related Links

• Measure your Google Adwords Campaign Performance with Zoho Reports
• See how Zoho Reports compares with a spreadsheet like Microsoft Excel

The latest Zoho Reports update brings in the 'Query over Query' functionality, which has been one of the top request from our users. You can now have SELECT queries that combine already existing Query Tables. Let's see an example of how this comes in handy. Say you have a Sales table with sales figures for various customers, products, regions etc across years.



Say you would like to know who your Top 10 customers with respect to sales value were in 2006. Your query table would look like,



The above Query Table in 'View Mode' with the top 10 customers arranged in alphabetical order.



And now you want to know how much you sold to those top customers of 2006 in 2008. This is where the new functionality will come in handy. You will arrive with the required data by joining the base Sales table with the above top 10 customers of 2006 Query Table. The new Query Table will look like,



The above Query Table in 'View Mode' with the customer names in alphabetical order.



You can save the resultant query table and create the necessary reports over it.

With this new "Query over Query" feature, you can create very powerful reporting queries on your data and use it for your report generation and analytics. It also helps in organizing your queries into reusable functional units. To know more about how to create a Query Table, checkout this video.

Other Enhancements
Previously, direct import was restricted to CSV files. And you had to do a copy-paste from Microft Excel (XLS) files. From now on, you can directly import XLS files too.



Also, for xls, html files larger than 3 MB, you can now zip them and import into Zoho Reports.

Try these new features in Zoho Reports and let us know your feedback comments.

Related Posts

• Zoho Reports : Pivot Table Enhancements
• Relational Data Modeling in Zoho Reports
  
• Quickly Generate Similar Reports in Zoho Reports
  

There are instances when you want to generate the same type of reports repeatedly on some periodic data. Say for example sales report for each month or quarter, or quality analysis reports on test output generated periodically etc. In these scenarios you would have created variety of reports based on your needs. And to create such reports repeatedly for each new similar dataset can be a pain. Not anymore. Zoho Reports has come up with a new feature, Autogenerate Reports - Similar to Another Table, which you can use for easy report duplication.

Using this feature is simple. Let's say you have your business sales data for 4Q 2008. Having imported it into Zoho Reports, let's say you have created reports based on it. The reports will look like the below.
Now, you have sales data for the first quarter of 2009 and you want to have the same set of reports that you created originally (for 4Q 2008). Import the new data into Zoho Reports as a new table, Sales 1Q 2009.
Click on New and choose the Autogenerate Reports : Similar to Another Table option. Select the appropriate table - Sales 4Q 2008 - for report generation, and specify the folder where the newly generated reports are to be saved (in our case, let's say Sales 1Q 2009 Analysis).
Click OK and you are done. The reports you created for the initial set of data (4Q 2008) are generated for the subsequent data set too (1Q 2009).
Note : The above feature will only work on similar tables. That is, the new table should have the same column names along with the same data type as that of the original table. The new table can have a sub-set of the original table's columns too and in this case reports will be generated only for the columns present in the new table.

Report generation using Similar to Another Table option is in continuation to our efforts to make report generation easy and fun in Zoho Reports, as done by our Auto Analysis feature introduced recently. Still more to come.

Give this new feature in Zoho Reports a try and let us know your feedback in the comments or mail us at support at zohoreports dot com.

A nifty feature in Zoho Reports is the way it supports relational data modeling. You can create or import a reporting database consisting of multiple tables which have relationships defined between them as in a relational database.

In relational databases world, tables are related using Foreign Key relationships. In Zoho Reports, this is done using the Lookup Column feature where a column in one table points to a column in another table.

Relating tables with Lookup columns

Let me explain this by creating a sample Employee database. This Database consists of two tables Department and Employee. The Department table has two columns - Department Name and Department ID.

The Employee table has the Employee names and their respective Employee IDs. The Department to which each Employee belongs to is referred by the third column Department ID


We define the Department ID in the Department table as the column that is being looked up by the Department ID column of the Employee table. Click on the Edit Design button in the Employee table to define this relationship. Double-clicking on a cell in the Lookup Column lists the different tables & their columns. In our example, we choose the Department table's Department ID.


Joining Tables with Query Table

Database tables which are connected to each other through such relationships allow you to fetch data combining the related tables. In Zoho Reports you can combine the data in such related tables by creating a Query Table which contains a SQL Select Join query. For example, a query combining the Employee and Department tables can be made as shown below.


The example query above joins the Employee & Department tables, getting the department name mapped to each employee. Over the query table that you have created by joining the necessary tables, Zoho Reports allows you to create any type of reports for analysis and visualization.

Enforces Data Integrity using Cascade-on-Delete

To ensure that the integrity of the data is maintained when data rows get deleted from related tables, Zoho Reports supports Cascade-on-Delete feature. That is, when rows are deleted in a parent table, then all the corresponding rows in the child table will be deleted automatically. In the above example, if any department gets deleted in the Department table (parent table), then all the employees will get deleted automatically from the Employee table (child table). For example, let's say the Accounts department (row) is deleted.


This will result in the corresponding rows in the Employee table getting deleted.


Hope you find these relational modeling features in Zoho Reports useful. Give it a try and let us know your feedback in the comments or mail us at support at zohoreports dot com. We have planned for a lot more enhancements on relational modeling in the future. Stay tuned!

Pivot Tables or Pivoting is a great way to analyze your business data. Zoho Reports has added further power to its pivot function. Salient points include,

• Support for adding multiple data columns (you could have only one column before). This is very useful for doing comparative data analysis
• Sorting support at any column level
• Viewing the underlying data of any data-point in the pivot
  

Let's see them in detail.

Support for adding Multiple Data / Columns

Pivot Tables allow you to create a powerful view with data summarized in a grid both in horizontal and vertical columns (also known as Matrix Views or Cross Tabs). Earlier, you could only plot single data column pivots in Zoho Reports. Like the one below.


Now, Zoho Reports allows you to have multiple data columns, which enables you to do comparative data analysis. Also now you can add multiple column level grouping in a pivot which was not possible previously. Below is a pivot showing sales & profit figures (multiple data) for different regions over the years (multiple columns).


Sorting Data on any Column

Zoho Reports now lets you sort data on any column. You can right-click on any column and apply the appropriate sort option.


View Underlying Data

Want to drill down into what each cell in the pivot table / matrix table represents? Say you want to know who bought Office Chairs when and for how much. You can click on the relevant data point in the pivot table and see the underlying data.


A sample pivot table (URL for the pivot) is embedded below.



The above features show how powerful pivot tables in Zoho Reports are when you have to analyze and cut through your data in a meaningful way. Give the new pivot table features in Zoho Reports a spin and let us know your feedback.

With layoff news all over the place, I thought I'd do little analysis on the layoff data. I spent around 30 minutes creating a simple Layoff Tracker Application using Zoho Creator & Zoho Reports. Here is the application I ended up creating. The interesting part is, how this was done. Let me explain on how this was done.


To get started, I created a simple Zoho Creator application pasting the data from TechCrunch Layoff Tracker which took me couple of minutes to place the data into Zoho Creator's database. To do some analysis, Zoho DB & Reports is the perfect tool. So I decided to connect Zoho Creator & Zoho DB.


Recently, we rolled out the first cut of our integration between Zoho Creator and Zoho DB & Reports. I used this feature to connect both these applications. I created a database in Zoho DB & Reports and linked it to the data form Zoho Creator App and set it up to frequently update itself every hour keeping Zoho Creator as the Master DB. This way, Zoho Creator's forms can be used to input the data directly into the Zoho Creator database which is then synchronized withZoho DB & Reports.

Once I have the database synchronized, I then generated several reports dragging and dropping columns based on the data in Zoho DB (which is automatically fetched from Zoho Creator). Here are some of reports.

Layoffs By Date

Layoffs By Month & Location
Layoffs Trend

Using the 'Publish' option in Zoho DB and HTML View option in Zoho Creator, I embedded these reports back in Zoho Creator Application in another tab. This is the final app with Reports tab displaying reports from Zoho Reports.

Zoho Creator Application
Public Zoho DB