As a grouping example, let's look at this one:
query 80100 _qryGLEntries
{
QueryType = Normal;
DataAccessIntent = ReadOnly;
Caption = '_GL_GLEntries_PBI';
elements
{
dataitem(GLEntry; "G/L Entry")
{
column(GLAccountNo; "G/L Account No.")
{
}
column(GLAccountName; "G/L Account Name")
{
}
column(PostingDate; "Posting Date")
{
}
column(DimensionSetID; "Dimension Set ID")
{
}
column(Amount; Amount)
{
Method= Sum;
}
column(Quantity; Quantity)
{
Method= Sum;
}
}
}
trigger OnBeforeOpen()
begin
end;
}
You see, I'm calculating the Sum(Amount) and Sum(Quantity) by GLAccountNo, Name, Date,Dimension.
Then you can add queries like:
query 80101 _qryGLAccount
{
QueryType = Normal;
DataAccessIntent = ReadOnly;
Caption = '_GL_GLAccount_PBI';
elements
{
dataitem(GLAccount; "G/L Account")
{
column(GLAccountNo; "No.")
{
}
column(GLAccountName; Name)
{
}
column(AccountCategory; "Account Category")
{
}
column(AccountSubcategory; "Account Subcategory Descript.")
{
}
column(AccountType; "Account Type")
{
}
column(GenPostingType; "Gen. Posting Type")
{
}
}
}
trigger OnBeforeOpen()
begin
end;
}
and
query 80102 _qryDimensionSetEntry
{
QueryType = Normal;
DataAccessIntent = ReadOnly;
Caption = '_GL_DimensionSetEntry_PBI';
elements
{
dataitem(DimensionSetEntry; "Dimension Set Entry")
{
column(DimensionSetID; "Dimension Set ID")
{
}
column(DimensionCode; "Dimension Code")
{
}
column(DimensionName; "Dimension Name")
{
}
column(DimensionValueCode; "Dimension Value Code")
{
}
column(DimensionValueName; "Dimension Value Name")
{
}
column(DimensionValueID; "Dimension Value ID")
{
}
}
}
trigger OnBeforeOpen()
begin
end;
}
Then you publish each query individually as a web service and you import them individually in PBI Desktop. There you can create the relationships.
I don't recommend importing more then 1 ledger table in a PBI report. For example if you need to report on Sales and on Purchase, then I would create 2 separate PBI reports, one that uses Customer Ledgers as the fact table and another report that uses the Vendor Ledger entries. Or, if possible, one report in total that uses the Value Entries as the fact table.
I hope this explains it a bit more?
