Creating a 4 Series bar chart

[realsol]

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?

[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;

[realsol]

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?

[realsol]

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.

[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.