Delphi透视表

5.4  透视图表决策分析方案

对数据表中的数据进行分析的时候，有时需要对数据表中的单个字段进行统计，或者是对数据表中的多个字段进行统计，当对数据进行统计后，还可以以图表的形式进行显示。本节将用透视图表来实现以上的功能。

5.4.1  动态透视表分析方案

动态透视表是将透视表的制作过程保存在存储过程中，通过参数传递的形式，将表名及生成透视表的相关字段名传到存储过程中，生成指定样式的透视表。本节主要是利用制作的存储过程，生成多种样式的透视表。

1．方案分析

本方案可以将指定的数据表通过存储过程，动态生成所需要的透视表，并且可以在透视表中选择列中相应的字段，对透视表中的数据进行设置。下面是动态透视表分析方案的制作流程图，如图5.44所示。

图5.44  动态透视表分析方案的制作流程图

2．实施过程

在对数据表进行分析时，可以将数据表中的字段以行、列、数据、统计方式的格式来制作一个透视表，该透视表可以可以根据各列的信息，再次对透视表中的信息进行过滤。下面以生产表为例，来实现一个透视表的制作过程。

    实例位置：光盘/mr/5/5.4/5.4.1/01

在如图5.45所示的“产品生产表”窗体中，通过单击“透视表”按钮，可以弹出“制作透视表”窗体，如图5.46所示。

图5.45 产品生产表

图5.46  制作透视表

在该窗体中，可以设置透视表的页、行、列、数据和统计方式，其中页字段是可选字段，其他的都是必选的，在选择完条件后，单击“生成”按钮，将在“产品生产表”窗体中的表中显示透视表的信息，在单击透视表的列标题时，将在右边的文本框中显示当前列的信息，并通过对列信息的选择，可以对透视表中的数据进行筛选如图5.47所示。

图5.47  生成透视表并进行过滤

程序各窗体中相关组件说明，如表5.29所示。

表5.29                               各窗体的相关组件

对象名	对象类型	属性	值
DataModule1	TDataModule	Name	DataModule1
ADOConnection1	TADOConnection	ConnectionString	连接数据库
ADOQuery1、ADOQuery2	TADOQuery	Connection	ADOConnection1
DataSource1	TDataSource	DataSet	ADOQuery1
Form1	TForm	Caption	产品生产表
Panel1	TPanel	Align	alBottom
BitBtn1、BitBtn2	TBitBtn	Anchors	[akRight,akBottom]
Panel2	TPanel	Align	alRight
CheckListBox1	TCheckListBox		alClient
DBGrid1	TDBGrid
Form2	TForm	Caption	制作透视表
Image1	TImage	Picture	添加一个图片

本实例主要是对MR_Distribution数据库中的tb_AppearTab数据表进行透视表操作。

在制作本实例前，首先要制作一个存储过程Pro_ClairvoyantTab，该存储过程不但可以生成透视表，还可以根据透视表中的相关信息，对已生成的透视表进行过滤。完整代码如下：

定义向存储过程所传递的参数。

CREATE PROCEDURE Pro_ClairvoyantTab

@TableName as varchar(50),                   --实现透视表依据的表名

@PageFieldByColumn as varchar(50),          --页字段依据的列名

@PageFieldValue as varchar(50),             --用来控制透视表页字段的数据

@RowFieldByColumn as varchar(50),           --行字段依据的列名

@RowFieldValue as varchar(200),              --用来控制透视表行字段的数据

@ColumnFieldByColumn as varchar(50),        --列字段依据的列名

@ColumnFieldValue as varchar(200),           --用来控制透视表列字段的数据

@DataFieldByColumn as varchar(50),           --数据字段依据的列名

@DataFieldOperateMethod as varchar(10)      --对数据字段的统计方式

AS

在存储过程中定义变量。

DECLARE @Str as varchar(8000),@ColName as varchar(8000),

@ColumnName as varchar(8000), @PageFieldData as varchar(8000),

@RowFieldData as varchar(8000), @ColumnFieldData as varchar(8000),

@StrOne as varchar(8000),@Temp as varchar(8000),@TemTab as varchar(8000),

@StatIf as varchar(8000)

用于判断各字段中的数据，当字段的数据为空时，显示数据表中的所有信息，当字段的数据不为空时，将各字段的信息做为查询条件进行保存，以便对透视表进行过滤。

set @StatIf=''

if(@ColumnFieldValue='') --将列字段的信息生成SQL条件语句

  set @ColumnFieldData=''

