My first interactive report

Close your eyes and imagine for a moment that all the information you have is a big Excel spreadsheet that you want to do all kind of sorts, filters, calculations and charts.

All this is available under NexOne Office (GIMMI Web). This technology allows you to manipulate, extract and produce endless lists, reports and statistics from your data.

You will find below the procedure to follow in order to produce a simple report which, at first glance, may seem impossible to you.

Situation:

I want to know among the sales made over the last 24 months, the average number of days between the registration date and the sale date; and this grouped by the first 3 characters of the property's postal code in order to target my next campaign advertising.

Procedure:

• Selection of the basic report

The customization of a report is done from a basic report. Initially, select the report whose data most closely resembles what you want to extract and then customize this report.

For our example, go to Transactions> Reports (top right)" menu.
Click on the Detailed button to display the detailed list of transactions.

• Selection of columns to display

Click on Actions and then choose Select columns
The left side displays the columns that do NOT appear in the report, the right side displays the columns that appear in the report.

Select on the right side the columns you want to remove from the report and click on the '<' arrow to remove them
Select on the left side the columns you want to add to the report and click on the '>' arrow to add them.

Note: To do a multiple, selection press the Ctrl or Shift buttons during the selection.

For our example, let's leave the MLS #, property address information, listing date, transaction date and add the price sold. And you click on Apply. The report is generated with the columns you have just chosen

• Selection of filters

Clear existing filters from the base report by clicking on the "X" to the right of the filter.

1.Define a new filter: Keep only transactions from the last 24 months ...

In the Actions menu, select: "Filter".
Under the "Column" field, select the column: "Transaction date".
Under the "Operator" field, select: "Is in the last".
Under the "Expression" field, enter: "24" (for 24 months).
Select the "Month" time unit in the drop-down list to the right of the Expression field (for 24 months).
Click on Apply. A new filter "Transaction date is in the last 24 months" is added at the top of the interactive report. The data in the report now reflects this filter.

2.Establish a new filter: Keep only transactions that have a listing date ...

In the Actions menu, select: "Filter".
Under the "Column" field, select the column: "listing date".
Under the "Operator" field, select: "is not null"
Click on Apply. A new filter "Listing date is not null" is added at the top of the interactive report. The data in the report now reflects this filter.

3.Add a column representing the first three characters of the postal code

In the Actions menu, select: "Format> Compute".
This function is used as in Microsoft Excel to create a formula from existing data
Under “Column Label”, enter “3 first char. of postal code”
In the "Computation expression" field, enter "Substr (BB, 1, 3)".
Click on the Apply button. A new column "First 3 char. Of postal code" is added to the report with as value the first 3 characters of the postal code of each of the properties.

Note: 'BB' is an alias and refers to the "Postal code" column (your alias might be different). Under "Columns" appears the list of all the columns with their aliases. They can be used to perform both simple and complex computations (i.e difference between asking price and selling price, number of days the property is on the market, etc.).

4.Add a column to determine the number of days elapsed between the listing date and the transaction date

In the Actions menu, select: "Format> Compute"
We will now add a column that will calculate the number of days elapsed between the listing date and the transaction date
Under the "Column Label" field, enter "Nb of days"
Under the "computation expression" field, enter the following formula: "I - CA".
Click on the Apply button. A new column "Nb days" is added to the report with as value the difference between the transaction date and the registration date of each of the properties.

Note: 'I' and 'CA' are aliases and refer to the "Transaction date" and "listing date" columns (your aliases could be different). See your aliases in the list under the caption "Columns".

5.Group transactions by the first three characters of the postal code

In the Actions menu, select: "Format> Control Break".
This function is used to break a column of a report. For our example, group the transactions by the first three characters of the postal code.
Under the "Column" field, select the column entitled "** 3 first char. Of the postal code"
Click on the Apply button. The report is regenerated immediately by grouping the transactions by the first 3 characters of the postal code.

6.Calculate the average of the days elapsed based on the first three characters of the postal codes.

In the Actions menu, select: "Format> Aggregate".
This function is used to perform sums, average, maximum, median, etc. from a numeric or currency column in a report. For our example, calculate the average of the days elapsed for each of the postal code groupings.
Under the "Function" field, select: "Average".
Under the "Column" field, select: "** Nb days".
Click on the Apply button. The report is regenerated immediately by adding to the last line of each of our groupings by postal code, the average number of days elapsed between the listing and the sale.

7.Save Save your personalized interactive report

If you want to reuse your personalized report, you can just save it.

In the Actions menu, select: Save report and enter a representative name (the description is optional).

You can reuse it at any time by going back to "Modules> Transactions> Desktop> Detail button" and clicking on the "Reports" drop-down list. Your report appears at the bottom of the list in Private Reports.

Your first interactive report is over!

CONVERT THE RESULT TO A GRAPH

As it seems heavy to me to go through each of the pages to know the average number of days per postal code, nothing better than to represent in a graphic form the result of my work.

• In the Actions menu, select: "Format> Chart".

This function allows you to easily represent in graphic form, 2 data of my report with a function.

Type of graphic: horizontal or vertical stick
Under the "Caption" field, select: "** First 3 char. Of the postal code", Axis title for the caption: "Postal code".
Under the "Value" field, select: "** Nb days", Axis title for the value: "Days".
Under the "Function" field, select: "Average".
Under the "Sort" field, select: "Value - Descending".
Click on the Apply button. The graph is generated immediately

Modify the chart

To modify the layout or sorting of the chart, all you have to do is click on the "Edit chart" link which is located among the filters.

If you want to have the same statistic but by city instead of the postal code, it's simple; Just click on the link "Edit chart"

Select, under "Label", "City" (Change the title of the label to "City").
Click on the Apply button. The graph is regenerated immediately but displays the average number of days per city instead of the first 3 characters of the postal code.