FI$Cal Reports and Queries

Overview

The Financial Information System for California (FI$Cal) is the states enterprise-wide financial management solution. It consolidates budgeting, accounting, procurement, cash management, and reporting into a single, integrated platform. One of the most powerful features of FI$Cal is its capacity to generate detailed reports and execute custom queries, giving agencies the insight they need to make datadriven decisions.

This page explains the core concepts behind FI$Cal reports, the main categories of reports available, how to build and run queries, and tips for getting accurate and timely results.

Report Types

FI$Cal provides several builtin report families. Each family serves a specific purpose and is tailored to the workflows of different functional areas.

Standard Financial Reports

• General Ledger (GL) Detail Lists every transaction posted to the GL, filterable by account, period, fund, and department.
• Trial Balance Summarizes debits and credits by account, useful for verifying that the books are balanced.
• Budget vs. Actual Shows budgeted amounts alongside actual expenditures, highlighting variances.
• Cash Flow Statement Tracks inflows and outflows to help manage liquidity.

Procurement Reports

• Purchase Order (PO) Register Lists all open and closed purchase orders, with status and vendor information.
• Spend Analysis Aggregates spending by vendor, commodity, or cost center.
• Contract Compliance Monitors adherence to contract terms and identifies over or underspending.

Human Resources & Payroll Reports

• Payroll Register Detailed payroll transaction data for each employee.
• Salary Budget Summary Projects salary costs against approved budgets.
• Benefit Allocation Tracks employer contributions to health, retirement, and other benefit programs.

Custom Reports

When standard reports do not meet a specific need, FI$Cals report designer allows users to craft custom layouts. These reports can be saved, shared, and scheduled for automated delivery via email or file export.

Query Builder

Beyond preconfigured reports, the Query Builder is a flexible tool for adhoc data extraction. It works by creating a SELECT statement against FI$Cals relational data warehouse, but you never need to write SQL directly unless you choose to.

Key Steps to Build a Query

1. Select a Data Set Choose the module (e.g., GL, Procurement, Payroll) that contains the tables you need.
2. Choose Fields Drag and drop column names into the results pane. Common fields include AccountNumber, TransactionDate, Amount, and DepartmentID.
3. Apply Filters Add criteria such as Fiscal Year = 2025 or Amount > 1000. Multiple filters can be combined with AND/OR logic.
4. Group & Summarize For totals or averages, group rows by a dimension (e.g., FundCode) and select aggregate functions.
5. Sort & Limit Define the order of rows and, if needed, limit the result set for quick previews.
6. Run & Export Execute the query and export the output to Excel, CSV, or PDF.

Example Query

This query returns the total spending per account for fiscal year 2024, showing only positive amounts and ordering the results from highest to lowest.

Saving and Scheduling Queries

After a query is built, you can:

• Save as a Template Reuse the same logic without rebuilding.
• Schedule Execution Run automatically on a daily, weekly, or monthly basis. Results can be mailed to a distribution list or placed in a shared drive.
• Set Permissions Control who can view or modify the query, protecting sensitive data.

Best Practices for Accurate Reporting

• Define Clear Objectives Know the decisionmaking question you need to answer before selecting fields or filters.
• Validate Data Sources Ensure the underlying tables are refreshed and that the fiscal calendar aligns with your reporting period.
• Use Consistent Naming Conventions Standardize how you reference accounts, funds, and cost centers to avoid duplication.
• Apply Appropriate Aggregations Mixing summed amounts with nonaggregated columns can produce misleading totals; always group by the nonsummed fields.
• Check for Nulls and Zeros Many reports default to zero for missing values; be aware of how this may affect variance calculations.
• Document Your Queries Keep a short description of purpose, filters, and version history within the query metadata.
• Review Access Controls Sensitive financial data should be restricted to authorized personnel only.
• Test Before Scheduling Run a query manually for a few cycles to confirm results before automating delivery.

Additional Resources

For deeper exploration of FI$Cal reporting capabilities, consider the following:

• FI$Cal Official FAQ
• FI$Cal Training Portal Ondemand webinars and handson labs.
• Report User Guide (PDF)
• User Community Forum Ask questions and share best practices.
• Technical Support Submit tickets for system issues or data anomalies.

Staying current with system updates and training releases ensures you can leverage new report templates and query enhancements as they become available.