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));
}
