Building a query
A query is an SQL query against the FA database. In most cases, the database is MySQL so the SQL needs to be compatible with that. Query needs to be any valid SELECT query against the database - no other types of queries are allowed.
SELECT s.name AS Name, s.isin_code as ISIN FROM pm2_security s SELECT * FROM pm2_security
Since the data in FA is divided into multiple tables, we recommend using joins to combine the data.
More information on building SQL queries: SQL tutorial, MySQL Functions, MySQL Select syntax, MySQL Subqueries.
You can build your queries in the Queries view. Enable the query editor by clicking Show query, and type or copy-paste your query into the text area.The query editor is context-sensitive and supports building the query in the following way:
- [CTRL + Space]
Lists all the available database tables.
- <table name>[.]
Lists all the available table columns.
- <table alias>[.]
List all the available table columns.
- [CTRL + Space] then [$]
Lists all the available custom functions supported.
- [Enter]
Inserts a line break.
- [CTRL + Enter]
Executes the query.
Examples
Listing assets under managements of all the EUR based portfolios over time:
SELECT r.report_date AS Date, ROUND(SUM(r.market_value),2) AS AUM, count(p.id) AS Portfolios FROM pm2_pf_report r LEFT JOIN pm2_portfolio p ON p.id = r.pf_id LEFT JOIN pm2_security cur ON cur.id = p.currency WHERE cur.security_code='EUR' AND ( month(r.report_date) != month(ADDDATE(r.report_date, INTERVAL 1 DAY)) OR ( r.report_date = CURDATE()) ) GROUP BY Date ORDER BY r.report_date ASC
Listing top ten biggest portfolios on the selected date:
SELECT p.name AS Portfolio, ROUND(r.market_value,2) AS AUM FROM pm2_pf_report r LEFT JOIN pm2_portfolio p ON p.id = r.pf_id LEFT JOIN pm2_security cur ON cur.id = p.currency WHERE cur.security_code='EUR' AND r.report_date = $P(endDate) ORDER BY AUM DESC LIMIT 10