Creating a 4 Series bar chart

TeeChart FireMonkey (Windows,OSX,iOS & Android) for Embarcadero RAD Studio, Delphi and C++ Builder (XE5+)

Creating a 4 Series bar chart

Postby realsol » Tue May 02, 2017 7:22 pm

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?
realsol
Newbie
Newbie
 
Posts: 47
Joined: Mon Feb 27, 2017 12:00 am

Re: Creating a 4 Series bar chart

Postby Yeray » Wed May 03, 2017 8:55 am

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;
Best Regards,

ImageYeray Alonso
Development & Support
Steema Software
Av. Montilivi 33, 17003 Girona, Catalonia (SP)
Image Image Image Image Image Image ImagePlease read our Bug Fixing Policy
Yeray
Site Admin
Site Admin
 
Posts: 8032
Joined: Tue Dec 05, 2006 12:00 am
Location: Girona, Catalonia

Re: Creating a 4 Series bar chart

Postby realsol » Wed May 03, 2017 6:33 pm

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
Newbie
Newbie
 
Posts: 47
Joined: Mon Feb 27, 2017 12:00 am

Re: Creating a 4 Series bar chart

Postby realsol » Wed May 03, 2017 6:54 pm

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.
Attachments
2017-05-03_1152.png
2017-05-03_1152.png (18.7 KiB) Viewed 177 times
realsol
Newbie
Newbie
 
Posts: 47
Joined: Mon Feb 27, 2017 12:00 am

Re: Creating a 4 Series bar chart

Postby Yeray » Thu May 04, 2017 9:06 am

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.
Best Regards,

ImageYeray Alonso
Development & Support
Steema Software
Av. Montilivi 33, 17003 Girona, Catalonia (SP)
Image Image Image Image Image Image ImagePlease read our Bug Fixing Policy
Yeray
Site Admin
Site Admin
 
Posts: 8032
Joined: Tue Dec 05, 2006 12:00 am
Location: Girona, Catalonia


Return to FMX

Who is online

Users browsing this forum: No registered users and 3 guests