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数据源模型通过拖拽维表和度量值来完成透视表
以下:
=======================================================================================