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 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 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 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 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 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 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 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 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 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 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 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 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 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);
}
//[ExpectedException(typeof(ArgumentException))]
public void TestInvalidDateFilterQuery()
{
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("Date 1", "'Customer'[Birth Date]", typeof(DateTime), ADOTabularObjectType.Column), modelCaps)
{
FilterType = FilterType.Is, FilterValue = "24/24/2019"
});
ExceptionAssert.Throws <ArgumentException>(() => QueryBuilder.BuildQuery(modelCaps, cols, filters), "Unable to parse the value '24/24/2019' as a DateTime value");
}
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 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);
}
