Codeunit 6320 Power BI Filter Helper
src/Layers/W1/BaseApp/Modules/System/PowerBI/Embedding/PowerBIFilterHelper.Codeunit.al173 lines, Copyright (c) Microsoft Corporation. MIT
namespace System.Integration.PowerBI;codeunit 6320 "Power BI Filter Helper"{ Access = Internal; var PowerBiServiceMgt: Codeunit "Power BI Service Mgt."; BasicFilterSchemaTok: Label 'http://powerbi.com/product/schema#basic', Locked = true; // Telemetry labels UnsupportedFilterTypeTelemetryMsg: Label 'Cannot filter Power BI report: the filter type is not supported.', Locked = true; SelectionTooLargeTelemetryMsg: Label 'Power BI filter skipped. %1 records selected.', Locked = true; NoFilterDefinedTelemetryMsg: Label 'There is no filter defined in the Power BI report.', Locked = true; procedure VariantToFilterRecord(InputSelectionVariant: Variant; var PowerBIFilter: Record "Power BI Filter") var ValuesJArray: JsonArray; begin AddToJsonArray(ValuesJArray, InputSelectionVariant); PowerBiFilter.DeleteAll(); PowerBIFilter.Init(); PowerBIFilter.FilterOperator := PowerBIFilter.FilterOperator::"In"; PowerBIFilter.OverwriteFilterValues(ValuesJArray); PowerBIFilter.Insert(); end; procedure RecordRefToFilterRecord(FilteringRecordRef: RecordRef; FieldNumber: Integer; var PowerBIFilter: Record "Power BI Filter") var FilteringFieldRef: FieldRef; ValuesJArray: JsonArray; begin PowerBiFilter.DeleteAll(); if (FilteringRecordRef.GetFilters() = '') or (not IsSupportedFilterValuesCount(FilteringRecordRef.Count())) then begin PowerBIFilter.Init(); PowerBIFilter.FilterOperator := PowerBIFilter.FilterOperator::All; PowerBIFilter.Insert(); exit; end; FilteringRecordRef.SetLoadFields(FieldNumber); if FilteringRecordRef.FindSet() then repeat FilteringFieldRef := FilteringRecordRef.Field(FieldNumber); AddToJsonArray(ValuesJArray, FilteringFieldRef.Value); until FilteringRecordRef.Next() = 0; PowerBIFilter.Init(); PowerBIFilter.FilterOperator := PowerBIFilter.FilterOperator::"In"; PowerBIFilter.OverwriteFilterValues(ValuesJArray); PowerBIFilter.Insert(); end; procedure MergeIntoFirstFilter(ReportFiltersInfo: JsonArray; var PowerBIFilter: Record "Power BI Filter") ReportFiltersWithValues: JsonArray var FilterPath: Text; begin FilterPath := FindFirstFilter(ReportFiltersInfo); if (FilterPath = '') or (ReportFiltersInfo.Count() = 0) or PowerBIFilter.IsEmpty() then exit(ReportFiltersInfo); ReportFiltersWithValues := ReportFiltersInfo.Clone().AsArray(); MergeIntoFilterByPath(ReportFiltersWithValues, FilterPath, PowerBIFilter); exit(ReportFiltersWithValues); end; local procedure MergeIntoFilterByPath(var FiltersJsonArray: JsonArray; FilterPath: Text; var PowerBIFilter: Record "Power BI Filter" temporary) var FilterStructure: JsonObject; FilterStuctureToken: JsonToken; begin // Filter Schema: https://learn.microsoft.com/en-us/javascript/api/overview/powerbi/control-report-filters FiltersJsonArray.SelectToken(FilterPath, FilterStuctureToken); FilterStructure := FilterStuctureToken.AsObject(); FilterStructure.Remove('operator'); FilterStructure.Remove('values'); FilterStructure.Add('operator', Format(PowerBIFilter.FilterOperator)); FilterStructure.Add('values', PowerBIFilter.ReadFilterValues()); end; local procedure FindFirstFilter(JArrayFilters: JsonArray): Text var JTokenFilter: JsonToken; JTokenSchema: JsonToken; begin foreach JTokenFilter in JArrayFilters do if JTokenFilter.IsObject() then if JTokenFilter.AsObject().Get('$schema', JTokenSchema) then if JTokenSchema.IsValue() then if JTokenSchema.AsValue().AsText() = BasicFilterSchemaTok then exit(JTokenFilter.Path()); Session.LogMessage('0000KQT', NoFilterDefinedTelemetryMsg, Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', PowerBiServiceMgt.GetPowerBiTelemetryCategory()); exit(''); end; local procedure IsSupportedFilterValuesCount(FilterValuesCount: Integer): Boolean begin if FilterValuesCount <= 100 then exit(true); Session.LogMessage('0000LMO', StrSubstNo(SelectionTooLargeTelemetryMsg, FilterValuesCount), Verbosity::Normal, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', PowerBiServiceMgt.GetPowerBiTelemetryCategory()); exit(false); end; local procedure AddToJsonArray(var JsonArray: JsonArray; InputSelectionVariant: Variant) var TextVariable: Text; DateVariable: Date; TimeVariable: Time; DateTimeVariable: DateTime; DecimalVariable: Decimal; BooleanVariable: Boolean; IntegerVariable: Integer; begin case true of InputSelectionVariant.IsText, InputSelectionVariant.IsGuid, InputSelectionVariant.IsCode: begin TextVariable := InputSelectionVariant; JsonArray.Add(TextVariable); end; InputSelectionVariant.IsDate: begin DateVariable := InputSelectionVariant; JsonArray.Add(DateVariable); end; InputSelectionVariant.IsTime: begin TimeVariable := InputSelectionVariant; JsonArray.Add(TimeVariable); end; InputSelectionVariant.IsDateTime: begin DateTimeVariable := InputSelectionVariant; JsonArray.Add(DateTimeVariable); end; InputSelectionVariant.IsInteger: begin IntegerVariable := InputSelectionVariant; JsonArray.Add(IntegerVariable); end; InputSelectionVariant.IsBoolean: begin BooleanVariable := InputSelectionVariant; JsonArray.Add(BooleanVariable); end; InputSelectionVariant.IsDecimal: begin DecimalVariable := InputSelectionVariant; JsonArray.Add(DecimalVariable); end; else begin Session.LogMessage('0000GJU', UnsupportedFilterTypeTelemetryMsg, Verbosity::Warning, DataClassification::SystemMetadata, TelemetryScope::ExtensionPublisher, 'Category', PowerBiServiceMgt.GetPowerBiTelemetryCategory()); TextVariable := Format(InputSelectionVariant, 0, 9); JsonArray.Add(TextVariable); end; end; end;}