1. 三维报表

1.1. 三维交叉(横一，纵二)

1.2. 使用说明

缺省的时候，是系统自动根据结果中的字段来产生SQL语句及结果的，但同样允许直接写SQL语句，只是SQL的格式须按规定的格式书写。形如：

select
    hfld(横向字段) ,
    v1fld(纵向字段一) ,
    v2fld(纵向字段二) ,
    dfld(数据字段)
from .....

如果是存储过过程来写，则需要向临时表 ##res01(hfld,v1fld,v2fld,dfld,horder,vorder) 中写数据，其中horder,vorder字段赋0即可。

insert into ##res01(hfld,v1fld,v2fld,dfld,horder,vorder)
select h1fld,h2fld,vfld,dfld,0,0 from yourTable
--exec your_procedure param1,param2[,...]

1.2.1. 结果格式

• 显示模板

此处举例说明,其它结果格式参见软件模板。

• 模板源码

<table class="tableline" id="tb_csr3_001" cellspacing="0" cellpadding="2" border="0" cross="3">
    <caption class="f16 fu" style="padding-bottom: 4px; font-family: 黑体" align="center">&#160; 测试报表 &#160;</caption>
    <tbody name="title">
        <tr>
            <td align="center" colspan="3">&#160;</td> 
            <td class="c3hfld aggrfield" name="hfld1" align="center">横向内容对应的字段</td> 
            <td class="nouse" align="right" bgcolor="#eeeeee">&gt;&gt;&gt;</td> 
            <td class="fb" align="center" name="sum_caption">合计<span aggr="0" col="0" /></td> 
        </tr> 
    </tbody> 
    <tbody name="details" class="mynone"> 
        <tr> 
            <td class="donotneedattr" align="center">{order}</td> 
            <td class="c3v1fld donotneedattr aggrfield {_v1fld1_s1}" nouse="{_v1fld1_s2}" valign="center" name="v1fld1">纵向内容对应的字段1</td> 
            <td class="c3v2fld aggrfield" name="v2fld1">纵向内容对应的字段2</td> 
            <td class="c3data aggrdata" align="right" name="dfld" loc="{vindex}_1" fmt="#,##">数据字段</td> 
            <td class="nouse" align="right" bgcolor="#eeeeee">+</td> 
            <td class="c3haggr aggrmethod fb" align="right" name="sum" fmt="#,##">统计方法</td> 
        </tr> 
        <tr class="nouse"> 
            <td align="center" bgcolor="#eeeeee">1</td> 
            <td>&#160;</td> 
            <td bgcolor="#eeeeee">+</td> 
            <td align="right" bgcolor="#eeeeee">+</td> 
            <td align="right" bgcolor="#eeeeee">+</td> 
            <td bgcolor="#eeeeee">&#160;</td>
        </tr> 
        <tr class="nouse"> 
            <td align="center" bgcolor="#eeeeee">2</td> 
            <td>&#160;</td>
            <td class="c3v2caption fi" align="center">小计</td>
            <td class="c3v2aggr aggrmethod fi" align="right" name="sum" fmt="#,##">统计方法</td> 
            <td bgcolor="#eeeeee">&gt;&gt;&gt;</td> 
            <td bgcolor="#eeeeee">&#160;</td>
        </tr> 
        <tr class="nouse"> 
            <td align="center" bgcolor="#eeeeee">3</td> 
            <td valign="top" bgcolor="#eeeeee" rowspan="3">VVV1</td> 
            <td bgcolor="#eeeeee">&#160;</td>
            <td bgcolor="#eeeeee">&#160;</td> 
            <td bgcolor="#eeeeee">&#160;</td> 
            <td bgcolor="#eeeeee">&#160;</td>
        </tr> 
        <tr class="nouse"> 
            <td align="center" bgcolor="#eeeeee">4</td> 
            <td bgcolor="#eeeeee">&#160;</td>
            <td bgcolor="#eeeeee">&#160;</td> <td bgcolor="#eeeeee">&#160;</td>
            <td bgcolor="#eeeeee">&#160;</td>
        </tr> 
        <tr class="nouse"> 
            <td align="center" bgcolor="#eeeeee">5</td> 
            <td align="center" bgcolor="#eeeeee">小计</td> 
            <td bgcolor="#eeeeee">&#160;</td> <td bgcolor="#eeeeee">&#160;</td>
            <td bgcolor="#eeeeee">&#160;</td>
        </tr> 
    </tbody> 
    <tbody name="foot"> 
        <tr> 
            <td class="fb" align="center" colspan="3">合计<span aggr="0" row="0" /></td> 
            <td class="c3v1aggr aggrmethod fb" align="right" name="sum" fmt="#,##" loc="v1">统计方法</td> 
            <td class="nouse" bgcolor="#eeeeee">&#160;</td> 
            <td class="c3total aggrmethod fb" align="right" name="sum" fmt="#,##">统计方法</td> 
        </tr> 
    </tbody>
