SSAS多维设计模型与报表客户端-Excel透视表制作

VS:SSAS设计多维设计模型：

数据源视图.dsv：

数据源视图表，名称命名规范:vw_表名（事实表_F_/维表_D_），

VS 多维数据集CUBE:

MSSQLServer AS:

MDX查询语句：

WITH MEMBER [Target] as ([Measures].[REV TARGET Month1]+[Measures].[REV TARGET Month2]+[Measures].[REV TARGET Month3])
                            MEMBER [MTD1] as  [Measures].[SO AMT Month1]
                            MEMBER [MTD2] as  [Measures].[SO AMT Month2]
                            MEMBER [MTD3] as  [Measures].[SO AMT Month3]
                            MEMBER [BUQTD] as ([Measures].[SO AMT Month1]+ [Measures].[SO AMT Month2]+ [Measures].[SO AMT Month3])
                            MEMBER [HitRate%] as IIF([Target]=NULL OR [BUQTD]=NULL OR [Target]=0,NULL,[BUQTD]/[Target])
                            SELECT CROSSJOIN({[AccountMgr-BU].[BU CODE],
                            {[AccountMgr-BU].[BU CODE].&[NB],
                            [AccountMgr-BU].[BU CODE].&[CN],[AccountMgr-BU].[BU CODE].&[CM],
                            [AccountMgr-BU].[BU CODE].&[MO],[AccountMgr-BU].[BU CODE].&[PJ],
                            [AccountMgr-BU].[BU CODE].&[ST],[AccountMgr-BU].[BU CODE].&[TP]}},
                            {[Target],[MTD1],[MTD2],[MTD3],[BUQTD],[HitRate%]}) ON COLUMNS, NON EMPTY 
                            CROSSJOIN(
                            exists([AccountMgr-Channel].[SELLOUT TYPE NAME].CHILDREN,[AccountMgr-Channel].[SELLOUT TYPE NAME].&[New Channel]),
                            [AccountMgr-Employee].[EMPLOYEE NAME].CHILDREN ,[AccountMgr-DealerGroup].[DEALER GROUP].CHILDREN
                            )ON ROWS   
                            FROM [AccountMgr]

 

MDX语句查询结果截图：

VS2010报表生成程序-控制台程序：

获取SSAS DataTable:

        public DataTable GetAccountPerformanceReport_SellOut(string strChannel)
        {
            #region
            //...
            string strSql = @"WITH MEMBER [Target] as ([Measures].[REV TARGET Month1]+[Measures].[REV TARGET Month2]+[Measures].[REV TARGET Month3])
                            MEMBER [MTD1] as  [Measures].[SO AMT Month1]
                            MEMBER [MTD2] as  [Measures].[SO AMT Month2]
                            MEMBER [MTD3] as  [Measures].[SO AMT Month3]
                            MEMBER [BUQTD] as ([Measures].[SO AMT Month1]+ [Measures].[SO AMT Month2]+ [Measures].[SO AMT Month3])
                            MEMBER [HitRate%] as IIF([Target]=NULL OR [BUQTD]=NULL OR [Target]=0,NULL,[BUQTD]/[Target])
                            SELECT CROSSJOIN({[AccountMgr-BU].[BU CODE],{[AccountMgr-BU].[BU CODE].&[NB],
                            [AccountMgr-BU].[BU CODE].&[CN],[AccountMgr-BU].[BU CODE].&[CM],
                            [AccountMgr-BU].[BU CODE].&[MO],[AccountMgr-BU].[BU CODE].&[PJ],
                            [AccountMgr-BU].[BU CODE].&[ST],[AccountMgr-BU].[BU CODE].&[TP]}},
                            {[Target],[MTD1],[MTD2],[MTD3],[BUQTD],[HitRate%]}) ON COLUMNS, NON EMPTY 
                            CROSSJOIN(" + strSellType + @",[AccountMgr-Employee].[EMPLOYEE NAME].CHILDREN ," + strDealerGroup + @")ON ROWS   
                            FROM [AccountMgr]";

            DataTable ssasResult = base.GetSsasResult(strSql);
            ssasResult.Rows[1][0] = " Channel ";
            //base.CalcSubtotal_Common(//如果需要可以添加小计总计计算列...)
            #endregion
            return ssasResult;
        }

 

 设置SSAS连接，GetSsasResult方法--类BaseDBAccess：

    public class BaseDBAccess
    {
        private AdomdConnection conn;
        private string connStr = string.Empty;
        connStr = "Provider=MSOLAP; DataSource=http://IP/olap/msmdpump.dll; Initial Catalog=" + //数据库名称如：VPReport;

        protected DataTable GetSsasResult(string strSql)
        {
            conn = new AdomdConnection(connStr);
            conn.Open();
            CreateConn();
            AdomdCommand command = conn.CreateCommand();
            command.CommandText = strSql;
            CellSet cs = command.ExecuteCellSet();
            conn.Close();
            return CellSet2DataTable(cs);
        }
      }

 

运行报表程序生成的EXCEL报表效果截图：

调用方法，添加小计行之后：

这样到此自动报表程序就结束了，

其实在excel也可以直接连接外部SSAS数据源模型通过拖拽维表和度量值来完成透视表

以下：

=======================================================================================