WinMORE supplies you with multiple reports through the Print, Month-End, and Year-End menus. However, you or your OP or TL may want additional information, or you may want to see it in a different format. You’ve got powerful additional options through Ad Hoc Reporting. Through Ad Hoc Reporting you can do the following:
Create Your Own Ad Hoc Report*
* This information is adapted from a presentation by Regional MCA, Patrick McGowan.
Creating an Ad Hoc Report essentially means creating a “one-liner,” or a command script, to pull information from WinMORE. It’s an easy process when you understand where the information is coming from and which words will have which effect on the display of the information.
Tables
Information you enter into WinMORE is saved in tables. The tables in WinMORE are not relational databases. When you create an Ad Hoc report, you can only pull information from one table. The two most frequently utilized tables are AGENTS and DA.
Table Name Stores the following …
| AGENTS | Associate information from the Associate Entry window |
| DA | DA information from the DA Entry window |
| AGT_TRANS | Associate detail information from the greensheet or the DA Entry – Associate Detail window |
| DA_TRANS | Outside broker, co-broker, and deduction information from the DA Entry – Associate Detail window |
| LISTINGS | Property and lease listings |
| ALL_AGENTS | All associates in KW for Profit Share |
Fields
Within each table there are multiple fields. To determine which fields are in a particular table, refer to WinMORE.
- From WinMORE, select the Other menu, then Ad Hoc Reports.
- The Ad Hoc Report Entry window appears.
- Click the Schema List button in the bottom right-hand corner of the window.
- The Option window appears. Enter the table for which you would like to view field list: for example, AGENTS.
- Select a print option.
- The field list will appear.
Note: Fields aren’t the same from table to table. For example, in the AGENTS table, a date may appear as 1/1/10, but in the DA table, it could appear as 201001
Creating An Ad-Hoc "One-Liner"
Note: The “one-liner” does not have to have it all on one line. To track the 4 parts of the ad-hoc it might be easiest to put each section on its own line in the command window
1. Identify the Source Table
All one-liners start with LIST. Then add the name of the table with which you want to work.
Example: LIST AGENTS
In this example, you are pulling from the AGENTS table.
2. Identify the Sort Order
State by which field you would like the report sorted.
Example: LIST AGENTS BY NAME_L
In this example, you are pulling from the AGENTS table, and you are sorting the information by Legal Name.
Command Does the following …
| BY |
Determines sort order. Example: LIST AGENTS BY NAME_L |
| BY-DSND |
Sorts in descending order. Example: LIST AGENTS BY-DSND NAME_L WITH STATUS = ‘A’ NAME_L SOC_SEC |
3. State Selection Criteria
After identifying the source table, state the criteria by which data is to be selected. This can be a single clause in the one-liner or it may contain several clauses. Notice these are logical arguments so you have to enter the field you want to match to and then what data you desire.
Example: LIST AGENTS BY NAME_L WITH STATUS = ‘A’
In this example, you are pulling from the AGENTS table, you want to sort the information by Legal Name, and you only want the agents with a status of A.
You can add multiple criteria to the selection criteria. You will doing this using OR WITH or AND WITH in the statement. For example, you could pull people with an anniversary date in a particular range. Or you could pull people who lived in a certain zip code. Your options are only limited by the fields in the table and your imagination!
Example: LIST AGENTS BY NAME_L WITH STATUS = ‘A’ AND WITH …
You can also list multiple items to match the criteria to using an IN statement. To get a list of agents by more ID:
Example: LIST AGENTS WITH ID2 IN ( '4567' '1234' )........
Command Does the following …
| WITH |
Determines selection criteria. Example: LIST AGENTS BY NAME_L WITH STATUS ‘A’ |
| AND WITH |
Adds additional selection criteria. Example: LIST AGENTS BY NAME_L WITH STATUS ‘R’ AND WITH STATUS ‘VR’ |
| OR WITH |
Adds additional selection criteria. Example: LIST AGENTS BY NAME_L WITH STATUS ‘R’ OR WITH STATUS ‘VR’ |
| BETWEEN |
Selects a range of information. Example: LIST DA BY AGENT_NAME WITH STATUS = ‘P’ AND WITH CLOSE_PERIOD BETWEEN 201001 AND 201002 AGENT_NAME PRICE UNITS |
| = |
Field must equal your entry in order to be selected for your report. Example: LIST DA BY AGENT_NAME WITH STATUS = ‘P’ AND WITH CLOSE_PERIOD BETWEEN 201001 AND 201002 |
| # or NE |
Says selection criteria should be anything but what you’ve given or not equal to Example: LIST AGENTS BY NAME_L WITH STATUS # 'VR' NAME_L SOC_SEC |
| GT | Says the selection should be greater than what you’ve listed. |
| LT | Says the selection should be less than what you’ve listed. |
Also, tokens can be inserted into the criteria to assist you with not having to update ad hocs constantly. For instance, if you write a report that is looking at DA's for the current period, instead of entering an actual period in your ad hoc you can insert the token for current period.
Example: CLOSE_PERIOD = '%PERIOD%' will pull the report using the current period of WinMORE instead of you constantly updating the period.
For a list of available tokens, click the token button in the ad-hoc Window.
4. List Report Fields as Needed
Once you have defined how data is to be sorted and selected, you can then specify the fields to include in the report. Example: LIST AGENTS BY NAME_L WITH STATUS = ‘A’ NAME_L SOC_SEC
In this example, you are pulling from the AGENTS table, you want to sort the information by Legal Name, and you only want the agents with a status of A. You’ve also decided that you want to see the Legal Name and the Social Security Number as columns in the report.
Your report would look like the following:
AGENTS
999 - Keller Williams Valley
02/08/2010 09:48:06PM 1
| Key | Legal Name | SOC_SEC |
| 177*5268 | Ains, Bob | 123465268 |
| 177*0485 | Ama, Zack | 123440485 |
| 177*2030 | Analt, Steve | 123452030 |
| 177*7583 | Angel, Jason | 123437583 |
| 177*5785 | Annecon, Juana | 123455785 |
| 177*8433 | Anunciacion, Andrew | 123448433 |
| 177*5227 | Ari, Janet | 123415227 |
| 177*5956 | Atta, Hervin | 123465956 |
Modify Your Reports
You can use additional commands to further customize your report; for example, to add subtotals or select a date range.
Command Does the following …
| “‘p’” |
Tells the system to insert a page break. Example: LIST AGENTS BY FISC_MO WITH STATUS = “A” BREAK-ON FISC_MO “‘p’” NAME_L |
| BREAK-ON |
Groups data in reports. Example: LIST AGENTS BY FISC_MO WITH STATUS = “A” BREAK-ON FISC_MO NAME_L |
| TOTAL |
Totals a column. If you are using BREAK-ON, it will provide subtotals as well. Example: LIST DA BY AGENT_NAME WITH STATUS = ‘P’ AND WITH CLOSE_PERIOD BETWEEN 200601 AND 200602 BREAK-ON AGENT_NAME TOTAL PRICE TOTAL UNITS |
| DET-SUPP |
Shows totals only in the report. Example: LIST DA BY AGENT_NAME WITH STATUS = ‘P’ AND WITH CLOSE_PERIOD BETWEEN 201001 AND 201002 BREAK-ON AGENT_NAME DET-SUPP TOTAL PRICE DET-SUPP TOTAL UNITS |
| % |
Tokens allow you to write a report to pull information based on either conditions (ex. current year or month) or to prompt you for information when the report is ran. This eliminates the need to constantly update the ad-hoc with certain criteria. In some cases this means it will prompt the system to automatically select information, such as in this report in which %YEAR% is a token for the current year. Example: LIST DA WITH CLOSE_PERIOD BETWEEN %YEAR%01 AND %YEAR%12 AND WITH STATUS = “P” AND WITH CLASS = “L” FULL_STREET PROPERTY_CODE TOTAL LISTING_COMM TOTAL SALES_COMM Some tokens allow you to specify information. For example, %OPTION% would allow you to enter anything and %MOREID% would allow you to lookup a WinMORE ID. Click on the Tokens button on the Ad Hoc Report Entry window for a list of tokens and what they do. |
Click on the Token button to access a list of tokens you can use.
Advanced use of "AND" and "OR" statements:
Be mindful of the order you put things when joining criteria by 'AND' or 'OR'. These are processed in the order mentioned. You can group criteria by ( ) to control how the data pulls.
Example: Consider the following statement:
LIST AGENTS WITH STATUS = 'A' AND WITH PRODUCTION_STATUS 'A' OR WITH PRODUCTION_STATUS = 'O' FNAME LNAME STATUS PRODUCTION_STATUS
In this case you might think that you will get Agents in status "A" with either a production status of "A" or "O". What you will actually get is all agents in Status "A" with production status "A" and any associate that has a productions status of "O", regardless of their status. If you want to get the results first stated you would need to modify the ad-hoc as shown below:
LIST AGENTS WITH STATUS = 'A' AND ( WITH PRODUCTION_STATUS 'A' OR WITH PRODUCTION_STATUS = 'O' ) FNAME LNAME STATUS PRODUCTION_STATUS
The ( ) symbols let WinMORE know that both A or O statements are to be taken together.
Comments
0 comments
Please sign in to leave a comment.