Hi all,
I have created a Query object which looks into G/L Entry. I have 3 dimensions named
- Project
- Department
- Company
I have set them in General Ledger setup as Global Dimension1, Global Dimension 2 and Shortcut dimension 3.
When i query, I cannot find Shortcut dimension in G/L Entry. when i tried to join it with Dimension Set Entry, i get 3 separate rows.
Here is my Dataitem query.
dataitem(G_L_Entry; "G/L Entry")
{
column(G_L_Account_No_; "G/L Account No.") { }
column(G_L_Account_Name; "G/L Account Name") { }
column(Amount; Amount) { }
column(Posting_Date; "Posting Date") { }
column(Document_No_; "Document No.") { }
column(External_Document_No_; "External Document No.") { }
column(Description; Description) { }
dataitem(Project; "Dimension Set Entry")
{
DataItemLink = "Dimension Set ID" = G_L_Entry."Dimension Set ID";
column(Dimension_Value_Code; "Dimension Value Code")
{
}
dataitem(Project_Name; "Dimension Value")
{
DataItemLink = "Dimension Value ID" = Project."Dimension Value ID";
column(DimName; Name) { }
}
}
}
and my output is:
{
"G_L_Account_No_":"60510",
"Amount":-100,
"Posting_Date":"2020-06-30",
"Document_No_":"GEN000032",
"External_Document_No_":"12232",
"Description":"Drug Substance GMP Manufacturing",
"G_L_Account_Name":"Drug Substance GMP Manufacturing",
"Dimension_Value_Code":"LAB1636",
"DimName":"Lab1636","Name":"",
"AuxiliaryIndex1":302,
"AuxiliaryIndex2":7,
"AuxiliaryIndex3":"COMPANY",
"AuxiliaryIndex4":"COMPANY",
"AuxiliaryIndex5":"LAB1636",
"AuxiliaryIndex6":0,
"AuxiliaryIndex7":"",
"Document_Type_FilterOnly":null
},
{
"G_L_Account_No_":"60510",
"Amount":-100,
"Posting_Date":"2020-06-30",
"Document_No_":"GEN000032",
"External_Document_No_":"12232",
"Description":"Drug Substance GMP Manufacturing",
"G_L_Account_Name":"Drug Substance GMP Manufacturing",
"Dimension_Value_Code":"BIOLOGY",
"DimName":"BIOLOGY",
"Name":"",
"AuxiliaryIndex1":302,
"AuxiliaryIndex2":7,
"AuxiliaryIndex3":"DEPARTMENT",
"AuxiliaryIndex4":"DEPARTMENT",
"AuxiliaryIndex5":"BIOLOGY",
"AuxiliaryIndex6":0,
"AuxiliaryIndex7":"",
"Document_Type_FilterOnly":null
},
{
"G_L_Account_No_":"60510",
"Amount":-100,
"Posting_Date":"2020-06-30",
"Document_No_":"GEN000032",
"External_Document_No_":"12232",
"Description":"Drug Substance GMP Manufacturing",
"G_L_Account_Name":"Drug Substance GMP Manufacturing",
"Dimension_Value_Code":"LAB-001|GABA ASD",
"DimName":"LAB-001 - GABA - Ginty",
"AuxiliaryIndex1":302
,"AuxiliaryIndex2":7,
"AuxiliaryIndex3":"PROJECT",
"AuxiliaryIndex4":"PROJECT",
"AuxiliaryIndex5":"LAB-001|GABA ASD",
"AuxiliaryIndex6":0,
"AuxiliaryIndex7":"",
"Document_Type_FilterOnly":null
}
How can i get all relevant dimensions in same row instead of 3 rows as shown.
Regards
