Page History

...

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

...

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

General Notes on this Issue:

-any query that uses each budget allocation, transfers, encumbrances, balances, etc. , bring in budget year and fund group budget year and fund group fiscal year

-for just the transaction with the fund associated, no need to include related tables such as budget

-better to begin by joining to the main table for your query (e.g. finance_fund), then secondary tables, e.g. for invoice transactions, start with transactions derived table

-start with the table from which you will gather the most important columns

-using derived tables first best for performance

-purchase orders without transactions will not show fiscal year

-where parameters are included for both date ranges and fiscal year, make sure you are using dates within the given fiscal year (this is an important note to include for users in the README, too)

Example for Queries

Here is a helpful example from Stefan Dombek on the FOLIO project:

A fund_id can exist in different fiscal years because they are different budgets (due to fiscal year rollover). But they can only be distinguished by the fiscal year. And that can only be done via the budget table. If you want to join the data tables correctly, you should join them in this way:

folio_finance.fiscal_year                    
LEFT JOIN folio_finance.budget ON budget.fiscalyearid = fiscal_year.id
LEFT JOIN folio_finance.fund ON fund.id = budget.fundid
LEFT JOIN folio_finance.ledger ON ledger.id = fund.ledgerid

Fiscal Year Data Model

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

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

...

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

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,

...

• 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