Page History

...

Fields to Include in Queries that use Fiscal Year

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

...

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 write SELECT the fiscal_year_code field in your query with a join to the finance_fiscal_years table

SELECT ffy.code AS expense_class_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:      

Here is an example of a section of a report that includes all the fields required for fiscal year. 

...