Charting time series from SQL server data

[nlights]

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:

1. accumulated monthly expenditures month-by-month
2. account balance month-by-month
3. estimated balance trend (acquired through linear regression analysis)
4. actual burn rate (line from starting expenditures to ending accumulated expenditure)
5. 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?

[lvasic]

Re: Charting time series from SQL server data

I would use a stored procedure. You can put various calculations in temp tables and then present it all rolled up and ready to chart. I would also try to use a google chart.

[peteconway]

Re: Charting time series from SQL server data

I agree with Lee.

[nlights]

Re: Charting time series from SQL server data

Thank you for your response -- I will do that. I figured as much after some painful exploration inside Xbasic code...

As I was muddling through trying to get AA to run my nested query (nested selects) in Xbasic, the system was clearly not happy with it ("unexpected token: SELECT" where the second select appeared.) Somewhat annoying since the SQL Genie was perfectly happy with the exact same SQL statement.

Oh, well. I took a break from Alpha Anywhere for a year, now I'm back, and so is the pain.

Just FYI here's the SQL statement within Xbasic that fails:

dim sql as c
dim sql1 as c
dim sql2 as c
dim cn as sql::Connection
cn.PortableSQLEnabled = .f.


sql = <<%sql%
	SELECT SUM(a.sumamount) as sumamount,
		   a.YYYYMM AS YEAR_MONTH, 
		   a.MMYYYY AS MONTH_YEAR 
		FROM (
			  SELECT TOP 100000 SUM(ba_link.baamount) as sumamount,
			  					(STR(YEAR(BBATCH_DAT))+' '+STR(MONTH(BBATCH_DAT))) AS YYYYMM, 
			  					CONVERT(varchar(3), BBATCH_DAT, 100)+ CAST(YEAR(BBATCH_DAT) AS VARCHAR(4))AS MMYYYY 
			  FROM ba_link, batch 
			  	WHERE bbatch_id = ba_link.babatch_id AND 
			  		  bafull_acn LIKE '4-45544 %' 
			  	GROUP BY [Bbatch_dat] 
			  	ORDER BY [bbatch_dat]) a 
		GROUP BY YYYYMM,MMYYYY 
		ORDER BY YYYYMM,MMYYYY
 %sql%
 

 dim flag as l
 dim q as sql::Query
 q.parse(sql)   'cannot parse it, craps out at the second SELECT

[nlights]

Re: Charting time series from SQL server data

I would use a stored procedure. You can put various calculations in temp tables and then present it all rolled up and ready to chart. I would also try to use a google chart.

Just curious: Why would you use Google Chart over the native one?

[Michael1954]

Re: Charting time series from SQL server data

Have you looked at DataCache and Rgraph?

You can build nested SQL queries.

[nlights]

Re: Charting time series from SQL server data

Update: Stored procedure works really well, and doesn't add (overall) complexity to the solution (the complexity moves from Xbasic to SQL server, but that's OK, and also more generic since I can simply call the procedure whenever I need the same query somewhere else.)

Thank you for your suggestions.

[peteconway]

Re: Charting time series from SQL server data

Native clientside don't have a legend from what I can see, also Google are loaded with config options.