Query-based report templates
Query-based report templates allows you to dynamically save report templates based on your queries on the Queries view, allowing you to easily, flexibly and efficiently build your own reports directly in the system. Write a query to fetch any data you want, save the query as a report template, and use the template in reporting as any other available PDF report.
To create report templates, go to the Queries view.
To create a new report template:
Write a query in the Queries view. For details, see Best practices for writing a query for a report template.
Your query has to include the portfolio ID parameter - this allows the report template to react to the customer or portfolio you are generating the report for, showing only the selected customer's or portfolios' data. If your query includes this parameter, you can see the Group, customer, portfolio selector on the query tab when you hide your query.
If you wish, you can also include start date and end date parameters in your query - with date parameters your report template reacts to the date you are generating the report for. If your query includes these parameters, you can see the Start date and End date selectors on the query tab when you hide your query.
Save your query as a tab on the Queries view. Consider the name of your query since it is used as:
The file name of the report generated from the template (define the report file naming syntax in Preference - Reporting).
The report name in the header of the generated report file.
The report name in the report listing in the Report window.
Click the Save as a report template icon on the top-right to create a report template out of your query.
You can decide which users you want to share the report template with, i.e. which users can see and use the report template in the Report window. Not shared means the template is available only for you to use, Shared with everyone means the template is available for everyone to use, and Shared with group xx means the template is available for the users in the specific group.
Once you save the report template, a new item appears in the report listing in the Report window. Report templates are shown in the report listing among all other available reports with the suffix (PDF), indicating they can only be used in PDF file format.
After you have saved your query as a report template, a file icon appears in your saved query tab - the icon indicates this query has been saved as a report template. If you modify a query which have been saved as a report template, you are prompted to confirm that your changes to the query also affect the report template you have saved.
You can click the Remove from report templates icon to remove the report template from your available reports. Also, if you delete the query, the report template saved from it gets removed as well.
Once you have saved a query as a report template, you can use your report template as any other available report: select the report(s) from the left, define the parameters on the right, and click Open to generate the report.
Report templates are shown in the report listing among all other available reports with the suffix (PDF), indicating they can only be used in PDF file format. Once you select a report template from the left, the report parameters on the right dynamically react to the parameters your query supports: if your query supports the portfolio ID parameter (required), you'll have the Customer, group, portfolio selector available, and if your query supports the date parameters, you'll have the Date period selector available - whatever you select from these fields is then passed to your report template.
When you generate a report through a report template from the Report window, the system will run the underlying query for the selected contact or portfolio and the selected dates, and return the result as a PDF report that looks like any other report you generate. The report templates use the same styles, headers/footers and logos as your standard reports for consistent look&feel, allowing you to combine them seamlessly.
You can more or less write any kind of a query to fetch any data you want for your report template - see Building SQL queries in FA for instructions on writing queries. However, consider things like performance when writing the query - if the query takes a long time to run in the Queries view, it will take a long time to run when generating the report template as well
Portfolio ID parameter
Your query has to react to the portfolio ID parameter in order for the report templates to work consistently with other reports, i.e. only include information of the selected group, customer or portfolio. The portfolio ID parameter is required also to prevent accidentally saving report templates out of queries that don't limit their result to selected portfolio(s).
In order for a query to work properly as a report template when generated through the report window, the "portfolioId" parameter has to be included in your query's WHERE clause using the syntax id IN $P(portfolioId). With this, the "portfolioId" supports a list of portfolios, i.e. allows the user to also select a contact with multiple portfolios or a group with multiple portfolios from the Customer, group, portfolio field in the Report window. Including the "portfolioId" parameter with the syntax "pf_id = $P(portfolioId)" would only work when the user selects one distinct portfolio from the field.
The Report window dynamically reacts to the parameters included in your report template query: when your query includes the $P(portfolioId) parameter, the Customer, group, portfolio field is available in the Report window for the report template, and the selected portfolio(s) are passed to the query as a parameter for "portfolioId".
Example:
SELECT s.name AS 'Security', s.isin_code AS 'ISIN code', ROUND(SUM(pri.market_trade_amount), 0) AS 'Market value', ROUND(pri.share_of_portfolio * 100, 2) AS 'Share %'
FROM pm2_pf_rep_item pri
LEFT JOIN pm2_pf_report pr ON pr.id = pri.pr_id
LEFT JOIN pm2_security s ON s.id = pri.security
LEFT JOIN pm2_portfolio p ON p.id = pr.pf_id
WHERE pr.report_date = CURDATE() AND pr.pf_id IN $P(portfolioId)
GROUP BY pri.security
ORDER BY pri.market_trade_amount DESC
LIMIT 10
Date parameters
In addition, your query can optionally react to start date and end date parameters, if you want to allow the user to be able to select the dates the report template fetches data for. Instead of allowing the user to select a date, you can write a query that doesn't need to react to dates, or you can hard-code a date in your query for example by using the CURDATE() function.
When including the date parameters in your query, the best practice is to always include both the "starDate" and "endDate" parameters in your query's WHERE clause. With this, both the start date and end date of the date range the user selects from the Date period field in the Report window are passed to the query.
The Report window dynamically reacts to the parameters included in your report template query: when your query includes both the $P(startDate) AND $P(endDate) parameters, the Date period field is available in the Report window for the report template, 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".
Example:
SELECT s.name AS 'Security', tr.type_name AS 'Transaction type', DATE(t.transaction_date) AS 'Transaction date', t.amount AS 'Units', ROUND(t.trade_amount, 2) AS 'Value'
FROM pm2_transaction t
LEFT JOIN pm2_tr_type tr ON tr.type_code = t.type
LEFT JOIN pm2_security s ON t.security_id = s.id
WHERE t.status='OK' AND t.pf_id IN $P(portfolioId) AND t.transaction_date >= $P(startDate) AND t.transaction_date <= $P(endDate)
ORDER BY t.transaction_date ASC
Content and layout
The content of your PDF export corresponds to the columns and data your query returns, and the system dynamically produces a PDF out of it. You can try out how your query would look like as a report template by taking the PDF export out of it - the report template and PDF export generate a PDF that looks the same.
Best practice is to try out a few PDF exports out of different data before saving a query as a report template. This allows you to make sure beforehand your data fits nicely on a dynamic one-page PDF, and allows you for example to optimize the number of columns, their order, captions, etc. to build the best-possible report template.
Controlling column widths on the template
In addition to relying on the system to dynamically generating the PDF based on your template, you have an option to control the column widths on your query-based report template. The writer of the query to override the default logic of JasperReport and to control how many pixels are used for the column's width. (Available from FA 3.7.1 onward)
The syntax to control the column width is embedded into defining the names of the columns within your query - follow the syntax such as SELECT column AS "Column[pixels]" (e.g. SELECT p.name AS "Name[100]").
Define the pixels as an integer (e.g. [100]) at the end of the column name within the "" - brackets and width are left out from the column name in the report, showing only the name of the column. You can define the width for all columns, but then it is up to you to define the width in a way that the whole report area is used properly. Recommendation is to leave at least one column without a width, allowing it to resize to fill in the report appropriately.
When defining column widths, the following errors are handled:
If the text within the brackets cannot be determined to be an integer (number without decimals), the definition is ignored and considered to be part of the title.
If there are extra spaces within bracket, the definition is ignored. That is, the column width definition needs to be given without any extra characters (e.g. like ‘[100]’, not like ‘[ 100 ]’).
Example - both "Info1" and "Info3" columns have fixed widths and at the same time "Name" and "Info2" columns' width is defined automatically by the system.
SELECT
p.short_name AS "Name",
p.info1 as "Info1[100]",
p.info2 as "Info2",
p.info3 as "Info3[50]"
FROM pm2_portfolio p
WHERE p.id in $P(portfolioId)