数据表有下面字段:资料类型,归档日期。现在要做一个报表进行统计,报表显示格式如下:detail中显示:资料类型,归档日期,归档份数(由count(*)得到)。并对每种资料类型的份数进行小计。
我写了下面的sql语句,系统报错,不知错在那里,请高手帮忙!Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordsetconn.Provider = myProvider
conn.ConnectionString = myconnstr
conn.Opensql = "Select 资料类型,归档日期,count(*) as DayTotal from index1 group by 资料类型,归档日期 order by 资料类型,归档日期
sqlstr = "SHAPE {" & sql & "}" & _
" as kkk COMPUTE kkk by 资料类型"(或者直接写成:sqlstr = "SHAPE {Select 资料类型,归档日期,count(*) as DayTotal from index1 group by 资料类型,归档日期 order by 资料类型,归档日期}" & _
" as kkk COMPUTE kkk by 资料类型"
)
rs.Open sqlstr, conn
我写了下面的sql语句,系统报错,不知错在那里,请高手帮忙!Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordsetconn.Provider = myProvider
conn.ConnectionString = myconnstr
conn.Opensql = "Select 资料类型,归档日期,count(*) as DayTotal from index1 group by 资料类型,归档日期 order by 资料类型,归档日期
sqlstr = "SHAPE {" & sql & "}" & _
" as kkk COMPUTE kkk by 资料类型"(或者直接写成:sqlstr = "SHAPE {Select 资料类型,归档日期,count(*) as DayTotal from index1 group by 资料类型,归档日期 order by 资料类型,归档日期}" & _
" as kkk COMPUTE kkk by 资料类型"
)
rs.Open sqlstr, conn
Conn.Provider=MSDataShape
Conn.ConnectionString="Data Provider=sqloledb.1;data source=……"一定是你的连接语句里没有设置这两个。
Sql= "Select * From index1 Order By 资料类型,归档日期
Sqlstr = "SHAPE {" & Sql & "}" & _
" as kkk COMPUTE kkk, COUNT(kkk.'资料类型') " & _
"as 资料份数 by '资料类型 '"
我以前的设置是
conn.Provider = "SQLOLEDB.1"
conn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=mytest;Data Source=SUNRY"这个Provider不支持“shape”关键字,必须用下面来设置:
conn.Provider = "MSDataShape"
conn.ConnectionString = "PROVIDER=MSDataShape;DATA PROVIDER=SQLOLEDB;" & _
"SERVER=;DATABASE=mytest;UID=sa;PWD=;"这样设置就可以支持“shape”关键字。
然后用下面的语句可以生成多重数据集:
Dim sqlOne As String
Dim sqlTwo As String
Dim sqlstr As String
sqlOne = "Select 资料类型,归档日期,count(*) as DayTotal from index1 group by 资料类型, 归档日期 order by 资料类型,归档日期"
sqlTwo = "SHAPE {" & sqlOne & "}" & _
" as kkk COMPUTE kkk, sum(kkk.DayTotal) as SubTotal by 资料类型"
sqlstr = "SHAPE (" & sqlTwo & ")" & _
" as eee COMPUTE eee, sum(eee.SubTotal) as Total "
rsParent.StayInSync = False
rsParent.CursorLocation = adUseClient
rsParent.Open sqlstr, conn
rsParent.Requery
Set DRDatumType.DataSource = rsParent 'rsParent是一个ADODB.Recordset对象 DRDatumType.Sections.Item("DatumType_Detail").Controls.Item("txtType").DataField = "资料类型"
DRDatumType.Sections.Item("DatumType_Detail").Controls.Item("txtDate").DataField = "归档日期"
DRDatumType.Sections.Item("DatumType_Detail").Controls.Item("txtDayToal").DataField = "DayTotal"
DRDatumType.Sections.Item("DatumType_Detail").Controls.Item("txtType").DataMember = "kkk"
DRDatumType.Sections.Item("DatumType_Detail").Controls.Item("txtDate").DataMember = "kkk"
DRDatumType.Sections.Item("DatumType_Detail").Controls.Item("txtDayToal").DataMember = "kkk"
DRDatumType.Sections.Item("DatumType_SubTotal_Footer").Controls.Item("txtSubTotal").DataField = "SubTotal"
DRDatumType.Sections.Item("DatumType_SubTotal_Footer").Controls.Item("txtSubTotal").DataMember = "eee"
DRDatumType.Sections.Item("DatumType_Total_Footer").Controls.Item("txtTotal").DataField = "Total"
DRDatumType.Show 1