Questions:
Should all queries simply restrict results by fiscal year?
To ensure that your query can show data from multiple fiscal years, make sure you have included table joins on all financial fields required.
Fields to Include in Queries that use Fiscal Year
Table | Field | Notes |
---|---|---|
It is best to join tables in the following order if you need results for multiple fiscal years. If you do not do this, you may see duplicates in your results set.
finance_funds
finance_budgets
finance_fiscal_years
finance_group__fund_fiscal_years
finance_groups
finance_fund_types
expense_class
Restricting Your Query Results by Fiscal Year
There are 2 ways you can restrict your query results by fiscal year: including start and end dates that correspond to a given fiscal year, or using a fiscal year field.
For instance, you can write a WHERE statement for the start and end dates, such as
WHERE
((SELECT start_date FROM parameters) ='' OR (invoice_payment_date::date >= (SELECT start_date FROM parameters)::DATE))
AND ((SELECT end_date FROM parameters) ='' OR (invoice_payment_date::date < (SELECT end_date FROM parameters)::DATE))
then add this parameter filter at the top of your query. Note that the dates below correspond to the start and end of Fiscal Year 2023.
/* Enter the start date and end date in YYYY-MM-DD format */
'2022-07-01'AS start_date,
'2023-06-30'AS end_date,
Alternatively, you can use a fiscal year field in your query and include a parameter at the top of the query that allows the person running it to restrict results by a given fiscal year.
For instance, you can SELECT the fiscal_year_code field in your query with a join to the finance_fiscal_years table
SELECT ffy.code AS fiscal_year_code
FROM folio_reporting.finance_transaction_invoices AS fti
LEFT JOIN finance_fiscal_years AS ffy
ON ffy.id = fti.transaction_fiscal_year_id
include a WHERE statement to include fiscal_year_code as a parameter,
WHERE ffy.code = (SELECT fiscal_year_code FROM parameters)
then include the parameter in your WITH statement at the top of the query to allow those running the query to restrict the results to fiscal year:
/* Enter the fiscal year between the quotes below in FY#### format */
WITH parameters AS (
SELECT
'FY2023'::VARCHAR AS fiscal_year_code
)
Here is an example of a section of a report that includes all the fields required for fiscal year.
Checking results in the Finance App