Querying records by Dimension Set ID

TESDeveloper

I am attempting is to create a page on a role center that calculates the total amount for G/L Entries based on specific dimensions. Currently, the page performs this calculation when it loads.

I have created a query for this purpose, which is functional but not optimized for performance. I'm wondering if there are any ways to improve its efficiency. The function used to read the query serves two purposes: calculating the total amount and displaying the records found in the query. However, during the initial load, I only need to calculate the total amount. I'm unsure if there are is any improvements I can make.

We only need to filter for a maximum of two dimension values.

Query

query 50000 "Get Actual By Dim."
{
    QueryType = Normal;

    elements
    {
        dataitem(DimensionSetEntry; "Dimension Set Entry")
        {

            column(DimensionCode_DimensionSetEntry; "Dimension Code")
            {
            }
            column(DimensionName_DimensionSetEntry; "Dimension Name")
            {
            }
            column(DimensionSetID_DimensionSetEntry; "Dimension Set ID")
            {
            }
            column(DimensionValueCode_DimensionSetEntry; "Dimension Value Code")
            {
            }
            column(DimensionValueID_DimensionSetEntry; "Dimension Value ID")
            {
            }
            column(DimensionValueName_DimensionSetEntry; "Dimension Value Name")
            {
            }
            column(GlobalDimensionNo_DimensionSetEntry; "Global Dimension No.")
            {
            }
            dataitem(DimensionSetEntry2; "Dimension Set Entry")
            {
                DataItemLink = "Dimension Set ID" = DimensionSetEntry."Dimension Set ID";
                column(DimensionCode_DimensionSetEntry2; "Dimension Code")
                {
                }
                column(DimensionName_DimensionSetEntry2; "Dimension Name")
                {
                }
                column(DimensionSetID_DimensionSetEntry2; "Dimension Set ID")
                {
                }
                column(DimensionValueCode_DimensionSetEntry2; "Dimension Value Code")
                {
                }
                column(DimensionValueID_DimensionSetEntry2; "Dimension Value ID")
                {
                }
                column(DimensionValueName_DimensionSetEntry2; "Dimension Value Name")
                {
                }
                column(GlobalDimensionNo_DimensionSetEntry2; "Global Dimension No.")
                {
                }
                dataitem(GLEntry; "G/L Entry")
                {

                    DataItemLink = "Dimension Set ID" = DimensionSetEntry2."Dimension Set ID";
                    SqlJoinType = InnerJoin;
                    column(Entry_No_GLEntry; "Entry No.")
                    { }
                    filter(DimensionSetID_GLEntry; "Dimension Set ID")
                    {
                    }
                    column(Amount_GLEntry; Amount)
                    {
                        Method = Sum;
                    }
                    column(CreditAmount_GLEntry; "Credit Amount")
                    {
                    }
                    column(DebitAmount_GLEntry; "Debit Amount")
                    {
                    }
                    filter(G_L_Account_No_GLEntry; "G/L Account No.")
                    {

                    }
                    filter(Posting_Date_GLEntry; "Posting Date")
                    {

                    }
                }
            }
        }
    }
}

How I call the Query

local procedure GetActual(OpenPage: Boolean): Decimal
    var
        GLEntry: Record "G/L Entry";
        TempGLEntry: Record "G/L Entry" temporary;
        GetActualByDim: Query "Get Actual By Dim.";
        Amount: Decimal;
    begin        
        Clear(GetActualByDim);
        if GeneralLedgerSetup.Level_1_Dim <> '' then begin
            GetActualByDim.SetRange(GetActualByDim.DimensionCode_DimensionSetEntry, Rec."Dimension Code");
            GetActualByDim.SetRange(GetActualByDim.DimensionValueCode_DimensionSetEntry, Rec.Code);
        end;
        if GeneralLedgerSetup.Level_2_Dim <> '' then begin
            GetActualByDim.SetRange(GetActualByDim.DimensionCode_DimensionSetEntry2, GeneralLedgerSetup.Level_2_Dim);
            GetActualByDim.Setfilter(GetActualByDim.DimensionValueCode_DimensionSetEntry2, Level2DimensionFilter);
        end;
        GetActualByDim.SetFilter(GetActualByDim.G_L_Account_No_GLEntry, GLAccountFilter);
        GetActualByDim.SetFilter(GetActualByDim.Amount_GLEntry, '<>%1', 0);
        GetActualByDim.Open();
        while GetActualByDim.Read() do
            if GLEntry.Get(GetActualByDim.Entry_No_GLEntry) then begin
                TempGLEntry := GLEntry;
                if TempGLEntry.Insert() then
                    Amount += GetActualByDim.Amount_GLEntry;
            end;

        if OpenPagethen
            Page.Run(0, TempGLEntry);

        exit(Amount);

    end;

txeriff

By nav standard if you go to chart of accounts and use the flowfilters (limit totals to) you can filter by dimensions and will show you the balances.

vaprog

Analysis views are made for reporting on dimensions. You should look into this. You can have them adjust on the fly or on demand.