public void ParseAll() { #region Arrange var singDic = new Dictionary <string, string>(); singDic.Add("[Usage].[Inbound or Outbound].[Inbound or Outbound]", "[Usage].[Inbound or Outbound].&[Inbound]"); singDic.Add("[Usage].[Call Type].[Call Type]", "[Usage].[Call Type].&[MOC]"); singDic.Add("[UsageDate].[Usage_Year].[Usage_Year]", "[UsageDate].[Usage_Year].&[2014]"); singDic.Add("[UsageDate].[Usage_MonthAbbrev].[Usage_MonthAbbrev]", "[UsageDate].[Usage_MonthAbbrev].&[May]"); #endregion #region Parse string commandText; var pivotCellDic = new PivotCellDictionary(); pivotCellDic.SingleSelectDictionary = singDic; using (var tabular = new DaxDrill.Tabular.TabularHelper(serverName, "Roaming")) { tabular.Connect(); commandText = DaxDrillParser.BuildQueryText(tabular, pivotCellDic, "Gross Billed Sum", 99999); tabular.Disconnect(); } #endregion #region Assert Console.WriteLine(commandText); #endregion }
public void ParseCellDictionary() { #region Arrange var excelDic = new Dictionary <string, string>(); excelDic.Add("[Usage].[Inbound or Outbound].[Inbound or Outbound]", "[Usage].[Inbound or Outbound].&[Inbound]"); excelDic.Add("[Usage].[Call Type].[Call Type]", "[Usage].[Call Type].&[MOC]"); excelDic.Add("[UsageDate].[Usage_Year].[Usage_Year]", "[UsageDate].[Usage_Year].&[2014]"); excelDic.Add("[UsageDate].[Usage_MonthAbbrev].[Usage_MonthAbbrev]", "[UsageDate].[Usage_MonthAbbrev].&[May]"); #endregion #region Parse var pivotCellDic = new PivotCellDictionary(); pivotCellDic.SingleSelectDictionary = excelDic; string commandText; using (var tabular = new DaxDrill.Tabular.TabularHelper(serverName, "Roaming")) { tabular.Connect(); commandText = DaxDrillParser.BuildFilterCommandText(pivotCellDic, tabular, null); tabular.Disconnect(); } #endregion #region Assert Console.WriteLine(commandText); #endregion }
/// <summary> /// Builds DAX query based on location on pivot table (specified in parameters). This returns all columns in the table. /// </summary> /// <param name="tabular">Tabular connection helper</param> /// <param name="pivotCellDic">Dictionary representing Pivot Table context filters</param> /// <param name="measureName">Name of DAX measure to be used in drillthrough</param> /// <param name="maxRecords">Maximum records to be retrieved</param> /// <param name="detailColumns">List of columns to be included in drill-through</param> /// <returns></returns> public static string BuildQueryText(DaxDrill.Tabular.TabularHelper tabular, PivotCellDictionary pivotCellDic, string measureName, int maxRecords, IEnumerable <DetailColumn> detailColumns, IEnumerable <string> pivotFieldNames) { var measure = tabular.GetMeasure(measureName); string commandText = BuildCustomQueryText(tabular, pivotCellDic, measure.TableName, maxRecords, detailColumns, pivotFieldNames); return(commandText); }
public QueryClient(Excel.Range rngCell, PivotCellDictionary pivotCellDic, IEnumerable <string> pivotFieldNames) { this.rngCell = rngCell; this.pivotTable = rngCell.PivotTable; this.pcache = pivotTable.PivotCache(); this.connectionString = pcache.Connection; this.pivotFieldNames = pivotFieldNames ?? PivotCellHelper.GetPivotFieldNames(rngCell); this.pivotCellDic = pivotCellDic ?? PivotCellHelper.GetPivotCellQuery(rngCell); }
public void AddMultiplePageFieldFilterToDic() { string pivotFieldNamesString = @"[Measures].[Trades CCA Sum] [TranDate].[Tran_Year].[Tran_Year] [TranDate].[Tran_YearMonthDay].[Tran_Year] [TranDate].[Tran_YearMonthDay].[Tran_MonthAbbrev] [TranDate].[Tran_YearMonthDay].[Tran_DayOfMonth] [CounterCcy].[Counter Ccy].[Counter Ccy] [TranDate].[Tran_MonthAbbrev].[Tran_MonthAbbrev]"; string ptMdxString = @"SELECT NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[TranDate].[Tran_Year].[All],[TranDate].[Tran_Year].[Tran_Year].AllMembers}, {([TranDate].[Tran_MonthAbbrev].[All])}), [TranDate].[Tran_Year].[Tran_Year].AllMembers, [TranDate].[Tran_MonthAbbrev])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize({DrilldownLevel({[CounterCcy].[Counter Ccy].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.016E3].&[Jun],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.016E3].&[Mar]}) ON COLUMNS FROM [Model]) WHERE ([Measures].[Trades CCA Sum]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS"; string[] pivotFieldNames = pivotFieldNamesString.Split(new string[] { "\r\n" }, StringSplitOptions.None); var pivotCellDic = new PivotCellDictionary(); PivotCellHelper.AddMultiplePageFieldFilterToDic(pivotFieldNames, ptMdxString, pivotCellDic); }
public static string BuildFilterCommandText(PivotCellDictionary excelDic, DaxDrill.Tabular.TabularHelper tabular, IEnumerable <string> pivotFieldNames) { string singCmdText = BuildSingleSelectFilterCommandText(excelDic.SingleSelectDictionary, tabular, pivotFieldNames); string multiCmdText = BuildMultiSelectFilterCommandText(excelDic.MultiSelectDictionary, tabular); string result = singCmdText; if (!string.IsNullOrEmpty(multiCmdText)) { if (!string.IsNullOrEmpty(singCmdText)) { result += ",\n"; } result += multiCmdText; } return(result); }
public void ParseSelectedColumns() { #region Arrange var singDic = new Dictionary <string, string>(); singDic.Add("[Usage].[Inbound or Outbound].[Inbound or Outbound]", "[Usage].[Inbound or Outbound].&[Inbound]"); singDic.Add("[Usage].[Call Type].[Call Type]", "[Usage].[Call Type].&[MOC]"); singDic.Add("[UsageDate].[Usage_Year].[Usage_Year]", "[UsageDate].[Usage_Year].&[2014]"); singDic.Add("[UsageDate].[Usage_MonthAbbrev].[Usage_MonthAbbrev]", "[UsageDate].[Usage_MonthAbbrev].&[May]"); #endregion #region Parse var pivotCellDic = new PivotCellDictionary(); pivotCellDic.SingleSelectDictionary = singDic; var parser = new DaxDrillParser(); string commandText; using (var tabular = new DaxDrill.Tabular.TabularHelper(serverName, "Roaming")) { tabular.Connect(); var selectedColumns = new List <DetailColumn>(); selectedColumns.Add(new DetailColumn() { Name = "Call Type", Expression = "Usage[Call Type]" }); selectedColumns.Add(new DetailColumn() { Name = "Call Type Description", Expression = "Usage[Call Type Description]" }); commandText = DaxDrillParser.BuildQueryText(tabular, pivotCellDic, "Gross Billed Sum", 99999, selectedColumns, null); tabular.Disconnect(); } #endregion #region Assert Console.WriteLine(commandText); #endregion }
// This returns the query results based on the specified tableQuery. public static string BuildCustomQueryText(DaxDrill.Tabular.TabularHelper tabular, PivotCellDictionary pivotCellDic, string tableQuery, int maxRecords, IEnumerable <DetailColumn> detailColumns, IEnumerable <string> pivotFieldNames) { string filterText = BuildFilterCommandText(pivotCellDic, tabular, pivotFieldNames); // create inner clause string commandText = string.Format("TOPN ( {1}, {0} )", tableQuery, maxRecords); // nest into SELECTCOLUMNS function if (detailColumns != null && detailColumns.Count() > 0) { commandText = string.Format("SELECTCOLUMNS ( {0}, {{0}} )", commandText); string selectColumnsText = BuildSelectText(detailColumns); commandText = string.Format(commandText, selectColumnsText); } // add filter arguments if (!string.IsNullOrWhiteSpace(filterText)) { commandText += string.Format(",\n{0}", filterText); } // nest into CALCULATETABLE function commandText = string.Format("EVALUATE CALCULATETABLE ( {0} )", commandText); return(commandText); }
public static string BuildQueryText(DaxDrill.Tabular.TabularHelper tabular, PivotCellDictionary pivotCellDic, string measureName, int maxRecords) { return(BuildQueryText(tabular, pivotCellDic, measureName, maxRecords)); }