public void TestCustomMeasureQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
var meas = MockColumn.Create("Test Measure", null, typeof(double), ADOTabularObjectType.Measure, false);
meas.MeasureExpression = "123";
var tab = new Mock <IADOTabularObject>();
tab.SetupGet(t => t.DaxName).Returns("'Internet Sales'");
meas.SelectedTable = tab.Object;
cols.Add(meas);
filters.Add(new QueryBuilderFilter(MockColumn.Create("Number of Children", "'Customer'[Number of Children]", typeof(int), ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.Is, FilterValue = "2"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters);
var expectedQry = @"/* START QUERY BUILDER */
DEFINE
MEASURE 'Internet Sales'[Test Measure] = 123
EVALUATE
SUMMARIZECOLUMNS(
'Product'[Color],
KEEPFILTERS( TREATAS( {2}, 'Customer'[Number of Children] )),
""Test Measure"", [Test Measure]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestBlankFilterTypesQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(new QueryBuilderFilter(MockColumn.Create("String 1", "'Customer'[String1]", typeof(string), ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.IsBlank, FilterValue = ""
});
filters.Add(new QueryBuilderFilter(MockColumn.Create("String 2", "'Customer'[String2]", typeof(string), ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.IsNotBlank, FilterValue = ""
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( FILTER( ALL( 'Customer'[String1] ), ISBLANK( 'Customer'[String1] ))),
KEEPFILTERS( FILTER( ALL( 'Customer'[String2] ), NOT( ISBLANK( 'Customer'[String2] )))),
""Total Sales"", [Total Sales]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestInListFilterQueryWithoutTreatAs()
{
// specify that this model supports TreatAs
mockFuncs.Setup(f => f.TreatAs).Returns(false);
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
var filterCol = MockColumn.CreateADOTabularColumn("String 1", "'Customer'[String 1]", typeof(string),
ADOTabularObjectType.Column);
filters.Add(new QueryBuilderFilter(filterCol, modelCaps)
{
FilterType = FilterType.In, FilterValue = "red\ngreen\nblue"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, _blankOrderBy);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( FILTER( ALL( 'Customer'[String 1] ), 'Customer'[String 1] IN {""red"",""green"",""blue""} )),
""Total Sales"", [Total Sales]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestTreatAsNumberFilterQuery()
{
// specify that this model supports TreatAs
mockFuncs.Setup(f => f.TreatAs).Returns(true);
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number 1", "'Customer'[Number 1]", typeof(long), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.Is, FilterValue = "123"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, _blankOrderBy);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( TREATAS( {123}, 'Customer'[Number 1] )),
""Total Sales"", [Total Sales]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestNumericFilterQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
List <QueryBuilderColumn> orderBy = new List <QueryBuilderColumn>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number of Children", "'Customer'[Number of Children]", typeof(int),
ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.Is, FilterValue = "2"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, orderBy);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( TREATAS( {2}, 'Customer'[Number of Children] )),
""Total Sales"", [Total Sales]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestPreProcessQuery()
{
var qi = new QueryInfo(testQuery + "\n" + testParam, false, false, new Mocks.MockEventAggregator());
//var finalQry = DaxHelper.PreProcessQuery(testQuery + "\n" + testParam, new Mocks.MockEventAggregator());
StringAssertion.ShouldEqualWithDiff(expectedQry.NormalizeNewline(), qi.ProcessedQuery.NormalizeNewline(), DiffStyle.Full);
}
public void TestCommentedXmlParameters()
{
string qry = "/* " +
"* FILTER(table, column = @param1)\n" +
"*/" +
"EVALUTE\n" +
"-- FILTER(table, column = @param2)\n" +
"IF(LEN(@param3)>0\n" +
",FILTER(table, column = @param3))\n" +
"<Parameters>\n" +
"<Parameter>\n" +
"<Name>param3</Name>\n" +
"<Value>value3</Value>\n" +
"</Parameter>\n" +
"</Parameters>\n";
var mockEventAgg = new Mocks.MockEventAggregator();
var queryInfo = new QueryInfo(qry, false, false, mockEventAgg);
string expectedQry = "/* " +
"* FILTER(table, column = @param1)\n" +
"*/" +
"EVALUTE\n" +
"-- FILTER(table, column = @param2)\n" +
"IF(LEN(\"value3\")>0\n" +
",FILTER(table, column = \"value3\"))";
Assert.AreEqual(1, queryInfo.Parameters.Count);
Assert.AreEqual("param3", queryInfo.Parameters["param3"].Name);
StringAssertion.ShouldEqualWithDiff(expectedQry, queryInfo.QueryWithMergedParameters, DiffStyle.Full);
}
public void HelpFormatter()
{
var p = new FluentCommandLineParser();
p.Setup <int>('p', "port");
p.Setup <bool>('l', "log")
.WithDescription("Enable Debug Logging")
.SetDefault(false);
p.Setup <string>('f', "file")
.WithDescription("Name of file to open");
p.Setup <bool>('c', "crashtest")
.SetDefault(false);
p.Setup <string>('s', "server")
.WithDescription("Server to connect to");
p.Setup <string>('d', "database")
.WithDescription("Database to connect to");
var formattedHelp = DaxStudio.Standalone.HelpFormatter.Format(p.Options).NormalizeNewline();
var expectedHelp = " -l --log Enable Debug Logging\n -f --file <string> Name of file to open\n -s --server <string> Server to connect to\n -d --database <string> Database to connect to\n".NormalizeNewline();
//Assert.AreEqual(expectedHelp, formattedHelp);
StringAssertion.ShouldEqualWithDiff(expectedHelp, formattedHelp, DiffStyle.Full);
}
public void TestDateFilterQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Date 1", "'Customer'[Birth Date]", typeof(DateTime),
ADOTabularObjectType.Column), modelCaps, mockEventAggregator)
{
FilterType = FilterType.Is, FilterValue = "2019-11-24"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, false);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( TREATAS( {DATE(2019,11,24)}, 'Customer'[Birth Date] )),
""Total Sales"", [Total Sales]
)
ORDER BY
'Product Category'[Category] ASC,
'Product'[Color] ASC
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestColumnsAndMeasureAndFiltersQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> fils = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
fils.Add(new QueryBuilderFilter(MockColumn.Create("Gender", "'Customer'[Gender]", typeof(string), ADOTabularObjectType.Column))
{
FilterType = UI.Enums.FilterType.Is, FilterValue = "M"
});
fils.Add(new QueryBuilderFilter(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column))
{
FilterType = UI.Enums.FilterType.Is, FilterValue = "Red"
});
var qry = QueryBuilder.BuildQuery(cols, fils);
var expectedQry = @"// START QUERY BUILDER
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
FILTER(KEEPFILTERS(VALUES( 'Customer'[Gender] )), 'Customer'[Gender] = ""M""),
FILTER(KEEPFILTERS(VALUES( 'Product'[Color] )), 'Product'[Color] = ""Red""),
""Total Sales"", [Total Sales]
)
// END QUERY BUILDER".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestOnlyMeasuresQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> fils = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
cols.Add(MockColumn.Create("Total Freight", "[Total Freight]", typeof(double), ADOTabularObjectType.Measure));
fils.Add(new QueryBuilderFilter(MockColumn.Create("Date 1", "'Customer'[Birth Date]", typeof(DateTime), ADOTabularObjectType.Column))
{
FilterType = UI.Enums.FilterType.Is, FilterValue = "2019-11-24"
});
var qry = QueryBuilder.BuildQuery(cols, fils);
var expectedQry = @"// START QUERY BUILDER
EVALUATE
CALCULATETABLE(
ROW(
""Total Sales"", [Total Sales],
""Total Freight"", [Total Freight]
),
FILTER(KEEPFILTERS(VALUES( 'Customer'[Birth Date] )), 'Customer'[Birth Date] = DATE(2019,11,24))
)
// END QUERY BUILDER".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestOnlyMeasuresQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
cols.Add(
MockColumn.Create("Total Freight", "[Total Freight]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Date 1", "'Customer'[Birth Date]", typeof(DateTime),
ADOTabularObjectType.Column), modelCaps, mockEventAggregator)
{
FilterType = FilterType.Is, FilterValue = "2019-11-24"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, false);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
CALCULATETABLE(
ROW(
""Total Sales"", [Total Sales],
""Total Freight"", [Total Freight]
),
KEEPFILTERS( TREATAS( {DATE(2019,11,24)}, 'Customer'[Birth Date] ))
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestBetweenNumbersFilterTypesQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number 1", "'Customer'[Number1]", typeof(long), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.Between, FilterValue = "2", FilterValue2 = "5"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, false);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( FILTER( ALL( 'Customer'[Number1] ), 'Customer'[Number1] >= 2 && 'Customer'[Number1] <= 5 )),
""Total Sales"", [Total Sales]
)
ORDER BY
'Product Category'[Category] ASC,
'Product'[Color] ASC
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestColumnsAndFiltersQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
filters.Add(new QueryBuilderFilter(MockColumn.Create("Gender", "'Customer'[Gender]", typeof(string), ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.Is, FilterValue = "M"
});
filters.Add(new QueryBuilderFilter(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.Is, FilterValue = "Red"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( TREATAS( {""M""}, 'Customer'[Gender] )),
KEEPFILTERS( TREATAS( {""Red""}, 'Product'[Color] ))
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestColumnsIsNotFilterQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Gender", "'Customer'[Gender]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.IsNot, FilterValue = "M"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, false);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( FILTER( ALL( 'Customer'[Gender] ), 'Customer'[Gender] <> ""M"" ))
)
ORDER BY
'Product Category'[Category] ASC,
'Product'[Color] ASC
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestStringFilterTypesQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("String 1", "'Customer'[String1]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.Contains, FilterValue = "ABC"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("String 2", "'Customer'[String2]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.DoesNotContain, FilterValue = "DEF"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("String 3", "'Customer'[String3]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.StartsWith, FilterValue = "GHI"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("String 4", "'Customer'[String4]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.DoesNotStartWith, FilterValue = "JKL"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, false);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( FILTER( ALL( 'Customer'[String1] ), SEARCH( ""ABC"", 'Customer'[String1], 1, 0 ) >= 1 )),
KEEPFILTERS( FILTER( ALL( 'Customer'[String2] ), NOT( SEARCH( ""DEF"", 'Customer'[String2], 1, 0 ) >= 1 ))),
KEEPFILTERS( FILTER( ALL( 'Customer'[String3] ), SEARCH( ""GHI"", 'Customer'[String3], 1, 0 ) = 1 )),
KEEPFILTERS( FILTER( ALL( 'Customer'[String4] ), NOT( SEARCH( ""JKL"", 'Customer'[String4], 1, 0 ) = 1 ))),
""Total Sales"", [Total Sales]
)
ORDER BY
'Product Category'[Category] ASC,
'Product'[Color] ASC
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestQueryParamReplacement()
{
var qi = new QueryInfo(testQuery + "\n" + testParam, false, false, new Mocks.MockEventAggregator());
//var dict = DaxHelper.ParseParams(testParam, new Mocks.MockEventAggregator());
//var finalQry = DaxHelper.replaceParamsInQuery(new StringBuilder(testQuery), dict);
var actualQry = qi.ProcessedQuery;//.Replace("\n", "");
StringAssertion.ShouldEqualWithDiff(expectedQry.NormalizeNewline(), actualQry.NormalizeNewline(), DiffStyle.Full);
}
public void TestNumericFilterTypesQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
List <QueryBuilderColumn> orderBy = new List <QueryBuilderColumn>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number 1", "'Customer'[Number1]", typeof(int), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.GreaterThan, FilterValue = "1"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number 2", "'Customer'[Number2]", typeof(int), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.GreaterThanOrEqual, FilterValue = "2"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number 3", "'Customer'[Number3]", typeof(int), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.LessThan, FilterValue = "3"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Number 4", "'Customer'[Number4]", typeof(int), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.LessThanOrEqual, FilterValue = "4"
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, orderBy);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( FILTER( ALL( 'Customer'[Number1] ), 'Customer'[Number1] > 1 )),
KEEPFILTERS( FILTER( ALL( 'Customer'[Number2] ), 'Customer'[Number2] >= 2 )),
KEEPFILTERS( FILTER( ALL( 'Customer'[Number3] ), 'Customer'[Number3] < 3 )),
KEEPFILTERS( FILTER( ALL( 'Customer'[Number4] ), 'Customer'[Number4] <= 4 )),
""Total Sales"", [Total Sales]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestDontBreakInSlashComments()
{
var sm = new LongLineStateMachine(25);
var input = "var _var2 = {(1,123),(2,456),(3,789),(4,123),(5,456) //,(6,789) this is a long comment with the following chars )} which would normally trigger a line break \n }";
var actual = sm.ProcessString(input);
var expected = "var _var2 = {(1,123),(2,456)\n" +
",(3,789),(4,123),(5,456) //,(6,789) this is a long comment with the following chars )} which would normally trigger a line break \n" +
" }";
StringAssertion.ShouldEqualWithDiff(expected, actual, DiffStyle.Compact);
}
public void TestBasicLineBreaking()
{
var sm = new LongLineStateMachine(25);
var input = "var _var2 = {(1,123),(2,456),(3,789),(4,123),(5,456),(6,789)}";
var actual = sm.ProcessString(input);
var expected = "var _var2 = {(1,123),(2,456)\n" +
",(3,789),(4,123),(5,456),(6,789)\n" +
"}";
StringAssertion.ShouldEqualWithDiff(expected, actual, DiffStyle.Compact);
}
public void TestToggleDebugCommasWithLeadingBlockComment()
{
var input = @"Line 1,
/* leading comment */ Line 2,
Line 3";
var expected = @"Line 1
,/* leading comment */ Line 2
,Line 3";
var actual = FormatDebugMode.ToggleDebugCommas(input);
//Assert.AreEqual(expected, actual);
StringAssertion.ShouldEqualWithDiff(expected.NormalizeNewline(), actual.NormalizeNewline(), DiffStyle.Full);
actual = FormatDebugMode.ToggleDebugCommas(actual);
//Assert.AreEqual(expected, actual);
StringAssertion.ShouldEqualWithDiff(input.NormalizeNewline(), actual.NormalizeNewline(), DiffStyle.Full);
}
public void TestParameterQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
// override the default sort direction
cols[1].SortDirection = SortDirection.None;
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Gender", "'Customer'[Gender]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.Is, FilterValue = "M"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column),
modelCaps, mockEventAggregator)
{
FilterType = FilterType.Is, FilterValue = "color", FilterValueIsParameter = true
});
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, false);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
KEEPFILTERS( TREATAS( {""M""}, 'Customer'[Gender] )),
KEEPFILTERS( TREATAS( {@color}, 'Product'[Color] ))
)
ORDER BY
'Product Category'[Category] ASC
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestToggleDebugCommas()
{
var input = @"Date =
VAR FirstYear = -- Customizes the first year to use
YEAR ( MIN ( Sales[Order Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO (),
YEAR ( [Date] ) >= FirstYear
),
""Year"", DATE ( YEAR ( [Date] ), 12, 31 ),
""Year Month"", EOMONTH( [Date], 0 ),
""Month"", FORMAT( [Date], ""mmm"" ),
""Month Number"", MONTH( [Date] ),
""Day of Week"", FORMAT( [Date], ""ddd"" ),
""Day of Week Number"", WEEKDAY( [Date], 1 )
)";
var expected = @"Date =
VAR FirstYear = -- Customizes the first year to use
YEAR ( MIN ( Sales[Order Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO ()
, YEAR ( [Date] ) >= FirstYear
)
, ""Year"", DATE ( YEAR ( [Date] ), 12, 31 )
, ""Year Month"", EOMONTH( [Date], 0 )
, ""Month"", FORMAT( [Date], ""mmm"" )
, ""Month Number"", MONTH( [Date] )
, ""Day of Week"", FORMAT( [Date], ""ddd"" )
, ""Day of Week Number"", WEEKDAY( [Date], 1 )
)";
var actual = FormatDebugMode.ToggleDebugCommas(input);
//Assert.AreEqual(expected, actual);
StringAssertion.ShouldEqualWithDiff(expected.NormalizeNewline(), actual.NormalizeNewline(), DiffStyle.Full);
actual = FormatDebugMode.ToggleDebugCommas(actual);
//Assert.AreEqual(expected, actual);
StringAssertion.ShouldEqualWithDiff(input.NormalizeNewline(), actual.NormalizeNewline(), DiffStyle.Full);
}
public void TestToggleDebugCommasWithSimpleIndentedComments()
{
var input = @"Line 1,
-- comment with leading space
Line 2,
Line 3";
var expected = @"Line 1
-- comment with leading space
,Line 2
,Line 3";
var actual = FormatDebugMode.ToggleDebugCommas(input);
//Assert.AreEqual(expected, actual);
StringAssertion.ShouldEqualWithDiff(expected.NormalizeNewline(), actual.NormalizeNewline(), DiffStyle.Full);
actual = FormatDebugMode.ToggleDebugCommas(actual);
//Assert.AreEqual(expected, actual);
StringAssertion.ShouldEqualWithDiff(input.NormalizeNewline(), actual.NormalizeNewline(), DiffStyle.Full);
}
public void TestColumnsOnlyQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters);
var expectedQry = @"/* START QUERY BUILDER */
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color]
)
/* END QUERY BUILDER */".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public void TestSerializer()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> filters = new List <QueryBuilderFilter>();
List <QueryBuilderColumn> orderBy = new List <QueryBuilderColumn>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Gender", "'Customer'[Gender]", typeof(string), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.Is, FilterValue = "M"
});
filters.Add(
new QueryBuilderFilter(
MockColumn.CreateADOTabularColumn("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column),
modelCaps)
{
FilterType = FilterType.Is, FilterValue = "Red"
});
orderBy.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string),
ADOTabularObjectType.Column));
var qry = QueryBuilder.BuildQuery(modelCaps, cols, filters, orderBy);
QueryBuilderViewModel vm = new QueryBuilderViewModel(new MockEventAggregator(), null, mockOptions);
cols.ForEach(c => vm.Columns.Add(c));
filters.ForEach(f => vm.Filters.Add(f));
orderBy.ForEach(o => vm.OrderBy.Add(o));
var json = vm.GetJson();
var expectedJson = @"{}";
StringAssertion.ShouldEqualWithDiff(expectedJson, json, DiffStyle.Full);
}
public void TestMeasureOverrideQuery()
{
List <QueryBuilderColumn> cols = new List <QueryBuilderColumn>();
List <QueryBuilderFilter> fils = new List <QueryBuilderFilter>();
cols.Add(MockColumn.Create("Category", "'Product Category'[Category]", typeof(string), ADOTabularObjectType.Column));
cols.Add(MockColumn.Create("Color", "'Product'[Color]", typeof(string), ADOTabularObjectType.Column));
var meas = MockColumn.Create("Total Sales", "[Total Sales]", typeof(double), ADOTabularObjectType.Measure);
meas.MeasureExpression = "123";
var tab = new Mock <IADOTabularObject>();
tab.SetupGet(t => t.DaxName).Returns("'Internet Sales'");
meas.SelectedTable = tab.Object;
cols.Add(meas);
fils.Add(new QueryBuilderFilter(MockColumn.Create("Number of Childer", "'Customer'[Number of Children]", typeof(int), ADOTabularObjectType.Column))
{
FilterType = UI.Enums.FilterType.Is, FilterValue = "2"
});
var qry = QueryBuilder.BuildQuery(cols, fils);
var expectedQry = @"// START QUERY BUILDER
DEFINE
MEASURE 'Internet Sales'[Total Sales] = 123
EVALUATE
SUMMARIZECOLUMNS(
'Product Category'[Category],
'Product'[Color],
FILTER(KEEPFILTERS(VALUES( 'Customer'[Number of Children] )), 'Customer'[Number of Children] = 2),
""Total Sales"", [Total Sales]
)
// END QUERY BUILDER".Replace("\r", "");
StringAssertion.ShouldEqualWithDiff(expectedQry, qry, DiffStyle.Full);
}
public static void OrThrow(this StringAssertion assertion, string location, Func <string> func)
{
assertion.OrThrow(() => withLocation(location, func));
}