推荐订阅源

存储过程：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:        <Author,,Name>
-- Create date: 
-- Description:    评吸质量雷达图分析
-- 调用示例：EXEC [dbo].[usp_Tqc_Raw_SmokingTest] '2013-08-26','2013-08-27'
-- =============================================
ALTER PROCEDURE [dbo].[usp_Tqc_Raw_SmokingTest]
    @StartDate VARCHAR(20) , --取样起时间
    @EndDate VARCHAR(20)  --取样止时间
AS 
    BEGIN
        SET NOCOUNT ON ;
        -- 劲头加减计算
        SELECT TOP 3
                SampleNo ,
                Aroma1 ,
                Aroma2 ,
                Aroma3 ,
                Chroma ,
                ( CASE WHEN VigourAS = '+' THEN ( 20 - Vigour ) / 2
                       WHEN VigourAS = '-' THEN Vigour / 2
                  END ) AS Vigour ,
                AromaQuality / 2.5 AS AromaQuality ,
                AromaContent / 2.5 AS AromaContent ,
                Miscellaneous / 1.5 AS Miscellaneous ,
                Stimulate / 1.5 AS Stimulate ,
                Aftertaste / 2 AS Aftertaste
        INTO    #TMP_Tqc_Raw_SmokingTest
        FROM    Tqc_Raw_SmokingTest
        WHERE   CheckTime BETWEEN @StartDate AND @EndDate
    
        DECLARE @t TABLE
            (
              [样品号] VARCHAR(50) ,
              [清香] NUMERIC(18, 2) ,
              [甜香] NUMERIC(18, 2) ,
              [焦香] NUMERIC(18, 2) ,
              [浓度] NUMERIC(18, 2) ,
              [劲头] NUMERIC(18, 2) ,
              [香气质] NUMERIC(18, 2) ,
              [香气量] NUMERIC(18, 2) ,
              [杂气] NUMERIC(18, 2) ,
              [刺激] NUMERIC(18, 2) ,
              [余味] NUMERIC(18, 2)
            )
        -- 计算平均值
        INSERT  INTO @t
                SELECT  SampleNo ,
                        AVG(Aroma1) ,
                        AVG(Aroma2) ,
                        AVG(Aroma3) ,
                        AVG(Chroma) ,
                        AVG(Vigour) ,
                        AVG(AromaQuality) ,
                        AVG(AromaContent) ,
                        AVG(Miscellaneous) ,
                        AVG(Stimulate) ,
                        AVG(Aftertaste)
                FROM    #TMP_Tqc_Raw_SmokingTest
                GROUP BY SampleNo
        
             
        -- 列转行处理
        SELECT  [样品号] ,
                [检测项] = attribute ,
                [检测值] = value
        FROM    @t UNPIVOT
  ( value FOR attribute IN ( [清香], [甜香], [焦香], [浓度], [劲头], [香气质], [香气量], [杂气],
                             [刺激], [余味] ) ) AS UPV
                
        DROP TABLE #TMP_Tqc_Raw_SmokingTest

    END


GO

前端代码：

/// <summary>
        /// 评吸质量雷达图分析
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void button16_Click(object sender, EventArgs e)
        {
            XtraSmokingTestRadar report = new XtraSmokingTestRadar("2013-08-26","2013-08-27");
            report.ShowPreview();
        }

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using DevExpress.XtraReports.UI;
using SQLServerDAL;
using System.Data;

namespace XtraReportsTest
{
    public partial class XtraSmokingTestRadar : DevExpress.XtraReports.UI.XtraReport
    {
        public XtraSmokingTestRadar(string startDate, string endDate)
        {
            InitializeComponent();
            string strSQL = @"EXECUTE usp_Tqc_Raw_SmokingTest " + "'" + startDate + "'" + "," + "'" + endDate + "'";
            SqlHelper.FillDataset(ControlSql.GetConStr(), CommandType.Text, strSQL, dsTestRadarGraph1, new string[] { "usp_Tqc_Raw_SmokingTest" });
            this.xrTableCell75.Text = startDate;
            this.xrTableCell77.Text = endDate;
        }

    }
}

效果图：