以下是我的一个用DataReport分组报表打印设置的一段代码:以下可打印出来,就是组标头排序不知怎么设置
DataEnvironment1.Connection1.ConnectionString = strcnn
sql = "SHAPE {SELECT a.ccardid as Id,b.cdate AS bdate, b.cweek AS bweek, b.contime AS bontime,b.conhabitus as bonhabitus,b.cofftime as bofftime,b.coffhabitus as boffhabitus,b.cdepartment as bdepartment,b.cduty as bduty,a.cmenname AS aname, COUNT(a.ccardid) AS acuqingtimes, [zondutytimes] = SUM(CASE WHEN a.conhabitus = '正常上班' THEN 1 ELSE 0 END),[latetimes] = SUM(CASE WHEN a.conhabitus = '迟到' THEN 1 ELSE 0 END),[zoffdutytimes] = SUM(CASE WHEN a.coffhabitus = '正常下班' THEN 1 ELSE 0 END),[leaveEtimes] = SUM(CASE WHEN a.coffhabitus = '早退' THEN 1 ELSE 0 END) FROM T_checkin a LEFT JOIN (SELECT * FROM T_checkin ) b ON b.ccardid = a.ccardid where 1=1"
sql = sql & GroupSql '加查询条件
sql = sql & " GROUP BY a.ccardid, a.cmenname,b.cdate,b.cweek,b.contime,b.conhabitus,b.cofftime,b.coffhabitus,b.cdepartment,b.cduty "
sql = sql & " order by a.ccardid desc " '这里设置无效,打印的记录仍然按升序排序,指的是组标头字段的排序,细节字段的排序则可以在这里设置有效 sql = sql & "} AS CommdKqRd COMPUTE CommdKqRd BY 'Id','aname','bduty','acuqingtimes','zondutytimes','latetimes','zoffdutytimes','leaveEtimes','bdepartment'"'这些都是放在组标头的字段,我想关键是这里的设置加一个order by a.ccardid desc,但不知怎么加。摘要字段使用命令 CommdKqRdS(即分组字段命令名),细节字段使用 CommdKqRd(即最先添加的字段命令名)
DataEnvironment1.Commands.Item("CommdKqRdS").CommandText = sql
DRKq.Show 1
DataEnvironment1.Connection1.ConnectionString = strcnn
sql = "SHAPE {SELECT a.ccardid as Id,b.cdate AS bdate, b.cweek AS bweek, b.contime AS bontime,b.conhabitus as bonhabitus,b.cofftime as bofftime,b.coffhabitus as boffhabitus,b.cdepartment as bdepartment,b.cduty as bduty,a.cmenname AS aname, COUNT(a.ccardid) AS acuqingtimes, [zondutytimes] = SUM(CASE WHEN a.conhabitus = '正常上班' THEN 1 ELSE 0 END),[latetimes] = SUM(CASE WHEN a.conhabitus = '迟到' THEN 1 ELSE 0 END),[zoffdutytimes] = SUM(CASE WHEN a.coffhabitus = '正常下班' THEN 1 ELSE 0 END),[leaveEtimes] = SUM(CASE WHEN a.coffhabitus = '早退' THEN 1 ELSE 0 END) FROM T_checkin a LEFT JOIN (SELECT * FROM T_checkin ) b ON b.ccardid = a.ccardid where 1=1"
sql = sql & GroupSql '加查询条件
sql = sql & " GROUP BY a.ccardid, a.cmenname,b.cdate,b.cweek,b.contime,b.conhabitus,b.cofftime,b.coffhabitus,b.cdepartment,b.cduty "
sql = sql & " order by a.ccardid desc " '这里设置无效,打印的记录仍然按升序排序,指的是组标头字段的排序,细节字段的排序则可以在这里设置有效 sql = sql & "} AS CommdKqRd COMPUTE CommdKqRd BY 'Id','aname','bduty','acuqingtimes','zondutytimes','latetimes','zoffdutytimes','leaveEtimes','bdepartment'"'这些都是放在组标头的字段,我想关键是这里的设置加一个order by a.ccardid desc,但不知怎么加。摘要字段使用命令 CommdKqRdS(即分组字段命令名),细节字段使用 CommdKqRd(即最先添加的字段命令名)
DataEnvironment1.Commands.Item("CommdKqRdS").CommandText = sql
DRKq.Show 1
Private Sub CmdPrint_Click()
Dim sql As String
sql = "SHAPE {select * from table1 where 1=1"
sql = sql & " order by 单号 Desc " '这里设置无效,表格的每张单仍然单号按升序的排序
sql = sql & "} AS command1 COMPUTE command1 BY '单号' ,'采购人'"
DataEnvironment1.Commands.Item("command12").CommandText = sql
DataReport1.Show 1
End Sub
单号和采购人是放在分组标头,如下是报表的显示结果:
采购单
单号:1001 采购人:小明
物品名 数量 单位
钢笔 100 支
铅笔 200 支单号:1002 采购人:小明
物品名 数量 单位
钢笔 300 支
铅笔 200 支
sql = sql & " order by 单号 Desc " '这里设置无效,表格的每张单仍然单号按升序的排序
sql = sql & "} AS command1 COMPUTE command1 BY '单号' ,'采购人'"
这样的语句中SQL语句应该放在{}中,也就是:
"SHAPE {select * from table1 order by 单号 Desc } AS command1 COMPUTE command1 BY '单号' ,'采购人'"
我试了一下,好象是不行,不管用不用DataEnvironment控件