IPAddr

Using USER SQL Query to generate a report into Excel

The system provides the means for you to gene rate your own queries and reports in order to extract out the data in the way that you want to see it. You can save a query that you have generated so that you can come back in and re-run that query at any time in the future without having to specify it again. You can also edit an existing query to change what it prints, how the data is selected and the sort order of the report.


Generating a pre-defined report

  1. Go to then Main screen for your Rent Master or Ausbroker program
  2. Click on the INQUIRY menu
  3. Click on USER SQL INQUIRY option

  4. click on NEW

  5. Select an existing query from the list
  6. Click on DISPLAY RESULTS

  7. After a short delay while the data is generated you should see a list of data that exactly matches your query

  8. Click on the EXPORT button

  9. When asked for the file name change the name to be PROPERTY.CSV (or whatever you want to call it)

  10. Then, when asked, choose to open it in EXCEL

  11. You'll then see the data in Excel and you can save the Excel file to wherever you want it to enable you to generate your letters.

  12. When you exit out of Excel and go back to the main program - click on the CROSS in the top right hand corner of the screen to close the results screen


Generating a New Report

  1. Go to then Main screen for your Rent Master or Ausbroker program
  2. Click on the INQUIRY menu
  3. Click on USER SQL INQUIRY option

  4. Click on NEW

  5. Choose PROPERTY in the drop down list for SELECT MAIN TABLE
  6. Click on LANDLORD in the list for SELECT RELATED TABLES
  7. Click on NEXT

  8. Double click on all of the data items that you want in your Excel spread sheet - one after the other in the order you want them across the page
  9. (eg Property.Landlord Code, Name, Work Phone, No Of Properties, Property.Address 1, Property.Address 2, Property.Suburb, Property.Postcode, Date Landlord Agreement, Date Landlord Expiry, Current Balance)
  10. Click on the NEXT button

  11. Double click on LANDLORD.NAME to make it the main data item that you want to sort on
  12. Double click on LANDLORD.LANDLORD CODE to make it the next data item that you want to sort on
  13. Click on the NEXT button

  14. Change the box up the top to read GREATER THAN instead of EQUAL TO
  15. Enter 0 into the CRITERIA box
  16. Double click CURRENT BALANCE (this will beck that the current balance is greater than zero)
  17. Click on the NEXT button

  18. This will then show you a command similar to this one ...

          
      Select [Property].[Landlord Code], [Name], [Work Phone], [No Of Properties], 
        [Property].[Address 1], [Property].[Address 2], [Property].[Suburb], 
        [Property].[Postcode], [Date Landlord Agreement], [Date Landlord Expiry], 
        [Current Balance]
      From [Property], [Landlord]
      Where [Property].[Landlord Code] = [Landlord].[Landlord Code]
        And [Current Balance] >=  0
      Order By [Name], [Property].[Landlord Code]      
      
    
  19. Click on DISPLAY RESULTS

  20. After a short delay while the data is generated you should see a list of data that exactly matches your query

  21. Click on the EXPORT button

  22. When asked for the file name change the name to be PROPERTY.CSV (or whatever you want to call it)

  23. Then, when asked, choose to open it in EXCEL

  24. You'll then see the data in Excel and you can save the Excel file to wherever you want it to enable you to generate your letters.

  25. When you exit out of Excel and go back to the main program - click on the CROSS in the top right hand corner of the screen to close the results screen

  26. You can (optionally) click on the SAVE button to save this query (to be used again later) and give it a name.


Changing a pre-defined report

  1. Go to then Main screen for your Rent Master or Ausbroker program
  2. Click on the INQUIRY menu
  3. Click on USER SQL INQUIRY option

  4. click on NEW

  5. Select an existing query from the list
  6. Click on the EDIT button

Now you basically repeat the same procedure as for creating a new report except that the existing query choices are already there for you to modify them rather than starting again.