SQLSERVER2000 用过程来模拟MDX分析报表的效果(1)

这个想法的产生比较特殊,当时我在某学会工作,我对数据仓库的学习也初见成效,也做了一两个基于数据仓库的产品,总觉得数据仓库部署比较麻烦,而我的客户仅仅是需要一张分析型的报表.他只要满足多维的旋转,聚合,产生一个层次性的报表就可以满足要求.
     基于这种情况,我想:能不能在数据库下模拟一张这样的报表来满足用户的需要?
     因为写过仓库下的二个基于WEB的展示组件,显示一个MDX返回的CellSet,无非就是分解各轴中的Members的Caption,再取各单元格的属性作为显示格式等.思路并不复杂,不过,因为要设置合并,过程还是比较复杂的.
     那如果,我在数据库中用存储过程返回一个类CellSet的DataTable,那么,不也就可以实现了模拟的效果了吗?
     好了,闲话不说了.看正题吧.

第一部分:相关的存储过程
这个过程的目的就是生成一个类MDX的CellSet的结果.
要解释这个过程,先说CellSet的结构.我觉得因为你在了解了CellSet的结构后,理解这个过程会更简单,而且,我这个过程的思路本身就是从CellSet中来.

1.Cellset 对象
表示多维查询的结果。它是从立方或其它单元集中选出的单元的集合

Connection                   '//AdomdClient.AdomdConnection连接对象
 |
 |-CellSet                   '//查询结果集.
   |
   |-Cell                    '//结果集中的单元
   |-Axes                    '//结果集的轴集合,
   |-ProPerties              '//提供者的 Cellset 信息,可用 Property 检索.

说明

使用直接、类似数组的访问来实现对 Cellset 中数据的检索。可以“深入”到特定的成员以获得该成员的数据。例如,下列代码将返回名为 cst 的单元集第一坐标轴第一位置中第一个成员的标题:
cst.Axes(0).Positions(0).Members(0).Caption

在单元集中没有当前单元的概念,而是使用 Item 方法从单元集中检索指定的 Cell 对象。Item 方法的参数确定检索哪个单元。可以指定单元的唯一序号值。也可通过使用单元集

每个轴上的位置号来检索单元。

使用 Cellset 对象的集合、方法和属性,可以:
通过设置它的 ActiveConnection 属性,使打开的连接与 Cellset 对象相关联。
使用 Open 方法执行并检索多维查询的结果。
使用 Item 方法从 Cellset 中检索 Cell。
使用 Axes 集合返回定义 Cellset 的 Axis 对象。
使用 FilterAxis 属性检索有关用于过滤 Cellset 中数据的维的信息。
使用 Source 属性返回或指定用于定义 Cellset 的查询。
使用 State 属性返回 Cellset 的当前状态(打开、关闭、正在执行或正在连接)。
使用 Close 方法关闭打开的 Cellset。
使用标准的 ADO Properties 集合检索特定提供者的 Cellset 信息。