else

  begin

    SET @Temp=@ColumnFieldValue

    SET @TemTab=''

    while (0=0)

    begin

       --在添加的多字段获取第一个字段名

       SET @StrOne = substring(@Temp,1,CHARINDEX(',',@Temp)-1)

       SET @StatIf=@StatIf+' and '+@ColumnFieldByColumn+'='+''''+@StrOne+''''

       SET @TemTab=@TemTab+''''+@StrOne+''''

       SET @Temp=substring(@Temp,CHARINDEX(',',@Temp)+1,1000 )

       if isnull(@Temp,'') <>''

       begin

          SET @TemTab=@TemTab+','

       end

       else

          break

    end

    set @ColumnFieldData=' where '+@ColumnFieldByColumn+' in ('+@TemTab+')'

  end

if(@PageFieldValue='') --将页字段的信息生成SQL条件语句

  set @PageFieldData=@TableName

else

  begin

    SET @Temp=@PageFieldValue

    SET @TemTab=''

    while (0=0)

    begin

       --在添加的多字段中获取第一个字段名

       SET @StrOne = substring(@Temp,1,CHARINDEX(',',@Temp)-1)

       SET @TemTab=@TemTab+@PageFieldByColumn  +'='+''''+@StrOne+''''

       SET @Temp=substring(@Temp,CHARINDEX(',',@Temp)+1,1000 )

       if isnull(@Temp,'') <>'' 

        SET @TemTab=@TemTab+' or '

       else

          break

    end

    set @PageFieldData='(select * from '+@TableName+' where '+@TemTab+@StatIf+')'

  end

if(@RowFieldValue='') --将行字段的信息生成SQL条件语句

  set @RowFieldData=''

else

  begin

    SET @Temp=@RowFieldValue

    SET @TemTab=''

    while (0=0)

    begin

       --在添加的多字段获取第一个字段名

       SET @StrOne = substring(@Temp,1,CHARINDEX(',',@Temp)-1)

       SET @TemTab=@TemTab+''''+@StrOne+''''

       SET @Temp=substring(@Temp,CHARINDEX(',',@Temp)+1,1000 )

       if isnull(@Temp,'') <>'' 

        SET @TemTab=@TemTab+','

       else

          break

    end

    set @RowFieldData='where PageFieldData.'+@RowFieldByColumn+' in('+@TemTab+')'

  end

通过对游标的遍历来生成SQL语句。

EXECUTE ('DECLARE Cursor_Cost  CURSOR FOR SELECT DISTINCT ' + @ColumnFieldByColumn

      + ' from ' + @TableName + @ColumnFieldData + ' for read only') --定义游标

SET @Str='select '+@RowFieldByColumn+'=case grouping('+@RowFieldByColumn+') when 1

then '+''''+'汇总'+''''+' else '+@RowFieldByColumn+' end,'

OPEN Cursor_Cost  --打开游标

while (0=0)

