This is what I am doing now and getting ready to add 2 more series for a comparison to last year. So I want to end up with Income/Expenses bars for this year and 2 bars representing last year for comparison.
Here is my code for the 2 chart method:
- Code: Select all
for i := 1 to 2 do
Begin
if i = 1 then TopQuery := BarQuery else TopQuery := BarQuery2;
begin
TopQuery.Active := false;
TopQuery.SQL.Clear;
//TopQuery.SQL.Add('SELECT *, ABS(SUM (AMOUNT)) AS ABSTOTAL,');
TopQuery.SQL.Add('SELECT *, ');
TopQuery.SQL.Add(' SUM (AMOUNT) AS TOTAL,');
TopQuery.SQL.Add(' (cast(strftime(''%Y%m'', date) as integer)) AS YEAR_MONTH,');
TopQuery.SQL.Add(' date(DATE,''start of month'') AS MONTH_START,');
TopQuery.SQL.Add(' case strftime(''%m'', DATE) when ''01'' then ''Jan'' when ''02'' then ''Feb'' when ''03'' then ''Mar'' when ''04''');
TopQuery.SQL.Add(' then ''Apr'' when ''05'' then ''May'' when ''06'' then ''Jun'' when ''07'' then ''Jul'' when ''08'' then ''Aug'' when ''09''');
TopQuery.SQL.Add(' then ''Sep'' when ''10'' then ''Oct'' when ''11'' then ''Nov'' when ''12'' then ''Dec'' else '''' end');
TopQuery.SQL.Add(' || " ''" || substr(strftime(''%Y'', date),3) as MON_YEAR,');
TopQuery.SQL.Add('strftime(''%d'', date) AS DAY,');
TopQuery.SQL.Add('(cast(strftime(''%d'', date) as integer)) / 7 + 1 AS WEEK,');
TopQuery.SQL.Add('strftime(''%m'', date) AS MONTH,');
TopQuery.SQL.Add('(cast(strftime(''%m'', date) as integer) + 2) / 3 as QTR');
TopQuery.SQL.Add('FROM TRANSACTIONS');
TopQuery.SQL.Add('INNER JOIN CATEGORIES');
TopQuery.SQL.Add('ON TRANSACTIONS.TREE_NAME=CATEGORIES.CatName');
TopQuery.SQL.Add(Where);
if I = 1 then
begin
if Where = '' then TopQuery.SQL.Add('Where CATEGORIES.Cat_Type = ''Income Categories''') else
TopQuery.SQL.Add('AND CATEGORIES.Cat_Type = ''Income Categories''');
end else
begin
if Where = '' then TopQuery.SQL.Add('Where CATEGORIES.Cat_Type = ''Expense Categories''') else
TopQuery.SQL.Add('AND CATEGORIES.Cat_Type = ''Expense Categories''');
end;
TopQuery.SQL.Add('AND Date BETWEEN "' + FormatDateTime('yyyy-mm-dd',IncExpStart) + '" AND "' + FormatDateTime('yyyy-mm-dd',IncExpEnd) + '"');
//TopQuery.SQL.Add('CATEGORIES.CAT_TYPE, YEAR_MONTH');
TopQuery.SQL.Add('GROUP BY MON_YEAR');
TopQuery.SQL.Add('ORDER BY YEAR_MONTH ASC, CATEGORIES.ROW_ID;');
try
DBChart2.SubTitle.Text.Clear;
DBChart2.SubTitle.Text.Add(DateToStr(IncExpStart) + ' to ' +DateToStr(IncExpEnd));
TopQuery.Active := true;
except
if i = 1 then Series2.Active := false else Series3.Active := false;
If DebugLog.Visible then DebugLog.Lines.Add(TopQuery.Name + ' ERROR: '+ TopQuery.SQL.Text);
if (Series2.Active = false) and (Series3.Active = false) then ChartType.ItemIndex := 1;
//
end;
//ShowMessage(TopQuery.Name + '=' + TopQuery.SQL.Text);
end;
end;
Creating 3 more Queries is no problem, but I want to figure out how I could simplify it. Both Series are the same including database settings (attached.