下面的这个函数就是将一个CellSet转为一个DataTable,从源程序来,有删节.

  1.     ''' <summary>
  2.     ''' 将CellSet转为DataTable.
  3.     ''' </summary>
  4.     ''' <param name="CellSet">CellSet对象.</param>
  5.     ''' <returns>DataTable</returns>
  6.     ''' <remarks></remarks>
  7.     Private Function AdoMdCellSetToDataTable(ByVal CellSet As Microsoft.AnalysisServices.AdomdClient.CellSet) As DataTable
  8.         Dim Dt As New DataTable
  9.         Dim Dc As New DataColumn
  10.         Dim Dr As DataRow = Nothing
  11.         Dim I As Int32
  12.         Dim ColName As String = ""
  13.         Dim sC As Int32
  14.         Dim sR As Int32 = 0
  15.         Dim RowID As Int32 = 0
  16.         Dim SDr As DataRow = Nothing
  17.         sC = 1
  18.         For Each M As Microsoft.AnalysisServices.AdomdClient.Member In CellSet.Axes(1).Positions(0).Members
  19.             sC += 1
  20.             Dc = New DataColumn()
  21.             Dc.ColumnName = Microsoft.VisualBasic.Space(sC) '//以空填充空白部分.
  22.             Dt.Columns.Add(Dc)
  23.         Next
  24.         '//生成数据列对象
  25.         sR = 0
  26.         Dim R, C As Int32
  27.         Dim ColID As Int16 = 0
  28.         Dim AxexCount_0 As Int32 = CellSet.Axes(0).Positions.Count
  29.         Dim AxexCount_1 As Int32 = CellSet.Axes(1).Positions.Count
  30.         Dim CName As String
  31.         R = 0 : C = 0
  32.         For Each P As Microsoft.AnalysisServices.AdomdClient.Position In CellSet.Axes(0).Positions '//分解0轴,Mdx中的 ON Columns 轴.
  33.             CName = ""
  34.             R = 0 : ColID += 1
  35.             For Each M As Microsoft.AnalysisServices.AdomdClient.Member In P.Members
  36.                 If R = 0 Then
  37.                     CName = CName & "[" & Trim$(M.Caption) & "]"
  38.                 Else
  39.                     CName = CName & ".[" & Trim$(M.Caption) & "]"
  40.                 End If
  41.                 R += 1
  42.             Next
  43.             Dc = New DataColumn()
  44.             Dc.ColumnName = CName
  45.             Dt.Columns.Add(Dc)
  46.             C += 1
  47.         Next
  48.         '//添加行数据
  49.         Dim CellVal As String
  50.         Dim Pos As Int32 = 0
  51.         Dim Ts As String
  52.         Dim Cid As Int32
  53.         For Each py As Microsoft.AnalysisServices.AdomdClient.Position In CellSet.Axes(1).Positions  '//分解1轴,Mdx中的 ON Rows 轴
  54.             Dr = Dt.NewRow()
  55.             RowID += 1
  56.             I = 0
  57.             '//行方向的名称列.
  58.             For Each m As Microsoft.AnalysisServices.AdomdClient.Member In py.Members
  59.                 Ts = m.Caption
  60.                 Cid = I
  61.                 Dr(Cid) = Ts
  62.                 I += 1
  63.             Next
  64.             '//行数据
  65.             For X As Int32 = 1 To AxexCount_0
  66.                 CellVal = "" & CellSet(Pos).FormattedValue
  67.                 If CellVal = "1.#INF" OrElse CellVal = "-1.#IND" OrElse CellVal = "1#I.NF0" OrElse CellVal = "1#I.NF0%" Then CellVal = "" 
  68.                 Dr(Cid+X) = CellVal
  69.                 Pos += 1
  70.             Next
  71.             Dt.Rows.Add(Dr)
  72.         Next
  73.         Return Dt
  74.     End Function

效果:

 

观察上图,发现了什么,原来DataTable与CellSet是很象的.实质上,他们之间是可以通过前台的应用程序来转化,你如果将上图的DaTable 按 "].[" 来分解,按层合并相同项,那么,DataTable也可以变成一个CellSet,有了这个思路,那么,就可以写一个存储过程,将数据库中的数据按你的要求组织成要求的格式.将列的名称用自定义的分隔符分隔,就可以达到相同的目的了.
下面的SQL过程就实现了这个目的.自定义的分隔符是 "$@".

效果:

/***********************************************************
--调用例子
EXECUTE Prc_CorssReport_V2 N'test_data',
    N'海关名称,洲名称,地区名称',
    N'商品名称,年,月',
    N'金额',' WHERE 海关编号 between ''01'' and ''02''   and 时间编号 between 20070401 and 20070401  and 类编号=''10000002'''
***********************************************************/

CREATE PROC Prc_CorssReport_V2 (
------------------------------------------------------
--- 模拟 MDX V2.0 版.
---          MSTOP 2008/3/21
------------------------------------------------------
    @nvr_TableName nvarchar(128),
    @nvr_OnRows  nvarchar(1024),  --行列表
    @nvr_OnColumns nvarchar(1024),  --列列表
    @nvr_OnValue nvarchar(64),  --值字段
    @nvr_Where  nvarchar(1024)='' --条件
) AS BEGIN

    DECLARE @NVR_CMD NVARCHAR(1024)
    DECLARE @NVR_Columns NVARCHAR(1024)
    DECLARE @NVR_TmpTableName NVARCHAR(128)
    DECLARE @NVR_NewColName varchar(16)
    DECLARE @nvr_sOnRows NVARCHAR(1024)
    DECLARE @nvr_OnRows_B NVARCHAR(1024)
    DECLARE @nvr_OnColumns_B nvarchar(1024)
    DECLARE @NVR_SQLCOM_00 NVARCHAR(4000)

    SET @nvr_OnRows_B=N'[' + REPLACE(@nvr_OnRows,N',',N'],[') + N']'
    SET @nvr_OnColumns_B=N'[' + REPLACE(@nvr_OnColumns,N',',N'],[') + N']'

    SET @nvr_sOnRows=@nvr_OnRows
    SET @NVR_NewColName=N'Val_' + LEFT(REPLACE(NEWID(),'-',''),6)
    SET @NVR_TmpTableName=N'@TAB_TABLE'
    --提取数据到表变量中.
    SET @NVR_SQLCOM_00=N'DECLARE ' + @NVR_TmpTableName + N' TABLE ([' + REPLACE(@nvr_OnRows,N',',N'] VARCHAR(128),[') + N'] VARCHAR(128),[' + @NVR_NewColName