BEGIN --遍历游标

  FETCH NEXT FROM Cursor_Cost INTO @ColumnName --通过游标获取列头信息

  if (@@fetch_status<>0)

     break

  SET @Str = @Str + @DataFieldOperateMethod + '(CASE ' + @ColumnFieldByColumn

        + ' WHEN ''' + @ColumnName + ''' THEN ' + @DataFieldByColumn + ' ELSE Null END)

        AS [' + @ColumnName + '], ' --循环追加SQL语句

END

SET @Str = @Str + @DataFieldOperateMethod + '(' + @DataFieldByColumn + ') AS [汇总] from

        ' + @PageFieldData + ' as PageFieldData ' + @RowFieldData

        + ' group by PageFieldData.' + @RowFieldByColumn --定义SQL语句尾

SET @Str=@Str+' with rollup' 

CLOSE Cursor_Cost

DEALLOCATE Cursor_Cost

PRINT @Str

EXEC(@Str)

GO

下面对动态透视表的制作过程进行全面的讲解。

在创建“产品生产表”窗体时，首先在TDBGrid组件中显示要生成透视表的数据表信息。在“产品生产表”窗体的OnCreate事件完成该操作。代码如下：

procedure TForm1.FormCreate(Sender: TObject);

begin

  Mark := 0;

  ABool := False;

  BitBtn2.OnClick(Sender);

end;

通过以上代码可以看出，在窗体的OnCreate事件中并没有实现在表中显示数据的功能，而是通过调用窗体中的“返回”按钮来实现的。这是因为窗体的OnCreate事件所实现的目的完全包含“返回”按钮的功能，为了节省代码，可以在创建窗体时直接调用其他组件的功能操作。“返回”按钮的相关代码如下：

procedure TForm1.BitBtn2Click(Sender: TObject);

begin

  ABool := False;

  CheckListBox1.Clear;

  with DataModule1.ADOQuery1 do

  begin

    close;

    SQL.Clear;

    SQL.Add('select Give_ID as 编号,Give_Date as 生产时间,Give_Plant as 生产车间,

Give_Group as 生产组,Give_Model as 生产型号,Give_Count as 生产数量 from tb_AppearTab');

    Open;

  end;

  DataModule1.DataSource1.DataSet := DataModule1.ADOQuery1;

  DBGrid1.DataSource := DataModule1.DataSource1;

  F1 := '';

  F2 := '';

  CheckBox1.Caption := '页';

end;

该窗体既显示数据表的信息，也显示生成透视表后的信息，在显示透视表后，可以通过单击数据表的列标头，在CheckListBox1组件中显示当前列的所有信息。该功能可以在DBGrid1组件的OnTitleClick事件中实现，代码如下：

procedure TForm1.DBGrid1TitleClick(Column: TColumn);

var

  i : integer;

  Bool : Boolean;

begin

  if (ABool=True) then

  begin

    Bool := False;

    for i:=0 to ComBox.Items.Count-1 do

      if column.Field.FieldName = ComBox.Items.Strings[i] then

      begin

        Field := column.Field.FieldName;

        Mark := 2;

        break;

      end

      else

      begin

        Field := F3;

        Mark := 3;

      end;

    //向文本框中添加字段信息

    AddField('tb_AppearTab', Field, DataModule1.ADOQuery2, CheckListBox1);

    if column.Field.FieldName='汇总' then

      CheckListBox1.Clear;

  end;

end;

自定义过程AddField()，用于将指定字段中的非重复信息添加到TCheckListBox组件中，并将该组件中的复选框设为可选状态。代码如下：

procedure TForm1.AddField(TabName, Field: String; ADOQuer: TADOQuery;

  Check: TCheckListBox);

var

  i : integer;

begin

    with ADOQuer do

    begin

      close;

      SQL.Clear;

      SQL.Add('select Distinct '+Field+' from '+TabName);

      Open;

    end;

    Check.Clear;

    for i:=0 to ADOQuer.RecordCount-1 do

    begin

      Check.Items.Add(ADOQuer.fieldbyname(Field).AsString);

      Check.Checked[i] := True;

      ADOQuer.Next;

    end;

    Check.Items.Add('全部');

    Check.Checked[ADOQuer.RecordCount] := True;

end;

AddField()过程的参数说明如表5.30所示。

表5.30                           AddField ()过程的参数说明

参数	说明
TabName	数据表名称
Field	数据表中的字段名称
ADOQuer	TADOQuery类型，连接数据表的数据源
Check	TCheckListBox类型，用于显示指定字段中的非重复信息

在CheckListBox1组件中显示数据信息后，要在该组件中对相关的信息进行多选，当选择全选时，选择全部信息，否则，不选中任何信息。代码如下：

procedure TForm1.CheckListBox1Click(Sender: TObject);

var

  i : integer;

begin

  //在CheckListBox1组件中选中全部复选框时，所有的复选框跟随做相同的操作

  if CheckListBox1.Items[CheckListBox1.ItemIndex]='全部' then

    if CheckListBox1.checked[CheckListBox1.Items.Count-1] then

      for i:=0 to CheckListBox1.Items.Count-1 do

        CheckListBox1.checked[i] := True

    else

      for i:=0 to CheckListBox1.Items.Count-1 do

        CheckListBox1.checked[i] := False;

  if CheckListBox1.Checked[CheckListBox1.ItemIndex]=False then

    CheckListBox1.checked[CheckListBox1.Items.Count-1] := False;

end;

当在CheckListBox1组件选择了信息后，可以通过下面的确定按钮，将所选的信息组合成一个字符串，并以参数的形式传送给存储过程Pro_ClairvoyantTab，以便对已生成的透视表信息进行过滤。代码如下：

procedure TForm1.Button1Click(Sender: TObject);

var

  i : Integer;

  Str : String;

begin

  Try

  if F2<>'' then

  begin

    if CheckListBox1.Items.Count>0 then

    begin

      if CheckListBox1.checked[CheckListBox1.Items.Count-1]=True then

        Str := ''

      else

      begin

        for i:=0 to CheckListBox1.Items.Count-2 do  //将选中的信息组合成字符串

        begin

          if CheckListBox1.checked[i]=True then

            Str := Str+CheckListBox1.Items[i]+',';

        end;

        Str := trim(Str);

      end;

      case Mark of

        1 :  V1 := Str;

        2 :  V2 := Str;

        3 :  V3 := Str;

      end;

    end;

    CheckBox1.Caption := '页  '+F1;

    form1.IfClairvoyance('tb_AppearTab','Pro_ClairvoyantTab',form1.F1,

form1.V1,form1.F2,form1.V2,form1.F3,form1.V3,form1.F4,form1.F5,

DataModule1.ADOQuery1,DataModule1.DataSource1,form1.DBGrid1);

    ABool := True;

  end;

  except

  end;

end;

自定义过程IfClairvoyance()，是一个共有过程，它可以被其它单元调用，它的功能是将生成透视表的相关信息以参数的形式传给存储过程，并将生成后的透视表显示在DBGrid1组件上。代码如下：

procedure TForm1.IfClairvoyance(TabName, ProName, FieldName1, FieldValue1,

  FieldName2, FieldValue2,FieldName3, FieldValue3, FieldName4, FieldName5: String;

ADOQuer: TADOQuery; DataSour: TDataSource; DGrid: TDBGrid);

begin

  with DataModule1.ADOQuery1 do

  begin

    close;

    SQL.Clear;

    SQL.Add(ProName+' '+''''+TabName+''''+','+''''+FieldName1+''''+','+''''

