public void ConvertPivotTableMdxToDaxFilterList()
{
var mdxString = @"SELECT NON EMPTY Hierarchize({[TranDate].[Tran_Year].[Tran_Year].AllMembers}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS FROM (SELECT ({[TranDate].[Tran_Year].&[2.016E3], [TranDate].[Tran_Year].&[2.015E3]}) ON COLUMNS FROM (SELECT ({[CounterCcy].[Counter Ccy].&[EUR],[CounterCcy].[Counter Ccy].&[USD]}) ON COLUMNS FROM [Model])) WHERE ([Account].[Account Currency].[All],[Activity].[Activity L1].[All],[CounterCcy].[Currency L1].&[Major],[Activity].[Is Hedgeable].&[True],[CounterCcy].[Counter Ccy Is Func].&[False],[SnapshotReported].[Compare Name].&[Current],[Snapshot].[Snapshot].&[Current],[Measures].[Trades CCA Sum]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS";
var daxFilterList = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(mdxString, null);
#region User Friendly Result
foreach (var df in daxFilterList)
{
Console.WriteLine("Column = {0}; Value = {1};", df.ColumnName, df.Value);
}
#endregion
#region Assert
IEnumerable <DaxFilter> columnDfs = null;
IEnumerable <DaxFilter> valueDfs = null;
columnDfs = daxFilterList.Where(c => c.ColumnName == "Tran_Year");
Assert.AreEqual(2, columnDfs.Count());
valueDfs = columnDfs.Where(c => c.Value == "2.016E3");
Assert.AreEqual(1, valueDfs.Count());
valueDfs = columnDfs.Where(c => c.Value == "2.015E3");
Assert.AreEqual(1, valueDfs.Count());
columnDfs = daxFilterList.Where(c => c.ColumnName == "Counter Ccy");
Assert.AreEqual(2, columnDfs.Count());
#endregion
}
public static void AddMultiplePageFieldFilterToDic(IEnumerable <string> pivotFieldNames, string ptMdx,
PivotCellDictionary pivotCellDic)
{
var daxFilterList = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(ptMdx, pivotFieldNames);
DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilterList, pivotCellDic.MultiSelectDictionary);
}
public void ParseHierTableMdx()
{
string mdxString = @"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_Year].&[2.016E3]},{[TranDate].[Tran_MonthAbbrev].&[Jun], [TranDate].[Tran_MonthAbbrev].&[May], [TranDate].[Tran_MonthAbbrev].&[Apr], [TranDate].[Tran_MonthAbbrev].&[Mar], [TranDate].[Tran_MonthAbbrev].&[Feb], [TranDate].[Tran_MonthAbbrev].&[Jan]}) ON COLUMNS FROM (SELECT ({[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.014E3].&[Jul],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.014E3].&[Mar],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.014E3].&[May],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.014E3].&[Nov],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.014E3].&[Oct],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.014E3].&[Sep],[TranDate].[Tran_YearMonthDay].[Tran_Year].&[2.016E3]}) ON COLUMNS FROM [Model])) WHERE ([Measures].[Trades CCA Sum]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS";
var daxFilters = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(mdxString);
var daxDic = DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilters);
#region User Friendly Result
foreach (var pair in daxDic)
{
Console.WriteLine(pair.Key + " ---------");
foreach (var value in pair.Value.Select(x => x.MDX))
{
Console.WriteLine(value);
}
}
#endregion
}
public void ParseTableMdx1()
{
string mdxString = @"SELECT NON EMPTY Hierarchize({DrilldownLevel({[UsageDate].[Usage_MonthAbbrev].[All]},,,INCLUDE_CALC_MEMBERS)}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[Usage].[Inbound or Outbound].[All],[Usage].[Inbound or Outbound].[Inbound or Outbound].AllMembers}, {([Usage].[Call Type].[All])}), [Usage].[Inbound or Outbound].[Inbound or Outbound].AllMembers, [Usage].[Call Type])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[UsageDate].[Usage_Year].&[2010],[UsageDate].[Usage_Year].&[2011],[UsageDate].[Usage_Year].&[2012],[UsageDate].[Usage_Year].&[2013],[UsageDate].[Usage_Year].&[2014],[UsageDate].[Usage_Year].&[2015],[UsageDate].[Usage_Year].&[2016],[UsageDate].[Usage_Year].&[2017],[UsageDate].[Usage_Year].&[2018],[UsageDate].[Usage_Year].&[2019],[UsageDate].[Usage_Year].&[2020]},{[Usage].[Country].&[Algeria],[Usage].[Country].&[American samoa]}) ON COLUMNS FROM [Model]) WHERE ([Measures].[Gross Billed Sum]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS";
var daxFilters = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(mdxString);
var daxDic = DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilters);
var yearValue = daxDic["[UsageDate].[Usage_Year]"];
var yearValueStr = @"[UsageDate].[Usage_Year].&[2010]
[UsageDate].[Usage_Year].&[2011]
[UsageDate].[Usage_Year].&[2012]
[UsageDate].[Usage_Year].&[2013]
[UsageDate].[Usage_Year].&[2014]
[UsageDate].[Usage_Year].&[2015]
[UsageDate].[Usage_Year].&[2016]
[UsageDate].[Usage_Year].&[2017]
[UsageDate].[Usage_Year].&[2018]
[UsageDate].[Usage_Year].&[2019]
[UsageDate].[Usage_Year].&[2020]";
Assert.AreEqual(yearValueStr, string.Join("\r\n", yearValue.Select(x => x.MDX)));
var countryValue = daxDic["[Usage].[Country]"];
var countryValueString = @"[Usage].[Country].&[Algeria]
[Usage].[Country].&[American samoa]";
Assert.AreEqual(countryValueString, string.Join("\r\n", countryValue.Select(x => x.MDX)));
#region User Friendly Result
foreach (var daxFilter in daxFilters)
{
Console.WriteLine("Column={0} ; Table={1} ; Value={2}", daxFilter.ColumnName,
daxFilter.TableName, daxFilter.Value);
}
foreach (var pair in daxDic)
{
Console.WriteLine(pair.Key + " ---------");
foreach (var value in pair.Value)
{
Console.WriteLine(value.Value);
}
}
#endregion
}
public void ParseTableMdx4()
{
string mdxString = @"SELECT NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[AsAtDate].[Year].[Year].AllMembers}, {([AsAtDate].[Month].[All])}), [AsAtDate].[Year].[Year].AllMembers, [AsAtDate].[Month])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[Activity].[Fin Activity].[All],[Activity].[Fin Activity].[Fin Activity].AllMembers}, {([ApTradeCreditor].[Supplier].[All])}), {[Activity].[Fin Activity].&[Other Capex]}, [ApTradeCreditor].[Supplier])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[AsAtDate].[Month].&[Apr]}) ON COLUMNS , ({[ApTradeCreditor].[Supplier].&[HUAWEI TECHNOLOGIES (AUSTRALIA) PTY LTD]}) ON ROWS FROM [Model]) WHERE ([ApTradeCreditor].[LiabilityAccount].[All],[ApTradeCreditor].[InvoiceNumber].&[AU1602160],[Measures].[TradeCreditorSum]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS";
var daxFilters = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(mdxString);
var daxDic = DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilters);
Assert.AreEqual("[AsAtDate].[Month].&[Apr]", daxDic["[AsAtDate].[Month]"][0].MDX);
Assert.AreEqual("[ApTradeCreditor].[Supplier].&[HUAWEI TECHNOLOGIES (AUSTRALIA) PTY LTD]", daxDic["[ApTradeCreditor].[Supplier]"][0].MDX);
#region User Friendly Result
foreach (var pair in daxDic)
{
Console.WriteLine(pair.Key + " ---------");
foreach (var value in pair.Value)
{
Console.WriteLine(value);
}
}
#endregion
}
public void ParseTableMdx5()
{
string mdxString = @"SELECT NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[PaymentDate].[Payment_Year].[All],[PaymentDate].[Payment_Year].[Payment_Year].AllMembers}, {([PaymentDate].[Payment_Month].[All])}), [PaymentDate].[Payment_Year].[Payment_Year].AllMembers, [PaymentDate].[Payment_Month])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(DrilldownMember(DrilldownMember(CrossJoin({[Activity].[Dim_1_2].[All],[Activity].[Dim_1_2].[Dim_1_2].AllMembers}, {([Activity].[Dim_1_3].[All],[APPmtDistn].[VendorName].[All])}), [Activity].[Dim_1_2].[Dim_1_2].AllMembers, [Activity].[Dim_1_3]), {[Activity].[Dim_1_3].&[Regulatory Fees]}, [APPmtDistn].[VendorName])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[PaymentDate].[Payment_Year].&[2.016E3]}) ON COLUMNS , ({[Activity].[Dim_1_3].&[Regulatory Fees]},{[APPmtDistn].[VendorName].&[AUSTRALIAN COMMUNICATIONS AND MEDIA AUTHORITY]}) ON ROWS FROM [Model]) WHERE ([APPmtDistn].[InvoiceNum].[All],[Account].[IsCash].&[1],[Measures].[Total Payment Amount Aud]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS";
var daxFilters = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(mdxString);
var daxDic = DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilters);
Assert.AreEqual("[PaymentDate].[Payment_Year].&[2.016E3]", daxDic["[PaymentDate].[Payment_Year]"][0].MDX);
Assert.AreEqual("[Activity].[Dim_1_3].&[Regulatory Fees]", daxDic["[Activity].[Dim_1_3]"][0].MDX);
Assert.AreEqual("[APPmtDistn].[VendorName].&[AUSTRALIAN COMMUNICATIONS AND MEDIA AUTHORITY]",
daxDic["[APPmtDistn].[VendorName]"][0].MDX);
#region User Friendly Result
foreach (var pair in daxDic)
{
Console.WriteLine(pair.Key + " ---------");
foreach (var value in pair.Value)
{
Console.WriteLine(value);
}
}
#endregion
}
public void ParseTableMdx2()
{
string mdxString = @"SELECT NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[UsageDate].[Usage_Year].[All],[UsageDate].[Usage_Year].[Usage_Year].AllMembers}, {([UsageDate].[Usage_MonthAbbrev].[All])}), [UsageDate].[Usage_Year].[Usage_Year].AllMembers, [UsageDate].[Usage_MonthAbbrev])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON COLUMNS , NON EMPTY Hierarchize(DrilldownMember(CrossJoin({[Usage].[Inbound or Outbound].[All],[Usage].[Inbound or Outbound].[Inbound or Outbound].AllMembers}, {([Usage].[Call Type].[All])}), [Usage].[Inbound or Outbound].[Inbound or Outbound].AllMembers, [Usage].[Call Type])) DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME ON ROWS FROM (SELECT ({[Usage].[Call Type].&[MOC], [Usage].[Call Type].&[GPRS]}) ON COLUMNS FROM [Model]) WHERE ([Usage].[Country].[All],[Measures].[Gross Billed Sum]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS";
var daxFilters = DaxDrillParser.ConvertPivotTableMdxToDaxFilterList(mdxString);
var daxDic = DaxDrillParser.ConvertDaxFilterListToDictionary(daxFilters);
var callTypeValue = daxDic["[Usage].[Call Type]"].Select(x => x.MDX);
string actual = string.Join("\r\n", callTypeValue);
string expected = @"[Usage].[Call Type].&[MOC]
[Usage].[Call Type].&[GPRS]";
Assert.AreEqual(expected, actual);
#region User Friendly Result
foreach (var pair in daxDic)
{
Console.WriteLine(pair.Key + " ---------");
foreach (var value in pair.Value)
{
Console.WriteLine(value);
}
}
#endregion
}