+ N'] VARCHAR(128),[' + @nvr_OnValue + N'] FLOAT PRIMARY KEY  (' + @nvr_OnRows_B + N',[' + @NVR_NewColName + N']))'
    --合并列的名称.分隔符为 $@ ,也可以指定其它的分隔符.
    SET @NVR_Columns=N'RTRIM([' + REPLACE(@nvr_OnColumns,N',',N']) + ''$@''+RTRIM([') + N'])+''$@'''
    SET @NVR_SQLCOM_00=@NVR_SQLCOM_00 + CHAR(13) + N'INSERT INTO ' + @NVR_TmpTableName + N' SELECT ' +@nvr_OnRows_B + N',(' + @NVR_Columns + N') AS [' +

@NVR_NewColName + N'],SUM([' + @nvr_OnValue + N']) AS '
            + @nvr_OnValue + N' FROM ' + @nvr_TableName
            + @nvr_Where
            + N' GROUP BY ' + @nvr_OnColumns_B   + N',' +  @nvr_OnRows_B
            + N' ORDER BY ' + @nvr_OnRows_B  + CHAR(13)

    --因为动态生成的SQL语句很长,所以定义多个SQL变量.
    DECLARE @NVR_XCOLNAME AS NVARCHAR(128)
    DECLARE @NVR_SQL0 AS NVARCHAR(4000)
    DECLARE @NVR_SQL1 AS NVARCHAR(4000)
    DECLARE @NVR_SQL2 AS NVARCHAR(4000)
    DECLARE @NVR_SQL3 AS NVARCHAR(4000)
    DECLARE @NVR_SQL4 AS NVARCHAR(4000)
    DECLARE @NVR_SQL5 AS NVARCHAR(4000)
    DECLARE @NVR_SQL6 AS NVARCHAR(4000)
    DECLARE @NVR_SQL7 AS NVARCHAR(4000)
    DECLARE @NVR_SQL8 AS NVARCHAR(4000)
    DECLARE @NVR_SQL9 AS NVARCHAR(4000)
    DECLARE @NVR_SQL10 AS NVARCHAR(4000)
    DECLARE @NVR_SQL11 AS NVARCHAR(4000)
    DECLARE @NVR_SQL12 AS NVARCHAR(4000)
    DECLARE @NVR_SQL13 AS NVARCHAR(4000)
    DECLARE @NVR_SQL14 AS NVARCHAR(4000)
    DECLARE @NVR_SQL15 AS NVARCHAR(4000)
    DECLARE @NVR_SQL16 AS NVARCHAR(4000)
    DECLARE @NVR_SQL17 AS NVARCHAR(4000)
    DECLARE @NVR_SQL18 AS NVARCHAR(4000)
    DECLARE @NVR_SQL19 AS NVARCHAR(4000)
    DECLARE @NVR_SQL20 AS NVARCHAR(4000)
    DECLARE @INT_ID AS INT

    SET @NVR_XCOLNAME=''
    SELECT

@NVR_SQL0='',@INT_ID=0,@NVR_SQL0='',@NVR_SQL1='',@NVR_SQL2='',@NVR_SQL3='',@NVR_SQL4='',@NVR_SQL5='',@NVR_SQL6='',@NVR_SQL7='',@NVR_SQL8='',@NVR_SQL9='',
       

