Query parameters

By embedding certain parameters into your query, you can allow your users to select which contact, portfolio, security, user or dates they want to run the query with. When parameters are inserted into the query, the Queries view shows the corresponding field where the user can select the parameter value.

List of parameters

$P(portfolioId), $P(contactId), $P(securityId) and $P(userId)

Enables the group/customer/portfolio selector, contact selector, security selector or user selector. These can be used to narrow down the portfolios that the query is targeted to.

Portfolios based on selected group/customer/portfolio:

SELECT p.id AS id
FROM pm2_portfolio p
WHERE p.id in $P(portfolioId)

$P(startDate) and/or $P(endDate)

Enables one or two date selectors. These can be used to narrow down the date or a date range.

Transactions between start date and end date:

SELECT t.id AS id
FROM pm2_transaction t
WHERE t.transaction_date >= $P(startDate) AND t.transaction_date &lt;= $P(endDate)

$P(customParameter)

Allows you to define your own parameters to narrow down the result. The custom parameters result in a text field in the Queries view, allowing you to type in the parameter value.

Best practices on using custom parameters:

Use custom parameters as you would use fixed parameters - you should include the custom parameter in your WHERE clause (for example WHERE name = "$P(name)"), allowing the user to type in a number or text that would be inserted into the query.
The parameter value the user enters into the field is inserted into the query as is. For example, if you are expecting the user to type in text, place the parameter in "", for example WHERE name = "$P(name)".
It is technically possible to for example write an entire SQL clause in the custom parameter field, however, this is NOT the best practice to use custom parameters.

"My customers" for logged in user:

SELECT name, contact_idFROM pm2_contact c
WHERE c.id IN $V(currentUserContactIdsExtended) AND type = 1

Parameters throughout FA

Place	Details	Specifics for certain parameters
Queries view	When parameters are inserted into the query, the Queries view shows the corresponding field where the user can select the parameter value. Parameter value is saved with the query, and the query is run against the saved parameter value when the query tab is accessed.	$P(portfolioId) / $P(contactId) / $P(userId) - Components support limited visibility, showing only the portfolios / contacts / users the logged in user is allowed to see. $P(startDate) / $P(endDate) - Saved parameter value is moved forward as the time moves forward (i.e. if you save either of the dates to 1.10.2019, the next day you come in the value is 2.10.2019, day after that 3.10.2019 etc.).
Dashboards with queries	User can select saved query tabs on the Dashboard only if the user has permission to access Queries view. However, someone else can pre-define dashboards with queries to users who don't have permission to access Queries view, when users can see the result of the query. User cannot modify the query from the Dashboard, but to see the result of the query in a table. User can maximize a query to export the content s XLS or PDF. When a section with a query is accessed, the query is always run with the saved parameter value (i.e. query is run and the result is by default shown for the parameter value saved in the Queries view for the query). When the user maximizes a query in the Dashboard, the user can change the parameter values to run the query with a different parameter - however, the parameter changes here are not saved, and when accessed again, the query is run with the saved parameter. Components induced by $P(portfolioId), $P(contactId) and $P(userId) support limited visibility - for these: Query is run with the saved parameter value only if the logged in user can access the saved parameter value (portfolio, contact or user) in terms of limited visibility. Otherwise the query is run "without a parameter value", resulting in "No result from your query". When user maximizes the query, the user can select a parameter value to run the query with a parameter value (portfolio, contact or user) they can access in terms of limited visibility. Dashboard DOES NOT limit the results of the query with limited visibility, just the components filter the contacts, portfolios and users the user can select!	$P(portfolioId) / $P(contactId) / $P(userId) - Components support limited visibility, showing only the portfolios / contacts / users the logged in user is allowed to see.
Report templates	Report window dynamically reacts to the parameters included in the query: Query includes $P(portfolioId) parameter =>"Customer, group, portfolio" dropdown is visible in the Report window, and the selected portfolio(s) are passed to the query as a parameter for "portfolioId". Query includes $P(startDate) or $P(endDate) parameters => "Date period" dropdown is visible in the Report window visible, and the start date of the selected date range is passed to the query as a parameter for "startDate" and the end date of the selected date range is passed to the query as a parameter for "endDate".	Supported parameters: $P(portfolioId) - Mandatory $P(startDate) / $P(endDate) - Best practice to include both or neither
Front (Datagrid + Custom content)	When queries with parameters are used in FA Front, you need to map the parameters you use in FA Back to parameters supported by FA Front - with the mapping, the parameter values are picked up for example from the selections in the Toolbar.	Best practices for mapping parameters from Back to Front (and where the values get passed to the query from Front): $P(portfolioId) => $portfolioId (for selected portfolio(s) in the Toolbar) $P(contactId) => $contactId (for selected contact in the Toolbar) $P(userId) => $userName (for logged-in user) $P(startDate) => $startDate (for start date selected in the Toolbar) $P(endDate) => $startDate (for end date selected in the Toolbar)
Query monitoring	Values for the parameters are defined in the process configurations	$P(startDate) => "Start date for the query" configuration $P(endDate) => "End date for the query" configuration $P(portfolioId), $P(contactId), $P(userId), and custom parameters are given in the "Any other query parameters"-configuration. Variables do not work with scheduled query monitoring.
Query API	Parameters for the Query API are defined in the query body in JSON format. E.g. {"startDate":"2019-05-15", "portfolioId":1092, "userId":"admin"}	Using variables requires providing the user ID as one of the parameters.

In this section:

Query parameters

List of parameters

Parameters throughout FA

Search results