</table>

注意：

<td class="c3data aggrdata" 
    align="right" 
    name="dfld" 
    loc="{vindex}_1" 
    fmt="#,##">
        数据字段
</td>

其中，fmt="#,##"决定最终显示格式。所以，当发现结果显示与你的sql实际结果不符时，通过调节这个参数。比如以上实例中，由于实际结果比较小，故需调整要精确度，即fmt="#,#####"

1.2.2. sql语句

-- sql设计器

insert into ##res01(hfld,v1fld,v2fld,dfld,horder,vorder) 
exec ebs_extend.dbo.rpt_jhdz_advances_utilization_rate '{ed_date1} 00:00:01','{ed_date2} 23:59:59'

-- 存储过程实现

USE [ebs_extend]
GO
/****** Object: StoredProcedure [dbo].[rpt_wechat_attention_rate_by_branch] Script Date: 09/30/2016 10:36:23 ******/

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO

alter procedure [dbo].rpt_jhdz_advances_utilization_rate
(
    @date1 datetime,
    @date2 datetime
)
as

begin

    SET NOCOUNT ON;

    declare @advances_utiliztion_amount table 
    ( 
        branchid int , 
        branchname varchar(100), 
        totalAmount bigint
    ) 

    declare @advances_utiliztion_used_amount table 
    ( 
        branchid int , 
        yearList varchar(100) , 
        monthList varchar(100), 
        usedamount bigint 
    ) 

    --插入预存总数量 
    insert into @advances_utiliztion_amount 
    (
        branchid,
        branchname,
        totalAmount
    ) 
    select 
        bc.bc_id,
        bc_company,
        COUNT(1) 
    from 
        ebs.dbo.mps_compdepart dpt with(nolock) ,
        ebs_extend.dbo.mps_compcustomervoucher v with(nolock),
        ebs.dbo.mps_branchcompany bc with(nolock) 
    where ccv_saledate>=@date1 
        and ccv_saledate<=@date2 
        and ccv_caption = '购机类代金券' 
        and lower(substring(ccv_sourceid,0,CHARINDEX('_',ccv_sourceid))) = lower(dp_code) 
        and dp_branch = bc_id 
        group by bc_id,bc_company

    insert into @advances_utiliztion_used_amount 
    (
        branchid,
        yearList,
        monthList,
        usedamount
    ) 
    select 
        bc.bc_id,
        YEAR(ccv_usedate),
        MONTH(ccv_usedate),
        COUNT(1) 
    from 
        ebs.dbo.mps_compdepart dpt with(nolock) , 
        ebs_extend.dbo.mps_compcustomervoucher v with(nolock),
        ebs.dbo.mps_branchcompany bc with(nolock) 
    where ccv_saledate>=@date1 
        and ccv_saledate<=@date2 
        and ccv_caption = '购机类代金券' 
        and lower(substring(ccv_sourceid,0,CHARINDEX('_',ccv_sourceid))) = lower(dp_code) 
        and ccv_usefee>0 
        and dp_branch = bc_id 
    group by bc_id ,YEAR(ccv_usedate),MONTH(ccv_usedate)

    select 
        used.yearList+'年'+case when len(used.monthList)=1 then '0'+
            used.monthList else used.monthList end +'月' h1,
        '预存款使用率' v1, 
        total.branchname v2,
        round(convert(decimal(18,6),used.usedamount/convert(decimal(18,2),total.totalAmount )),6) data,
        0 horder,
        0 vorder 
    from 
        @advances_utiliztion_amount total,
        @advances_utiliztion_used_amount used
    where 
        used.branchid = total.branchid

end

1.2.3. 报表系统提供的临时表- 与业务有关

• 与客户、员工、商品、设备等有关的报表