So you have Positions, and Dimensions which resemble each other but are not really related?
Could you please share some example values of both?
Perhaps there's some other approach to the problem, but only such person who knows more details could know.
But, based on only the details that you wanted to share so far, you need to first convert dimensionFinancialTag.Value into a string that you can use as criteria for HcmPosition.PositionId.
Something like this (please treat it as an illustrative example - your responsibility is to apply this approach to your exact situation):
HcmPositionId positionIdCriteria = strIns(dimensionFinancialTag.Value, '-', 3);
select sum(NetAccountingCurrencyAmount),sum(GrossAccountingCurrencyAmount),count(Worker) from payrollPayStatement
where payrollPayStatement.PayPeriod == payrollPayPeriodRecId
exists join payrollPayStatementLine where payrollPayStatementLine.PayStatement==payrollPayStatement.RecId
exists join hcmPosition where hcmPosition.RecId==payrollPayStatementLine.Position
&& hcmPosition.PositionId like positionIdCriteria;
