Page 1 of 1
Creating a 4 Series bar chart
Posted: Tue May 02, 2017 7:22 pm
by 16880166
Right now I am creating a chart showing up to 4 category types (income, expense, equity and other). As of now, I am looping the same code using four different TQueries in a for-do loop. The only difference between each SQL statement is whether the transaction belongs to one of the four series or 'category types'.
1. Is it possible to 'filter' one query to create different series with one query?
2. or is possible to 'lock in the values after each SQL execute and still use 1 component?
Re: Creating a 4 Series bar chart
Posted: Wed May 03, 2017 8:55 am
by yeray
Hello,
I understand you have something like:
Code: Select all
for i:=0 to 3 do
begin
if i=0 then
begin
SQL:='*** income ***';
...
Series1.DataSource:=Query1;
end
else if i=1 then
begin
SQL:='*** expense ***';
...
Series2.DataSource:=Query1;
end
else if i=2 then
begin
SQL:='*** equity ***';
...
Series3.DataSource:=Query1;
end
else if i=3 then
begin
SQL:='*** other ***';
...
Series4.DataSource:=Query1;
end;
end;
And you would like to do something like:
Code: Select all
SQL:='*** income, expense, equity, other ***';
...
Series1.DataSource:=Query1;
Series2.DataSource:=Query1;
Series3.DataSource:=Query1;
Series4.DataSource:=Query1;
You should use the ValueSource property in the series valuelists to assign a column from the query.
Here it is an example using a single query call to populate two series.
Code: Select all
var Series1: TBarSeries;
Series2: TPointSeries;
Query1: TQuery;
procedure TForm1.FormCreate(Sender: TObject);
begin
DBChart1.View3D:=False;
Series1:=DBChart1.AddSeries(TBarSeries) as TBarSeries;
Series2:=DBChart1.AddSeries(TPointSeries) as TPointSeries;
Query1:=TQuery.Create(Self);
Query1.DatabaseName:='TeeChart Pro Database';
Query1.SQL.Text:='SELECT LASTNAME, SALARY, BIRTH_DATE From Employee';
Series1.DataSource:=Query1;
Series2.DataSource:=Query1;
Series1.YValues.ValueSource:='SALARY';
Series1.XLabelsSource:='LASTNAME';
Series1.Marks.Style:=smsValue;
Series2.YValues.ValueSource:='BIRTH_DATE';
Series2.XLabelsSource:='LASTNAME';
Series2.YValues.DateTime:=True;
Series2.Marks.Visible:=True;
Series2.Marks.Style:=smsValue;
Series2.OnGetMarkText:=SeriesGetMarkText;
Query1.Active:=True;
end;
procedure TForm1.SeriesGetMarkText(Sender: TChartSeries; ValueIndex: Integer; var MarkText: string);
begin
if (Sender<>nil) and (ValueIndex>-1) then
MarkText:=FormatDateTime('dd/mm/yyyy', Sender.YValue[ValueIndex]);
end;
Re: Creating a 4 Series bar chart
Posted: Wed May 03, 2017 6:33 pm
by 16880166
I want to do what you showed in your first code example. I want to use one Query for each series in a bar chart with 4 bars displaying by month. Each bar represents 1 of 4 values included in the 'Category Type' datafield. But if I am using one Query, how does series1 still hold the correct value if the SQL changes for Series2?
Re: Creating a 4 Series bar chart
Posted: Wed May 03, 2017 6:54 pm
by 16880166
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.
Re: Creating a 4 Series bar chart
Posted: Thu May 04, 2017 9:06 am
by yeray
Hello,
I believe this is more a SQL doubt than a TChart issue.
The goal would be to create a single SQL query merging those two different queries, generating different columns to link to the series.