I am creating a new dashboard app for an existing accounting system, and I'm trying to streamline coding as much as possible. Here's the project description:
Objective:
Create a chart that shows:
The resulting chart would look something like this (a rather messy example):
chartsample.jpg
Data
Day-by-day expenditures against a given account, aggregated to month-by-month accumulated expenditures through an SQL query (the data resides in SQL server 2008)
Approach
I will create custom data series for the chart by using Xbasic and SQL statements. (Another option would be to generate this data through stored procedures, but that could get messy)
I'm planning to get the monthly expenditure totals with a simple SQL statement, then iterate through the resulting data in Xbasic to calculate accumulated expenditures for each month and fill in missing (zero expenditure) months
I can run linear regression as a separate SQL query to get the start and end points for the slope
This seems like a fair amount of manual coding in Xbasic, though, and even more to make it generic enough, but I assume there's really no way around it. Any thoughts?
Objective:
Create a chart that shows:
- accumulated monthly expenditures month-by-month
- account balance month-by-month
- estimated balance trend (acquired through linear regression analysis)
- actual burn rate (line from starting expenditures to ending accumulated expenditure)
- target burn rate (accumulated "ideal" expenditure line from starting expenditure to ideal accumulated total expenditure at the end date that would bring the account balance to zero (the budget is completely spent by the account end date)
The resulting chart would look something like this (a rather messy example):
chartsample.jpg
Data
Day-by-day expenditures against a given account, aggregated to month-by-month accumulated expenditures through an SQL query (the data resides in SQL server 2008)
Approach
I will create custom data series for the chart by using Xbasic and SQL statements. (Another option would be to generate this data through stored procedures, but that could get messy)
I'm planning to get the monthly expenditure totals with a simple SQL statement, then iterate through the resulting data in Xbasic to calculate accumulated expenditures for each month and fill in missing (zero expenditure) months
I can run linear regression as a separate SQL query to get the start and end points for the slope
This seems like a fair amount of manual coding in Xbasic, though, and even more to make it generic enough, but I assume there's really no way around it. Any thoughts?
Comment