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_groups

finance_fund_typesexpense_class

Fiscal Year Data Model

Here is a diagram of the data model for fiscal year in FOLIO. 

Restricting Your Query Results by Fiscal Year

There are 2 different ways you can restrict your query results by fiscal year, for example:

• including a parameter at the top of the query for start and end dates

...

• using a

...

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,

...

• date field such as invoice payment date, then setting the start and end dates to correspond with a given fiscal year
• including a parameter at the top of the query

...

• for the fiscal year code, then entering the fiscal year code for the fiscal year for which you wish to see data
• including a fiscal year code field in your query and setting it for a particular fiscal year

Checking results in the Finance App

To make sure your query results are showing the right results for a given fiscal year, check at least 3 rows of results against the same data in the Finance App in FOLIO.

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