Interactive report

Close your eyes and imagine for a moment that all the information you have is a large Excel spreadsheet to which you want to sort all sorts of filters, create calculations and graphs.

All this is available under GIMMI Web! This technology allows you to manipulate, extract and produce infinite lists, reports and statistics from your data.

Below is a step-by-step guide to create a simple report that at first sight may seem impossible to realize.

Context/Situation:

I want to know the average number of days from the date of listing to the date of sale, which is grouped by the first 3 characters of the property's postal code, in order to target my next campaign advertising.

Procedure:

1. Selecting the basic report...

Customizing a report is done from a base report. Initially, select the report whose data is most similar to what you want to extract and then customize that report.

For our example, go to the "Modules > Transactions > Office" menu.

Click the Detailed button to view the detailed transaction list.

1. Selecting Columns to Display...

Click on the Actions menu and select: "Select Columns".

On the left side are the columns that do NOT appear in the report, on the right side are the columns that appear in the report.

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

Note: To make a multiple selection press the Ctrl or Shift buttons when selecting.

For our example, leave the MLS #, the address information of the property, the listing date, the transaction date and add the price sold.

Click the Apply button. The report is generated with the columns you just selected.

1. Filter Selection...

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

3.2 Establish a new filter: Keep only transactions for the last 24 months...

From 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, write in: "24" (24 months).
Select the time unit "Months" in the drop-down list, at the right of the field "Expression" (24 months).

Click the Apply button. A new filter "Transaction Date is in the last 24 months" is added at the top of the interactive report. The report data now reflects this filter.

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

From the Actions menu, select: "Filter".
Under the "Column" field, select the column: "Listing Date".
Under the "Operator" field, select: "is not NULL".

Click the Apply button. A new filter "Listing Date is not NULL" is added at the top of the interactive report. The report data now reflects this filter.

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

From the Actions menu, select: "Format > Compute". 
This function is used as in Microsoft Excel to create a formula from existing data.

Under the "Column Heading" field, write in "3 postal code first chars".
Under the "Computation Expression" field, write in "Substr(BB, 1, 3)".

Note: 'BB' is an alias and refers to the column "Postal code" (your alias might be different). Under "Columns", you will see a list of all columns with their aliases. you can use them to perform both simple calculations and complex calculations (ex: difference between asking price and price sold, number of days that the property is on the market, etc.).  

Click the Apply button. A new column "3 postal code first chars" is added to the report with the first 3 characters of the postal code of each property.

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

From the Actions menu, select: "Format > Compute". 
We will now add a column that will calculate the number of days between the listing date and the transaction date.

Under the field "Column Heading", write in "Nb days".
Under the field "Computation Expression", enter the following formula : "I - CA".

Note: 'I' and 'CA' are aliases and refer to the columns "Date transaction" and "Listing Date" (your alias might be different). See your aliases in the list under the "Columns" label. 

Click the Apply button. A new "Nb days" column is added to the report with the difference between the transaction date and the listing date for each property.

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

From the Actions menu, select: "Format > Control Break". 
This function is used to break a column in a report. For our example, group transactions by the first three characters of the postal code.

Under the field "Column", select the column named "**3 postal code first chars". 

Click the Apply button. The report regenerates immediately by grouping transactions by the first 3 characters of the postal code.

1. Calculate the average of the past days based on the first three characters of the postal codes...

From the Actions menu, select: "Format> Aggregate".
This function is used to make sums, average, maximum, median, etc. From a numeric or monetary column of a report. For our example, calculate the average of the days elapsed for each of the postal code groupings.

Under the field "Function", select: "Average".
Under the field "Column", select: "**Nb days".

Click the Apply button. The report regenerates immediately by adding to the last line of each of our groupings by postal code the average number of days between listing and sale.

1. Save your customized interactive report...

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

From the Actions menu, select: "Save Rreport" and enter a representative name (the description is optional). 

You can reuse it at any time by returning to "Modules > Transactions > Office> Detailed 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 completed!

Convert the result to a graph

As I find it hard to go through each page to find the average number of days per postal code, it's no better than graphing the result of my work.

1. From the 

Actions menu, select: "Format > Chart". 

This function makes it easy to represent graphically 2 data of my report with a function.

Chart type: Horizontal or vertical rod (I prefer the vertical representation).
Under the field "Label", select: "**3 postal code first chars",  Title of the axis for Label: "Postal code".
Under the field "Value", select: "**Nb days",  Title of the axis for Value: "Days".
Under the field "Function", select: "Average".
Under the field "Sort", select: "Value - Descending".

Click the Apply button. The graph is generated immediately.

Note: You can click the 2nd button on the left of the Actions menu at any time to return to the list and at any time click on the 1st button on the left of the Actions menu to return to the graph.

Note: It is possible that there is not a value on the axis for each rod. To view the number of days versus a postal code; You just have to pass the mouse pointer on the stick and you will have the postal code as well as the average of the corresponding number of days.

1. Change chart...

To modify the layout or the sorting of the graph, simply click on the "Edit Chart" link which is among the filters.

If you want to have the same statistics but by city instead of postal code, it's simple!

Simply click on the "Edit Chart" link.

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

Congratulations! You have made your first interactive report!