使用rdlc的问题！！！

[wcbenben]

我第一次做报表！使用是微软自带的报表工具！
我现在就想让窗体上的DataGridView里的内容形成一个报表！
DataGridView的数据源并不是数据库中单独的一个表中得到的,是通过多表统计得到的！
我试着通过这个DataGridView里的字段建了表（视图），然后生成数据源，在报表文件中直接将字段拖入。
但是其中的聚合字段(sum())无法显示出来！？
有没有啥解决办法！？

[weisai]

直接使用SQL（你DataGridView使用的存储过程或SQL语句）查询出的结果集作为报表数据源不行吗

[wcbenben]

现在的问题好像就是碰到sum(),这样的聚合字段！报表不认！

[weisai]

你是指SQL语句里的sum吗？
水晶报表有汇总功能，插入分租汇总即可

[wcbenben]

这么样吧 我把我的DATAGRIDVIEW的数据源sql语句贴出来看看吧！
SELECT top 100000 1 as ID,zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,

case when zhanghaozhuangtai=1 then '正常卡' when zhanghaozhuangtai=2 then '挂失卡' when zhanghaozhuangtai=3 then '转账卡' when zhanghaozhuangtai=4 then '已退卡' when zhanghaozhuangtai=5 then '注销卡'end zhanghaozhuangtai,

kayajin,sum(chongzhijine),sum(jicixiaofei),sum(xiaofeijine),sum(tuikuanjine),sum(tuikajine),zhanghaoyue from

(SELECT top 100000 1 as ID,zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin, sum(Vchongzhicaozuobiao.chongzhijine) as chongzhijine,0 jicixiaofei,0 xiaofeijine,0 tuikuanjine,0 tuikajine,zhanghaoyue FROM Vchongzhicaozuobiao WHERE (1=1) and (chongzhishijian >= '2008-06-25') and (chongzhishijian <= '2008-07-11') group by zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin,zhanghaoyue
Union ALL SELECT top 100000 1 as ID,zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin, 0 chongzhijine,count(1) jicixiaofei,0 xiaofeijine,0 tuikuanjine,0 tuikajine,zhanghaoyue FROM VJiCiXiaoFei WHERE (1=1) and (caijishijian >= '2008-06-25') and (caijishijian <= '2008-07-11') group by zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin,zhanghaoyue

Union ALL SELECT top 100000 1 as ID,zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin, 0 chongzhijine,0 jicixiaofei,sum(cast(xiaofeijine as decimal(15,2))) as xiaofeijine,0 tuikuanjine,0 tuikajine,zhanghaoyue FROM VZhengChangXiaoFei WHERE (1=1) and (caijishijian >= '2008-06-25') and (caijishijian <= '2008-07-11') group by zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin,zhanghaoyue

Union ALL SELECT top 100000 1 as ID,zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin, 0 chongzhijine,0 jicixiaofei,0 xiaofeijine,sum(Vtuikuancaozuobiao.tuikuanjine) as tuikuanjine,0 tuikajine,zhanghaoyue FROM Vtuikuancaozuobiao WHERE (1=1) and (tuikuanshijian >= '2008-06-25') and (tuikuanshijian <= '2008-07-11') group by zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin,zhanghaoyue

Union ALL SELECT top 100000 1 as ID,zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin, 0 chongzhijine,0 jicixiaofei,0 xiaofeijine,0 tuikuanjine,sum(Vtuikacaozuobiao.hejijine) as tuikajine,zhanghaoyue FROM Vtuikacaozuobiao WHERE (1=1) and (tuikashijian >= '2008-06-25') and (tuikashijian <= '2008-07-11') group by zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin,zhanghaoyue )

a group by zhanghao,mingzi,bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,kayajin,zhanghaoyue

[wcbenben]

这是我 建的一个专为打印 的视图！


