Hi,
I’m trying to build a query that aggregates the amounts per combination of G/L Account, Dimension and Posting Date.
CurrencyCode and OrderAmount are fields that we’ve extended the G/L entry table with.
The problem I’m facing is that the same amount is shown per aggregation.
Can I get some pointers on how to structure my query?
Here’s the BC navigate view.
And here is the result of my OData query.
And the query.
query 70304300 "G/L Outgoing Balance_SCY"
{
Caption = 'G/L Outgoing Balance';
OrderBy = ascending(PostingDate, AccountNo);
elements
{
dataitem(Dimension; Dimension)
{
column(DimensionCode; Code)
{
Caption = 'Dimension Code';
}
column(DimensionName; Name)
{
Caption = 'Dimension Name';
}
dataitem(Dimension_Set_Entry; "Dimension Set Entry")
{
DataItemLink = "Dimension Code" = Dimension.Code;
SqlJoinType = InnerJoin;
column(DimensionValueName; "Dimension Value Name")
{
Caption = 'Dimension Value Name';
}
dataitem(G_L_Entry; "G/L Entry")
{
DataItemLink = "Dimension Set ID" = Dimension_Set_Entry."Dimension Set ID";
SqlJoinType = InnerJoin;
column(PostingDate; "Posting Date")
{
Caption = 'Posting Date';
}
column(LCYAmount; Amount)
{
Caption = 'LCY Amount';
Method = Sum;
}
column(OrderAmount; "Order Amount")
{
Caption = 'Order Amount';
Method = Sum;
}
column(OrderCurrencyCode; "Currency Code")
{
Caption = 'Order Currency Code';
}
dataitem(G_L_Account; "G/L Account")
{
DataItemLink = "No." = G_L_Entry."G/L Account No.";
SqlJoinType = InnerJoin;
column(AccountNo; "No.")
{
Caption = 'G/L Account Number';
}
column(AccountName; Name)
{
Caption = 'G/L Account Name';
}
}
}
}
}
}
}