You’re offline. This is a read only version of the page.
Hello,
I am creating a report in report builder from Table
GENERALJOURNALACCOUNTENTRY
There is field called ledgerdimension. when i INNER JOIN MAINACCOUNTLEDGERDIMENSIONVIEW i am able to get the main account + deptartment + worker.
Now what i want to filter is worker.
How do i extract the dimension worker from here.
Can i join this table with HCM worker or any other worker table .
Please help.
Note that i know to build reports only in report builder.
*This post is locked for comments
Hi,
You can get individual dimension values as below :
GeneralJournalAccountEntry generalJournalAccountEntry; DimensionAttributeLevelValueAllView dimAttrView;
DimensionAttribute dimAttr;
select DisplayValue from dimAttrView
where dimAttrView.ValueCombinationRecId == generalJournalAccountEntry.LedgerDimension
join BackingEntityType from dimAttr
where dimAttr.RecId == dimAttrView.DimensionAttribute
{
if(dimAttr.BackingEntityType == tablenum(DimAttributeHcmWorker):
info(strFmt(“Worker: %1”, dimAttrView.DisplayValue));
}
}
For adding dimension into the query you may use ysQuery::addDimensionAttributeRange Method
msdn.microsoft.com/.../sysquery.adddimensionattributerange.aspx
The tables using ledger dimension, such as GeneralJournalAccountEntry
join up with the rest of the financial dimension tables as follows.
select * -- replace * with the fields you actually want.
FROM
GENERALJOURNALACCOUNTENTRY left outer join
DIMENSIONATTRIBUTEVALUECOMBINATION DAVC
on GENERALJOURNALACCOUNTENTRY.LEDGERDIMENSION = DAVC.RECID
INNER JOIN
DIMENSIONATTRIBUTEVALUEGROUPCOMBINATION DAVGC ON
DAVC.RECID = DAVGC.DIMENSIONATTRIBUTEVALUECOMBINATION INNER JOIN
DIMENSIONATTRIBUTELEVELVALUE AS DAVL ON
DAVL.DIMENSIONATTRIBUTEVALUEGROUP = DAVGC.DIMENSIONATTRIBUTEVALUEGROUP INNER JOIN
DIMENSIONATTRIBUTEVALUE AS DAV ON DAV.RECID = DAVL.DIMENSIONATTRIBUTEVALUE INNER JOIN
DIMENSIONATTRIBUTE DA ON DA.RECID = DAV.DIMENSIONATTRIBUTE
To filter the dimension attribute rows down to only the row for the worker dimension, add a where clause like WHERE DA.RECID = '5637144849' -- replace the filter value with the RecId for your worker dimension
You could also filter on Name instead of recid. Either way you want to select only the row for the worker dimension from DIMENSIONATTRIBUTE table.
To bring in values for workers, add a left outer join to DIMATTRIBUTEHCMWORKER
on DIMENSIONATTRIBUTE.RECID to DIMATTRIBUTEHCMWORKER.KEY_
The VALUE field in DIMATTRIBUTEHCMWORKER is the personnel number and the NAME field is the worker's name.
P.S. There may not actually be records in GeneralJournalAccountEntry are related to worker. For unrelated rows, the query with outer joins would return a NULL value for fields from DIMATTRIBUTEHCMWORKER. If you would rather leave out all the rows not related to worker dimension, change the outer joins to inner joins.
Hope this helps. I can't guarantee this gives you everything you want for your report but it should give you and idea of which tables are involved and how they link up.
-- Lance.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (