Imagine for a moment that all the information data you have is in a big Excel spreadsheet where you can do all kinds of sorts, filters, calculations and charts.
...
You will find below the steps to follow to produce a simple interactive report and manipulate it.
Situation:
I want to know in the sales made over in the last 24 months, the average number of days between the registration date and the sale date; grouped by the first 3 characters of the property's postal code in order to target my next advertising campaign advertising.Procedure
Creating your report:
1. Selection of the basic report
| Info |
|---|
The customization of a report is |
...
started from |
...
an existing basic report. |
...
Select the report |
...
with the data closest to what you want to extract |
...
. |
For our example , go to we will use the detailed transactions report: Transactions > Reports (top right)" menu.
Click on the Detailed button to display the detailed list of transactions.> Transactions - Detailed
...
2. Selection of columns to display
Click on Actions and then choose on Select columns
...
The On the left side displays of the window you see 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 Select a column name and use the '< & >' arrow arrows 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 informationmove them from one side to the other.
...
You can change the order in which the columns are displayed in your report by using the arrows on the right side.
...
| Info |
|---|
You can use the CTRL or Shift keys to select more than one element at the same time |
For our example, we will only keep the MLS #, address, listing date, transaction date and we will add the sold price sold. And you click Click on Apply. The report is will be generated with the columns you have just chosenselected.
...
3. Selection of filters
-Clear existing filters from the base report by clicking on the "X" to the right of the filter.1.Define
...
-Create a new filter : Keep to 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 active 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.
...
-Create a new filter : Keep to 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 active 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
4. Add columns
-Add a column representing : the first three characters of the postal code.
In the Actions menu, select: "Format> Compute".
| Info |
|---|
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
...
Apply
...
.
...
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.
...
| Info |
|---|
...
'BB' is an alias and refers to the "Postal code" column (your alias might be different). Under "Columns" |
...
you can see 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"
...
Under the "Column Label" field, enter "Nb of days"
Under the "computation expression" field, enter the following formula: "I - CA".
Click on
...
Apply
...
.
...
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.
...
| Info |
|---|
Note: 'I' and 'CA' are aliases and refer to the "Transaction date" and "listing date" columns (your aliases could be different). |
...
You can see your aliases in the list under the caption "Columns". |
5. Group data
-Group transactions by the first three 3 characters of the postal code.
In the Actions menu, select: "Format> Control Break".
| Info |
|---|
This function is used to break a column of a report. |
...
Under the "Column" field, select the column
...
''** 3 first char. Of the postal code"
Click on
...
Apply
...
.
...
The report is regenerated immediately by ; grouping the transactions by the first 3 characters of the postal code.
...
6. Calculate an average
-Calculate the average of the number of days elapsed based on the first three characters of the postal codes.
In the Actions menu, select: "Format> Aggregate".
| Info |
|---|
This function is used to |
...
calculate sums, average, maximum, median, etc. from a numeric or currency column in a report |
...
. |
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 grouping by postal code, the average number of days elapsed between the listing and the saletransaction date.
...
7. Save Save your personalized interactive report
...