@NVR_SQL10='',@NVR_SQL11='',@NVR_SQL12='',@NVR_SQL13='',@NVR_SQL14='',@NVR_SQL15='',@NVR_SQL16='',@NVR_SQL17='',@NVR_SQL18='',@NVR_SQL19='',@NVR_SQL20=''
     
    DECLARE @INT_I INT
    DECLARE @NVR_FLDNAME NVARCHAR(4000)
   
    SET @NVR_SQL0 = N' SELECT ' +  @nvr_OnRows_B
    SET @NVR_CMD=N'DECLARE CORSS_CURSOR CURSOR FOR SELECT DISTINCT [' + @NVR_NewColName + N'] FROM (SELECT ' + @NVR_Columns + N' AS [' + @NVR_NewColName +

N'] FROM ' + @nvr_TableName + N' ' + @NVR_WHERE  + N' ) AS TIISHFURL ORDER BY [' + @NVR_NewColName + N'] FOR READ ONLY ' --生成游标
    EXECUTE (@NVR_CMD)
    OPEN CORSS_CURSOR
   
    WHILE (1=1)  BEGIN
        FETCH NEXT FROM CORSS_CURSOR INTO @NVR_XCOLNAME  --遍历游标,将列头信息放入变量@NVR_XCOLNAME
        IF (@@FETCH_STATUS <>0) BREAK
       
        SET @INT_ID=@INT_ID+1
        SET @NVR_XCOLNAME=REPLACE(@NVR_XCOLNAME ,CHAR(39),CHAR(39)+CHAR(39))
       
        ------------------------------------------------------------------------------
        IF @INT_ID <=20
            SET @NVR_SQL1 = @NVR_SQL1 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF @INT_ID <=40
            SET @NVR_SQL2 = @NVR_SQL2 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=60
            SET @NVR_SQL3 = @NVR_SQL3 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=80
            SET @NVR_SQL4 = @NVR_SQL4 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=100
            SET @NVR_SQL5 = @NVR_SQL5 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=120
            SET @NVR_SQL6 = @NVR_SQL6 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=140
            SET @NVR_SQL7 = @NVR_SQL7 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=160
            SET @NVR_SQL8 = @NVR_SQL8 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=180
            SET @NVR_SQL9 = @NVR_SQL9 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END) AS

[' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=200
            SET @NVR_SQL10 = @NVR_SQL10 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=220
            SET @NVR_SQL11 = @NVR_SQL11 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=240
            SET @NVR_SQL12 = @NVR_SQL12 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=260
            SET @NVR_SQL13 = @NVR_SQL13 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=280
            SET @NVR_SQL14 = @NVR_SQL14 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=300
            SET @NVR_SQL15 = @NVR_SQL15 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=320
            SET @NVR_SQL16 = @NVR_SQL16 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=340
            SET @NVR_SQL17 = @NVR_SQL17 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=360
            SET @NVR_SQL18 = @NVR_SQL18 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=380
            SET @NVR_SQL19 = @NVR_SQL19 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
        ELSE IF  @INT_ID <=400
            SET @NVR_SQL20 = @NVR_SQL20 + N',SUM(CASE [' + @NVR_NewColName + N'] WHEN ''' + @NVR_XCOLNAME + N''' THEN [' + @nvr_OnValue + N'] ELSE NULL END)

AS [' + @NVR_XCOLNAME + N']' --构造查询
    END -- WHILE (1 = 1)
    Close CORSS_CURSOR
    DEALLOCATE CORSS_CURSOR

    EXECUTE ( @NVR_SQLCOM_00 + @NVR_SQL0 + @NVR_SQL1 + @NVR_SQL2  + @NVR_SQL3 + @NVR_SQL4 + @NVR_SQL5 + @NVR_SQL6 + @NVR_SQL7 + @NVR_SQL8 + @NVR_SQL9
     + @NVR_SQL10 + @NVR_SQL11 + @NVR_SQL12  + @NVR_SQL13 + @NVR_SQL14 + @NVR_SQL15 + @NVR_SQL16 + @NVR_SQL17 + @NVR_SQL18 + @NVR_SQL19 + @NVR_SQL20
     + N' FROM ' + @NVR_TmpTableName + N' GROUP BY ' + @nvr_OnRows ) -- 执行
    RETURN 0 --释放游标,返回0表示成功

End

GO

 

来一张最终效果,否则你们没兴趣看了. 哈哈...

 

SQLSERVER2000 用过程来模拟MDX分析报表的效果(2)

 

[未完待续]

 

 

发布了25 篇原创文章 · 获赞 4 · 访问量 6万+
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览