Purpose: To provide a summary of each project’s current status, including progress and key dates.
Fields:
Project Name, Status, Number
Total Budget vs. Total Expenses to date and What Remains
Suggested Filters:
Status (default: In Progress)
Generated Number (e.g., projects exceeding a certain expense percentage)
Project Report Query Configuration
This document provides an explanation of the query logic and configuration used to generate a Project Report with detailed budget and expense calculations.
Purpose
The goal of this report is to provide a detailed financial overview for each project. It calculates:
Total Budget - The sum of all budget allocations for the project.
Total Contingency Reserves - The sum of reserved funds within the project's budget that may be set aside for emergencies or unexpected costs.
Total Expense - The sum of all recorded expenses for the project.
Remaining Budget Without Reserves - The remaining budget after excluding contingency reserves and subtracting expenses.
Remaining Budget With Reserves - The remaining budget including contingency reserves.
The query joins CODBEX_BUDGET and CODBEX_EXPENSE to the CODBEX_PROJECT table using the PROJECT_ID as the common key.
This ensures that all budgets and expenses are associated with their respective projects.
Aggregations:
SUM(BUDGET_AMOUNT): Calculates the total budget for each project.
SUM(BUDGET_CONTINGENCYRESERVES): Calculates the total contingency reserves for each project.
SUM(EXPENSE_AMOUNT): Calculates the total expenses for each project.
Remaining Budget Calculations:
Remaining Budget Without Reserves:
[
\text{(Total Budget - Total Contingency Reserves - Total Expenses)}
]
Remaining Budget With Reserves:
[
\text{(Total Budget - Total Expenses)}
]
Handling Null Values:
COALESCE is used to handle null values for budgets or expenses. If a project does not have budgets or expenses, the system treats them as 0 to avoid breaking the calculations.
Filtering:
The query includes a WHERE condition to filter projects by name using a placeholder parameter (:PROJECT_NAME). This allows dynamic filtering based on user input.
Grouping:
The results are grouped by project name (PROJECT_NAME) to ensure accurate calculations for each project individually.
Sorting:
The results are sorted alphabetically by project name for ease of navigation.
Columns in the Report
Column Name
Description
Project
The name of the project.
Total Budget
The total allocated budget for the project.
Total Contingency Reserves
The portion of the budget set aside for unexpected expenses.
Total Expense
The total expenses incurred for the project.
Remaining Budget Without Reserves
The remaining budget after excluding contingency reserves and expenses.
Remaining Budget With Reserves
The remaining budget including contingency reserves and expenses.
Parameters
Parameter
Type
Default Value
Description
PROJECT_NAME
VARCHAR
%
A filter for project names (e.g., "Project A"). % returns all projects.
This query provides a comprehensive overview of project financials, including dynamic filtering and precise calculations for budgets, reserves, and expenses.
bob4o-afk
commented
2 hours ago
Project Overview Report
Project Report Query Configuration
This document provides an explanation of the query logic and configuration used to generate a Project Report with detailed budget and expense calculations.
Purpose
The goal of this report is to provide a detailed financial overview for each project. It calculates:
Report Breakdown
Data Sources
CODBEX_PROJECT: Contains project details (e.g., project name).CODBEX_BUDGET: Contains budget-related information such asBUDGET_AMOUNTandBUDGET_CONTINGENCYRESERVES.CODBEX_EXPENSE: Contains project expense information.Logic
Joins:
CODBEX_BUDGETandCODBEX_EXPENSEto theCODBEX_PROJECTtable using thePROJECT_IDas the common key.Aggregations:
SUM(BUDGET_AMOUNT): Calculates the total budget for each project.SUM(BUDGET_CONTINGENCYRESERVES): Calculates the total contingency reserves for each project.SUM(EXPENSE_AMOUNT): Calculates the total expenses for each project.Remaining Budget Calculations:
Handling Null Values:
COALESCEis used to handle null values for budgets or expenses. If a project does not have budgets or expenses, the system treats them as 0 to avoid breaking the calculations.Filtering:
WHEREcondition to filter projects by name using a placeholder parameter (:PROJECT_NAME). This allows dynamic filtering based on user input.Grouping:
PROJECT_NAME) to ensure accurate calculations for each project individually.Sorting:
Columns in the Report
Parameters
%%returns all projects.This query provides a comprehensive overview of project financials, including dynamic filtering and precise calculations for budgets, reserves, and expenses.