+FieldValue1+''''+','+''''+FieldName2+''''+','+''''+FieldValue2+''''+','+''''

+FieldName3+''''+','+''''+FieldValue3+''''+','+''''+FieldName4+''''+','+''''

+FieldName5+'''');

    Open;

  end;

end;

IfClairvoyance()过程的参数说明如表5.31所示。

表5.31                          IfClairvoyance()过程的参数说明

参数	说明
TabName	数据表名称
ProName	存储过程名
FieldName1	设置透视表的页字段名
FieldValue1	页字段的数据信息
FieldName2	设置透视表的行字段名
FieldValue2	行字段的数据信息
FieldName3	设置透视表的列字段名
FieldValue3	列字段的数据信息
FieldName4	设置用于统计的数据字段
FieldName5	设置统计方式
ADOQuer	TADOQuery类型，用于执行SQL语句
DataSour	TDataSource类型，用于连接数据源
DGrid	TDBGrid类型，显示交叉表信息

在该窗体中单击“透视表”按钮，将弹出“制作透视表”窗体，对透视表的生成条件进行设置，下面对“制作透视表”窗体的制作过程进行详细介绍。

在窗体创建时，首先要将设置透视表的页、行、列、数据的下拉列表进行清空，然后将要生成透视表的表格中的字段名以指定的类型分别添加到相应的下拉列表中。代码如下：

procedure TForm2.FormCreate(Sender: TObject);

begin

  ComboBox1.Clear;  //清空列表框中的信息

  ComboBox2.Clear;

  ComboBox3.Clear;

  ComboBox4.Clear;

  FirstFace;

end;

自定义过程FirstFace，是用来将数据表中的字段以指定的类型存入到相应的下拉列表中。代码如下：

procedure TForm2.FirstFace;

var

  i : Integer;

  Stype : String;

  Ttype : TfieldType;

begin

  ComboBox1.Items.Clear;  //清空列表框中的信息

  ComboBox2.Items.Clear;

  ComboBox3.Items.Clear;

  ComboBox4.Items.Clear;

  form1.ComBox := TComboBox.Create(self);  //创建一个下拉列表框，用于存储数据表中的字段

  Self.InsertControl(form1.ComBox);  //将生成的列表框放在窗体上

  form1.ComBox.Clear;

  form1.ComBox.Visible := False;  //不显示创建的下拉列表

  with DataModule1.ADOQuery2 do

  begin

    close;

    SQL.Clear;

    SQL.Add('select * from tb_AppearTab');

    Open;

    for i:=0 to FieldCount-1 do  //以表中字段的个数进行循环

    begin

      Stype := Fields[i].FieldName;  //获取表中的字段名

      Ttype := DataModule1.ADOQuery2.FindField(Stype).DataType;  //获取指定字段名的类型

      if Ttype in [ftString,ftDateTime] then  //判断是否为字符型或日期型

      begin

        ComboBox1.Items.Add(Fields[i].FieldName);  //将字段添加到下拉列表中

        ComboBox2.Items.Add(Fields[i].FieldName);

        ComboBox3.Items.Add(Fields[i].FieldName);

        form1.ComBox.Items.Add(Fields[i].FieldName);

      end;

      //将数据表中的数值型字段添加到相应的下拉列表中

      if not (Ttype in [ftString,ftDateTime]) then

        ComboBox4.Items.Add(Fields[i].FieldName);

    end;

  end;

end;

在下拉列表中选择字段名时，页、行和列的下拉列表中的字段名是相同的，为了避免在选择条件时，选择重复的字段名，当选择其中一个下拉列表的字段时，其余的下拉列表将删除该字段名，下面以列下拉列表为例进行说明，代码如下：

procedure TForm2.ComboBox2Change(Sender: TObject);

begin

  ComboBox3.Items.Clear;

  //将已保存的字段信息存入行列表框中

  ComboBox3.Items.AddStrings(form1.ComBox.Items);

  FieldUpdate(ComboBox2.Text, ComboBox3);  //在下拉列表框中删除指定的字段名

  FieldUpdate(ComboBox1.Text, ComboBox3);

end;

   交叉链接：自定义过程FieldUpdate()已在5.3.4节的实施过程中进行了说明，在这里不作详解。

在设置完生成透视表的条件后，只要单击窗体中的“生成”按钮，便关闭当前按钮，在“产品生产表”窗体中显示生成后的透视表。代码如下：

procedure TForm2.BitBtn1Click(Sender: TObject);

begin

  //下面的变量为共有变量

  form1.F1 := ComboBox1.Text;

  form1.V1 := '';

  form1.F2 := ComboBox2.Text;

  form1.V2 := '';

  form1.F3 := ComboBox3.Text;

  form1.V3 := '';

  form1.F4 := ComboBox4.Text;

  if (ComboBox2.Text='') or (ComboBox3.Text='') or (ComboBox4.Text='') or

(ComboBox5.Text='') then

    showmessage('请选择制作透视表的字段')

  else

  begin

    form1.Button1.OnClick(Sender);

    close;

  end;

end;

3．补充说明

本方案所制作的存储过程，只要传递的参数正确，可以对任意数据表生成相应的透视表。在单击透视表的列标题时，也可以在列标题的下面动态显示含有列信息的文本框，这样，对数据的筛选更具有针对性。

5.4.2  动态透视表扩展方案

动态透视表扩展方案实际上就是对数据表中的各字段信息以不同的范围，逐层进行统计汇总。它可以更好的显示出数据表中各数据的包含关系。本节主要是在透视表的基础上，向数据表中添加字段，并进行汇总。

1．方案分析

本方案主要是利用自定义存储过程将指定的数据表生成一个多列的透视表，通过该透视表可以分析各个字段信息在一定范围内的汇总情况，从而对数据有更深入的了解。下面是动态透视表扩展方案的制作流程图，如图5.48所示。

图5.48  动态透视表扩展方案的流程图

2．实施过程

在对数据表进行分析时，不但可以对单个字段进行统计，还可以对多个字段同时进行统计，并根据顺序的不同，统计的效果也不一样。下面以“产品生产表”为例，详细介绍动态透视表扩展方案的实现过程。

    实例位置：光盘/mr/5/5.4/5.4.2/01

在如图5.49所示的“产品生产表”窗体中，单击“透视表”按钮，可以弹出“制作透视表”窗体，如图5.50所示。

图5.49  产品生产表

图5.50  制作透视表

在该窗体中，可以设置制作透视表的页、行、列、数据和统计方式，其中页字段是可选字段，其他的都是必选的，在选择完条件后，单击“生成”按钮，将在“产品生产表”窗体中的表中显示透视表的信息，单击该窗体的DBGrid1组件，将在窗体内生成一个新窗体，在新窗体中将显示数据表中的字段名，双击字段名，将该字段添加到透视表中。多字段透视表的生成效果如图5.51所示。

图5.51  在“产品生产表”窗体中显示透视表

程序各窗体中相关组件说明，如表5.32所示。“制作透视表”窗体与5.4.1节中的“制作透视表”窗体的结构和功能一样，在这里不作说明。

表5.32                               各窗体的相关组件

对象名	对象类型	属性	值
DataModule1	TDataModule	Name	DataModule1
ADOConnection1	TADOConnection	ConnectionString	连接数据库
ADOQuery1、ADOQuery2	TADOQuery	Connection	ADOConnection1
DataSource1	TDataSource	DataSet	ADOQuery1
Form1	TForm	Caption	产品生产表
Panel1	TPanel	Align	alBottom
BitBtn1、BitBtn2	TBitBtn	Anchors	[akRight,akBottom]
Panel2	TPanel	Align	alClient
DBGrid1	TDBGrid		alLeft
Form2	TForm	Caption	双击添加字段
Panel1	TPanel	Align	alTop
ListBox1	TListBox		alClient

本实例主要是对MR_Distribution数据库中的tb_AppearTab数据表进行透视表操作。

在制作本实例前，首先要制作一个存储过程Pro_MultiColTab，该存储过程可以生成多个列字段的存储过程，并根据列字段的先后顺序，对透视表中的数据进行统计。完整代码如下：

定义向存储过程所传递的参数。

CREATE  PROCEDURE Pro_MultiColTab

@TabName as varchar(50),    --表名

@head as varchar(50),   --行字段

@Data as varchar(50),   --数值字段

@Bunch as varchar(1000),   --添加的多字段

@Amount as int

AS

在存储过程中定义变量。

 DECLARE @StrOne varchar(200)    --字符串中的字段名

 DECLARE @StrTwo varchar(200)   ---字符串中的字段名

 DECLARE @SQL varchar(8000)

 DECLARE @Take int    --字符串中的字段个数

 DECLARE @Estim  varchar(4000)  --用于显示“汇总”位置

 DECLARE @Temp varchar(1000)   --临时字段字符串

 DECLARE @crSQL varchar(8000)   --插入语句

 DECLARE @Stat varchar(50)    --记录字符串中的统计字段名

SET @Take=0

SET @crSQL=''

在@Bunch参数中获取所有的字段名，并在透视表中以列名进行显示。

if @Bunch<>''

begin

  --在添加的多字段中获取第一个字段名

  SET @StrOne = substring(@Bunch,1,CHARINDEX(',',@Bunch)-1)

  SET @Stat = @StrOne

end

else

  SET @StrTwo=''

SET @Temp=substring(@Bunch,CHARINDEX(',',@Bunch)+1,1000 )

if @Amount>1

--在添加的多字段中获取第二个字段名

SET @StrTwo = substring(@Temp,1,CHARINDEX(',',@Temp)-1 )

else

SET @StrTwo=''

SET @SQL = 'select '+ @head+'='+' isnull( case  when grouping('+@Data+')=1  and '

+@head+'  is not null then null'

SET @SQL = @SQL+'  when grouping('+@Data+')=0 then '+@head +'  else '+''''+'汇总'+''''

+'  end ,'

--以上代码对行字段进行汇总

SET @SQL =@SQL+'  case when '+@Stat+' is null then '+''''+'汇总'+''''+' else null end ),'

while @Take<@Amount    --在表中添加新的列信息

begin

  SET @crSQL= @crSQL+@StrOne+'=  case   when grouping('+@Data+')=1 and '+@head+'

<>'+''''+'汇总'+''''+' and '+@StrOne+' is not null then '

 if isnull(@StrTwo,'')<>''

  begin

    SET @crSQL=@crSQL+' ( case  when grouping('+@Data+')=1 and  isnull('

+@StrTwo+','+''''+''+''''+')='+''''+''+''''+' then '+''''+'汇总'+''''+'  end) '

  end

  else

  begin

     SET @crSQL=@crSQL+''''+'汇总'+''''

  end

  SET @crSQL=@crSQL+'  else '+@StrOne+'  end , '

  if isnull(@StrTwo,'')<>''

    SET @StrOne=@StrTwo   --将第二个字段名保存到第一个字段名的变量中

  else

    SET @StrTwo='' 

  if CHARINDEX(',',@Temp) < len(@Temp)

  begin

    SET @Temp=substring(@Temp,CHARINDEX(',',@Temp)+1,1000 )

    SET @StrTwo = substring(@Temp,1,CHARINDEX(',',@Temp)-1 )    --获取下一个字段名

  end

  else

     SET  @StrTwo=''

  SET @Take=@Take+1

end

SET @SQL = @SQL+@crSQL

SET @SQL = @SQL+' sum('+@Data+')  '+@Data+'  from '+@TabName

SET @SQL = @SQL+' group by  '+@head+','+@Bunch+@Data     --在GROUP BY添加新的列字段

SET @SQL = @SQL +' with rollup ' 

SET @SQL = @SQL+' having '+@Stat+' is not null or grouping('+@Data+')=1'

print @SQL

EXEC(@SQL)

GO

下面对动态透视表扩展方案的制作过程进行全面讲解。

在创建“产品生产表”窗体时，首先在TDBGrid组件中显示要生成透视表的数据表信息。在“产品生产表”窗体的OnCreate事件完成该操作。代码如下：

procedure TForm1.FormCreate(Sender: TObject);

begin

  pp := 0;  //共有变量，用于记录在透视表添加列字段的个数

  TableName := 'tb_AppearTab';  //共有变量，

  Button2.OnClick(Sender);

end;

通过以上代码可以看出，在窗体的OnCreate事件中并没有实现在表中显示数据的功能，而是通过调用窗体中的“返回”按钮来实现的。这是因为窗体的OnCreate事件所实现的目的完全包含“返回”按钮的功能，为了节省代码，可以在创建窗体时直接调用其他组件的功能操作。“返回”按钮的相关代码如下：

procedure TForm1.Button2Click(Sender: TObject);

begin

  with DataModule1.ADOQuery1 do

  begin

    close;

    SQL.Clear;

    SQL.Add('select * from tb_AppearTab ');

    open;

  end;

  DataModule1.DataSource1.DataSet := DataModule1.ADOQuery1;

  Self.DBGrid1.DataSource := DataModule1.DataSource1;

  IfBool := false;

  Hunch := '';

end;

该窗体即可以显示数据表信息，也可以显示透视表信息，当显示透视表信息时，单击DBGrid1组件，将弹出“字段”窗体，代码如下：

procedure TForm1.DBGrid1CellClick(Column: TColumn);

begin

if IfBool=True then  //当前是透视表

  form2.Show;  //显示“字段”窗体

end;

在显示“字段”窗体时，先将数据表中的字段名添加到ListBox1组件中，以便对其进行选择。代码如下：

procedure TForm2.FormShow(Sender: TObject);

var

  i : Integer;

begin

  self.Left := form1.F2Width;

  self.top := 5;

  ListBox1.Clear;

  for i:=0 to form1.ComBox.Items.Count-1 do

    ListBox1.Items.Add(form1.ComBox.Items.Strings[i])

end;

在显示“字段”窗体后，可以双击该窗体ListBox1组件中的字段名，同时，通过Pro_MultiColTab存储过程将双击后的字段名添加在透视表中，并重新进行统计。代码如下：

procedure TForm2.ListBox1DblClick(Sender: TObject);

begin

  if (pos(Trim(ListBox1.Items.Strings[ListBox1.ItemIndex]),Form1.Hunch)=0)

and(pos(Trim(ListBox1.Items.Strings[ListBox1.ItemIndex]),Form1.RowField)=0)

and(pos(Trim(ListBox1.Items.Strings[ListBox1.ItemIndex]),Form1.CountField)=0) then

  begin

    Form1.pp := Form1.pp+1;

    Form1.Hunch := Form1.Hunch+Trim(ListBox1.Items.Strings[ListBox1.ItemIndex])+',';

    with DataModule1.ADOQuery1 do

    begin

      close;

      SQL.Clear;

      SQL.Add('Pro_MultiColTab'+' '+''''+form1.TableName+''''+','+''''