SELECT TOP 100000 1 AS ID, zhanghao, mingzi, bumenmingcheng, kaleibiemingcheng,
CASE WHEN zhanghaozhuangtai = 1 THEN '正常卡' WHEN zhanghaozhuangtai = 2 THEN
'挂失卡' WHEN zhanghaozhuangtai = 3 THEN '转账卡' WHEN zhanghaozhuangtai = 4
THEN '已退卡' WHEN zhanghaozhuangtai = 5 THEN '注销卡' END AS zhanghaozhuangtai,
kayajin, SUM(chongzhijine) AS chongzhijine, SUM(jicixiaofei) AS jicixiaofei,
SUM(xiaofeijine) AS xiaofeijine, SUM(tuikuanjine) AS tuikuanjine, SUM(tuikajine)
AS tuikajine, zhanghaoyue
FROM
( SELECT TOP 100000 1 AS ID, zhanghao, mingzi, bumenmingcheng,
kaleibiemingcheng, zhanghaozhuangtai, kayajin,
SUM(Vchongzhicaozuobiao.chongzhijine) AS chongzhijine, 0 jicixiaofei,
0 xiaofeijine, 0 tuikuanjine, 0 tuikajine, zhanghaoyue
FROM Vchongzhicaozuobiao
WHERE (1 = 1)
GROUP BY zhanghao, mingzi, bumenmingcheng, kaleibiemingcheng,
zhanghaozhuangtai, kayajin, zhanghaoyue

UNION ALL
SELECT TOP 100000 1 AS ID, zhanghao, mingzi, bumenmingcheng,
kaleibiemingcheng, zhanghaozhuangtai, kayajin, 0 chongzhijine, COUNT(1)
jicixiaofei, 0 xiaofeijine, 0 tuikuanjine, 0 tuikajine, zhanghaoyue
FROM VJiCiXiaoFei
WHERE (1 = 1)
GROUP BY zhanghao, mingzi, bumenmingcheng, kaleibiemingcheng,
zhanghaozhuangtai, kayajin, zhanghaoyue

UNION ALL
SELECT TOP 100000 1 AS ID, zhanghao, mingzi, bumenmingcheng,
kaleibiemingcheng, zhanghaozhuangtai, kayajin, 0 chongzhijine, 0 jicixiaofei,
SUM(cast(xiaofeijine AS decimal(15, 2))) AS xiaofeijine, 0 tuikuanjine,
0 tuikajine, zhanghaoyue
FROM VZhengChangXiaoFei
WHERE (1 = 1)
GROUP BY zhanghao, mingzi, bumenmingcheng, kaleibiemingcheng,
zhanghaozhuangtai, kayajin, zhanghaoyue

UNION ALL
SELECT TOP 100000 1 AS ID, zhanghao, mingzi, bumenmingcheng,
kaleibiemingcheng, zhanghaozhuangtai, kayajin, 0 chongzhijine, 0 jicixiaofei,
0 xiaofeijine, SUM(Vtuikuancaozuobiao.tuikuanjine) AS tuikuanjine, 0 tuikajine,
zhanghaoyue
FROM Vtuikuancaozuobiao
WHERE (1 = 1)
GROUP BY zhanghao, mingzi, bumenmingcheng, kaleibiemingcheng,
zhanghaozhuangtai, kayajin, zhanghaoyue

UNION ALL
SELECT TOP 100000 1 AS ID, zhanghao, mingzi, bumenmingcheng,
kaleibiemingcheng, zhanghaozhuangtai, kayajin, 0 chongzhijine, 0 jicixiaofei,
0 xiaofeijine, 0 tuikuanjine, SUM(Vtuikacaozuobiao.hejijine) AS tuikajine,
zhanghaoyue
FROM Vtuikacaozuobiao
WHERE (1 = 1)
GROUP BY zhanghao, mingzi, bumenmingcheng, kaleibiemingcheng,
zhanghaozhuangtai, kayajin, zhanghaoyue )
a
GROUP BY zhanghao, mingzi, bumenmingcheng,kaleibiemingcheng,zhanghaozhuangtai,
kayajin, zhanghaoyue

[weisai]

sum(chongzhijine),sum(jicixiaofei),sum(xiaofeijine),sum(tuikuanjine),sum(tuikajine)

sum(chongzhijine) as 11 ,sum(jicixiaofei) as 22 后面的也一样要加一个名称

[wcbenben]

这个项目用的就是微软自带的报表工具！
没用水晶报表！
现在这个问题就出在了！用sum聚合出来的这几列上！！
报表里不显示！

[wcbenben]

加过别名了！！
还是一样的

[weisai]

是Reporting Service 吗？
可以直接拖拽到啊

[fellowcheng]

试试　与雪共舞　的方法

或者
用代码直接构造数据

    /// <summary>
    /// 生成报表图形
    /// </summary>
    /// <param name="DSource">数据源</param>
    private void CreateReport(DataTable DSource,DataTable LastYearSource)
    {
        ReportViewer1.LocalReport.ReportPath = "Weith.Water/Report/MonthReport.rdlc";
        ReportViewer1.LocalReport.DataSources.Clear();
        //生成固定结构的表以方便Report调用
        DataTable TempTable = new DataTable();
        DataColumn d1 = new DataColumn("TextFiled", System.Type.GetType("System.Double"));
        DataColumn d2 = new DataColumn("NouseFiled", System.Type.GetType("System.String"));
        DataColumn d3 = new DataColumn("ButtomFiled", System.Type.GetType("System.String"));
        DataColumn d4 = new DataColumn("RightFiled", System.Type.GetType("System.String"));
        TempTable.Columns.Add(d1);
        TempTable.Columns.Add(d2);
        TempTable.Columns.Add(d3);
        TempTable.Columns.Add(d4);
        for (int i = 0; i < DSource.Rows.Count;i++ )
        {
            TempTable.Rows.Add(DSource.Rows[i][2],"",DateTime.Parse(DSource.Rows[i][1].ToString()).Day.ToString(),"本月");
        }
        if (LastYearSource.Rows.Count > 0)
        {
            for (int j = 0; j < LastYearSource.Rows.Count; j++)
            {
                TempTable.Rows.Add(LastYearSource.Rows[j][2], "", DateTime.Parse(LastYearSource.Rows[j][1].ToString()).Day.ToString(), "去年");
            }
        }
        ReportViewer1.LocalReport.DataSources.Add(new Microsoft.Reporting.WebForms.ReportDataSource("ReservoirParameter_ReservoirParameterSelectAreadt", TempTable));
        ReportViewer1.LocalReport.Refresh();
    }

[wcbenben]

[Quote=引用 6 楼 weisai 的回复:]
sum(chongzhijine),sum(jicixiaofei),sum(xiaofeijine),sum(tuikuanjine),sum(tuikajine)

sum(chongzhijine) as 11 ,sum(jicixiaofei) as 22 后面的也一样要加一个名称
[/Quote]

大哥 请受小弟一拜啊！
终于解决了！

[wcbenben]

最主要的是要和那个新建的视图一样！