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