+Trim(Form1.RowField)+''''+','+''''+Trim(form1.CountField)+''''+','+''''

+Trim(Form1.Hunch)+''''+','+IntToStr(Form1.pp));

      Open;

    end;

  end;

end;

在“产品生产表”窗体中单击“透视表”按钮，将弹出“制作透视表”窗体，该窗体与5.4.1中“制作透视表”窗体的操作步骤与功能完全一样，在这里不作说明。

3．补充说明

本方案为了能体现程序的通用性，并没有在窗体显示时，在SQL语句中将显示数据表中的字段以中文设置别名进行显示。如果想让TDBGrid组件以中文字段名进行显示。可以在tb_ChinaTable（该表在5.3.4节中已进行介绍）数据表中记录指定数据表中各字段的中文解释。在窗体显示时，可以通过对tb_ChinaTable数据表中字段的查找，改变字段名称。代码如下：

procedure TForm1.FormShow(Sender: TObject);

var

  i : Integer;

begin

  with ADOQuery1 do

  begin

    for i:=0 to DBGrid1.Columns.Count-1 do

    begin

      Close;

      SQL.Clear;

      SQL.Add('select * from tb_ChinaTable where TableName='+''''+'tb_AppearTab '+''''

+' and FieldName='+''''+DBGrid1.Columns[i].Title.Caption+'''');

      Open;

      if RecordCount>0 then

        DBGrid1.Columns[i].Title.Caption := ADOQuery1.FieldByName('China').AsString;

    end;

  end;

end;

   交叉链接：在生成透视表时，将下拉列表中的英文字段改为中文字段，可参考5.3.4节。

5.4.3  透视图分析方案

透视图实际上就是将统计后的透视表以透视图的形式进行显示。在对数据进行分析时，透视图比透视表更具有直观性。本节主要介绍在透视表的基础上，制作相应的透视图。

1．方案分析

透视图是根据透视表的数据结果而生成的，它是将透视表中除第一列的所有列字段，分别与第一列的字段组合成N个新的数据表，再将这些数据表与TDBGrid组件中的各图表相连接，从而生成一个相应的透视图。

透视图的制作流程图，如图5.52所示

图5.52  透视图分析方案流程图

2．实施过程

下面“产品生产表”为例，详细介绍产品生产情况透视表和透视图的制作过程。

    实例位置：光盘/mr/5/5.4/5.4.3/01

透视图是根据相应的透视表而生成的，要想制作透视图，首先要生成一个透视表，本实例是根据5.4.1节的例子来制作透视图的。生成的透视表效果如图5.53所示，透视图效果如图5.54所示。

图5.53  产品生产表的透视表效果

图5.54  根据透视表生成的透视图效果

因为透视图是基于透视表而生成的，在“产品生产表”窗体的“确定”按钮中，利用自定义过程来完成透视图的制作，主要代码如下：

procedure TForm1.Button1Click(Sender: TObject);

var

  i : Integer;

  Str : String;

begin

  ...

  ..

  ..

    ABool := True;

    FObjectList.Clear;  //是一个TobjectList类的共有变量，用于存放动态生成的ADO组件

    //设置透视图

    ClairvoyanceChart(DataModule1.ADOQuery1, DBChart1, DataModule1.ADOQuery1.

Fields.Count-2);   //制作透视图

    DBChart1.Title.Text.Text := form1.Caption+'的透视图效果';

  end;

  except

  end;

end;

  注意：因为本例与5.4.1节中的例子基本相同，所以只给出制作透视图的代码，省略代码可参考5.4.1节中的相关代码。

自定义过程ClairvoyanceChart()用于根据透视表的列字段个数，动态生成ADO组件和图表，并将透视表中的第一列字段与各列字段进行组合，存入到动态生成的ADO组件中，再将图表与ADO组件相连接，生成透视图。该过程的相关代码如下：

procedure TForm1.ClairvoyanceChart(ADOQuer: TADOQuery; DChart : TDBChart; n: Integer);

var

  i : Integer;

  ADOTemp:TADOQuery;

  series : TChartSeries;  //图表类型

begin

  if DChart.SeriesCount>0 then

    for i:=dbchart1.SeriesCount-1 downto 0  do

      DChart.SeriesList.Delete(i);

  for i:=0 to n do  //动态生成多个TADOQuery组件

  begin

    ADOTemp := TADOQuery.Create(self);

    ADOTemp.Connection := DataModule1.ADOConnection1;

    FObjectList.Add(ADOTemp);

  end;

  ADOQuer.Fields.Remove(ADOQuer.Fields[ADOQuer.Fields.Count-1]);

  ADOQuer.SaveToFile('C:/T.text');

  ADOQuer.First;

  for i:=0 to n-1 do  //使动态创建的数据源与图表进行连接

  begin

    //动态生成图表

    series := TBarSeries.Create(self);

    DChart.AddSeries(series);

    //向动态生成的ADO组件件插入透视表中第一列与各列的组合图表

    TADOQuery(FObjectList.Items[i]).Active := False;

    TADOQuery(FObjectList.Items[i]).LoadFromFile('C:/T.text');

    TADOQuery(FObjectList.Items[i]).Active := True;

    //去除透视表的最后一行数据，即汇总记录

    TADOQuery(FObjectList.Items[i]).Last;

    TADOQuery(FObjectList.Items[i]).Filter := F2+'<>'+''''+TADOQuery

(FObjectList.Items[i]).fieldbyname(F2).AsString+'''';

    TADOQuery(FObjectList.Items[i]).Filtered := True;

    //图表与ADO相连接

    DChart.Series[i].Title := TADOQuery(FObjectList.Items[i]).Fields[i+1].FieldName;

    DChart.Series[i].DataSource := TADOQuery(FObjectList.Items[i]);

    DChart.Series[i].XLabelsSource := Trim(F2);

    DChart.Series[i].YValues.ValueSource := TADOQuery

(FObjectList.Items[i]).Fields[i+1].FieldName;

    DChart.Series[i].Marks.Style := smsValue;

  end;

  TBarSeries(DChart.Series[0]).MultiBar := TMultiBar(2);  //对图表进行组合

end;

ClairvoyanceChart()过程的参数说明如表5.33所示。

表5.33                           ClairvoyanceChart ()过程的参数说明

参数	说明
ADOQuer	TADOQuery类型，存储透视表的ADO组件
DChart	TDBChart类型，生成透视图的图表
N	透视表中列字段的个数

3．补充说明

在制作透视图时，可以根据实际情况选择进行组合的图表类型，例如，可以将条型图或线型图进行组合，这样的组合，便于区分各自不同的发展规律。