public override ReportFilterSetting SetHiddenFilters(SetHiddenFilterParam param) { if (UserContext.Current?.CurrentUser != null && UserContext.Current.CurrentUser.IsSystemAdmin()) { return(base.SetHiddenFilters(param)); } var filterFieldName = "ShipRegion"; Func <ReportFilterSetting, int, QuerySource, QuerySourceField, Guid, Relationship, int> addHiddenFilters = (result, filterPosition, querySource, field, equalOperator, rel) => { var firstFilter = new ReportFilterField { Alias = $"ShipRegion{filterPosition}", QuerySourceId = querySource.Id, SourceDataObjectName = querySource.Name, QuerySourceType = querySource.Type, QuerySourceFieldId = field.Id, SourceFieldName = field.Name, DataType = field.DataType, Position = ++filterPosition, OperatorId = equalOperator, Value = "WA", RelationshipId = rel?.Id, IsParameter = false, ReportFieldAlias = null }; var secondFilter = new ReportFilterField { Alias = $"ShipRegion{filterPosition}", QuerySourceId = querySource.Id, SourceDataObjectName = querySource.Name, QuerySourceType = querySource.Type, QuerySourceFieldId = field.Id, SourceFieldName = field.Name, DataType = field.DataType, Position = ++filterPosition, OperatorId = equalOperator, Value = "[Blank]", RelationshipId = rel?.Id, IsParameter = false, ReportFieldAlias = null }; result.FilterFields.Add(firstFilter); result.FilterFields.Add(secondFilter); var logic = $"({filterPosition - 1} OR {filterPosition})"; if (string.IsNullOrEmpty(result.Logic)) { result.Logic = logic; } return(filterPosition); }; var filterSetting = new ReportFilterSetting() { FilterFields = new List <ReportFilterField>() }; var position = 0; var ds = param.ReportDefinition.ReportDataSource; // Build the hidden filters for ship country fields foreach (var querySource in param.QuerySources // Scan thru the query sources that are involved in the report .Where(x => x.QuerySourceFields.Any(y => y.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase)))) // Take only query sources that have filter field name { // Pick the relationships that joins the query source as primary source // Setting the join ensure the proper table is assigned when using join alias in the UI var rels = param.ReportDefinition.ReportRelationship. Where(x => x.JoinQuerySourceId == querySource.Id) .ToList(); // Count the relationships that the filter query source is foreign query source var foreignRelCounts = param.ReportDefinition.ReportRelationship .Where(x => x.ForeignQuerySourceId == querySource.Id) .Count(); // Find actual filter field in query source var field = querySource.QuerySourceFields.FirstOrDefault(x => x.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase)); // Pick the equal operator var equalOperator = Izenda.BI.Framework.Enums.FilterOperator.FilterOperator.EqualsManualEntry.GetUid(); // In case there is no relationship that the query source is joined as primary if (rels.Count() == 0) { // Just add hidden filter with null relationship position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, null); } else { // Add another hidden filter for query source that appears in both alias primary and foreign query source of relationships. // This step is mandatory because when aliasing a primary query source, it becomes another instance of query source in the query. // So if we only add filter for alias, the original query source instance will not be impacted by the filter. That's why we need // to add another filter for original instance when it appears in both side of alias and foreign. // For example: // [Order] LEFT JOIN [Employee] // [Aliased Employee] LEFT JOIN [Department] // If the system needs to add a hidden filter to [Employee], for example: [CompanyId] = 'ALKA' // It needs to add // [Employee].[CompanyId] = 'ALKA' AND [Aliased Employee].[CompanyId] = 'ALKA' // By this way, it ensures all [Employee] instances are filtered by ALKA company id. if (foreignRelCounts > 0) { position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, null); } foreach (var rel in rels) { // Loop thru all relationships that the query source is joined as primary and add the hidden field associated with each relationship position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, rel); } } } return(filterSetting); }
/// <summary> /// Sets custom filters which are hidden to the user of the interface. /// </summary> public override ReportFilterSetting SetHiddenFilters(SetHiddenFilterParam param) { //We want to filter down to a unique value in the CustomerID var filterFieldName = "customerCode"; //Here we can use the UserContext reference to pull the TenantID value from the current users (The TenantID value you chose during the Tenant Setup portion) var tenantId = String.Empty; if (UserContext.Current.CurrentTenant != null) { tenantId = UserContext.Current.CurrentTenant.TenantID; } Func <ReportFilterSetting, int, QuerySource, QuerySourceField, Guid, Relationship, int> addHiddenFilters = (result, filterPosition, querySource, field, equalOperator, rel) => { //Here we construct a filter behind the scenes, passing the tenantId variable we made above into the value portion of the filter, so it will only show rows where customerCode = tenantId var firstFilter = new ReportFilterField { Alias = $"customerCode{filterPosition}", QuerySourceId = querySource.Id, SourceDataObjectName = querySource.Name, QuerySourceType = querySource.Type, QuerySourceFieldId = field.Id, SourceFieldName = field.Name, DataType = field.DataType, Position = filterPosition, OperatorId = equalOperator, Value = tenantId, RelationshipId = rel?.Id, IsParameter = false, ReportFieldAlias = null }; //clean up tenantId for comparison var tenantIdAllLower = tenantId.Trim().ToLower(); //added so if you are logged in at the factor's reseller or ADMIN level, the filter gets left off if (tenantIdAllLower != "none" && tenantIdAllLower.Length > 0 && tenantIdAllLower != "rfactors") { result.FilterFields.Add(firstFilter); } var logic = $"({filterPosition})"; if (string.IsNullOrEmpty(result.Logic)) { result.Logic = logic; } else { result.Logic += $" AND {logic}"; } return(filterPosition); }; var filterSetting = new ReportFilterSetting() { FilterFields = new List <ReportFilterField>() }; var position = 0; var ds = param.ReportDefinition.ReportDataSource; // Build the hidden filters for customerCode fields foreach (var querySource in param.QuerySources // Scan thru the query sources that are involved in the report .Where(x => x.QuerySourceFields.Any(y => filterFieldName.Contains(y.Name)))) //(filterFieldName, StringComparison.OrdinalIgnoreCase)))) // Take only query sources that have filter field name { // Pick the relationships that joins the query source as primary source // Setting the join ensure the proper table is assigned when using join alias in the UI var rels = param.ReportDefinition.ReportRelationship. Where(x => x.JoinQuerySourceId == querySource.Id) .ToList(); // Count the relationships that the filter query source is foreign query source var foreignRelCounts = param.ReportDefinition.ReportRelationship .Where(x => x.ForeignQuerySourceId == querySource.Id) .Count(); // Find actual filter field in query source // var field = querySource.QuerySourceFields.FirstOrDefault(x => x.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase)); var fields = querySource.QuerySourceFields.Where(x => filterFieldName.Contains(x.Name)); // Pick the equal operator var equalOperator = Izenda.BI.Framework.Enums.FilterOperator.FilterOperator.EqualsManualEntry.GetUid(); // TAKE THIS OUT LATER foreach (var field in fields) { // In case there is no relationship that the query source is joined as primary if (rels.Count() == 0) { // Just add hidden filter with null relationship position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, null); } else { // Add another hidden filter for query source that appears in both alias primary and foreign query source of relationships. // This step is mandatory because when aliasing a primary query source, it becomes another instance of query source in the query. // So if we only add filter for alias, the original query source instance will not be impacted by the filter. That's why we need // to add another filter for original instance when it appears in both side of alias and foreign. // For example: // [Order] LEFT JOIN [Employee] // [Aliased Employee] LEFT JOIN [Department] // If the system needs to add a hidden filter to [Employee], for example: [CompanyId] = 'ALKA' // It needs to add // [Employee].[CompanyId] = 'ALKA' AND [Aliased Employee].[CompanyId] = 'ALKA' // By this way, it ensures all [Employee] instances are filtered by ALKA company id. if (foreignRelCounts > 0) { position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, null); } foreach (var rel in rels) { // Loop thru all relationships that the query source is joined as primary and add the hidden field associated with each relationship position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, rel); } } } } return(filterSetting); }
public override ReportFilterSetting SetHiddenFilters(SetHiddenFilterParam param) { var filterFieldName = "ShipRegion"; Func <ReportFilterSetting, int, QuerySource, QuerySourceField, Guid, Relationship, int> addHiddenFilters = (result, filterPosition, querySource, field, equalOperator, rel) => { var firstFilter = new ReportFilterField { Alias = $"ShipRegion{filterPosition}", QuerySourceId = querySource.Id, SourceDataObjectName = querySource.Name, QuerySourceType = querySource.Type, QuerySourceFieldId = field.Id, SourceFieldName = field.Name, DataType = field.DataType, Position = ++filterPosition, OperatorId = equalOperator, Value = "WA", RelationshipId = rel?.Id, IsParameter = false, ReportFieldAlias = null }; var secondFilter = new ReportFilterField { Alias = $"ShipRegion{filterPosition}", QuerySourceId = querySource.Id, SourceDataObjectName = querySource.Name, QuerySourceType = querySource.Type, QuerySourceFieldId = field.Id, SourceFieldName = field.Name, DataType = field.DataType, Position = ++filterPosition, OperatorId = equalOperator, Value = "[Blank]", RelationshipId = rel?.Id, IsParameter = false, ReportFieldAlias = null }; result.FilterFields.Add(firstFilter); result.FilterFields.Add(secondFilter); var logic = $"({filterPosition - 1} OR {filterPosition})"; if (string.IsNullOrEmpty(result.Logic)) { result.Logic = logic; } else { result.Logic += $" AND {logic}"; } return(filterPosition); }; var filterSetting = new ReportFilterSetting() { FilterFields = new List <ReportFilterField>() }; var position = 0; var ds = param.ReportDefinition.ReportDataSource; // Build the hidden filters for ship country fields foreach (var querySource in param.QuerySources // Scan thru the query sources that are involved in the report .Where(x => x.QuerySourceFields.Any(y => y.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase)))) // Take only query sources that have filter field name { // Pick the relationships that joins the query source as primary source // Setting the join ensure the proper table is assigned when using join alias in the UI var rels = param.ReportDefinition.ReportRelationship. Where(x => x.JoinQuerySourceId == querySource.Id) .ToList(); // Find actual filter field in query source var field = querySource.QuerySourceFields.FirstOrDefault(x => x.Name.Equals(filterFieldName, StringComparison.OrdinalIgnoreCase)); // Pick the equal operator var equalOperator = Izenda.BI.Framework.Enums.FilterOperator.FilterOperator.EqualsManualEntry.GetUid(); // In case there is no relationship that the query source is joined as primary if (rels.Count() == 0) { // Just add hidden filter with null relationship position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, null); } else { // Loop thru all relationships that the query source is joined as primary and add the hidden field associated with each relationship foreach (var rel in rels) { position = addHiddenFilters(filterSetting, position, querySource, field, equalOperator, rel); } } } return(filterSetting); }