Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Unanswered

SysComputedColumn rounding

(0) ShareShare
ReportReport
Posted on by 13

Hello,

for the first time I am working with SysComputedColumn and I am having a problem with this requirement. There is a view with SysComputedColumn, that returns some value with some number of decimal places. Iwant to take this value from system:

pastedimage1676909662726v1.png_2D00_864x642.png_2D00_640x480.png_2D00_640x480.png

and round the value, that is returned by the SysComputedColumn to the same number of decimal places as is in the General rounding rule for EUR.

I have created SQL select statement, that returns the value of the General rounding rule (0.01). But I have no idea, how to round the SysComputedColumn returned value. I was thinking about something like:

return strFmt('ROUND(%1, mySelectStatement)',
SysComputedColumn::if(SysComputedColumn::and(projActualCostItemList),
SPLProjTransPostingCube::projReportingActualConsCostHours(),
SPLProjTransPostingCube::projActualConsumptionCostHours()
));

but I do not know, if it is possible to use the round function in SysComputedColumn or round function inside strFmt, because as I know, it creates SELECT statements from SysComputedColumns. I did not find any method for rounding the SysComputedColumn, just this thread:     but it is not what I am looking for, as I need the number of decimals is not a constant, but it should be dynamic based on the value of General rounding rule.

Any idea, please? Sorry if I missed something, but this is first time I am working with SysComputedColumn, never have even heard about that before

  • Martin Dráb Profile Picture
    Martin Dráb 230,900 Most Valuable Professional on at
    RE: SysComputedColumn rounding

    The purpose of the method is generating SQL code. You can use normal X++ methods, but only to generate the string with SQL code. You can't directly fetch data in X++; you need to generate SQL fetching the data.

    Therefore your next step is checking what code you're generating at the moment and comparing it with what you intend to get.

  • Sayah Profile Picture
    Sayah 13 on at
    RE: SysComputedColumn rounding

    goshoom 

    Ok thanks. And where is the problem in my case, please? When I use it for testing purposes like

    return strFmt('round(%1, 1)',
                               SysComputedColumn::if(
                               SysComputedColumn::and(projActualCostItemList),
                               SPLProjTransPostingCube::projReportingActualConsCostHours(),
                               SPLProjTransPostingCube::projActualConsumptionCostHours()));

    it means I want to round the value to one decimal place. The value that I have is originaly like xxx.2543 but the result when I want to round that to one decimal place is xxx.6.

    And how can I work with that dynamic field that determines, how many decimal places should be used? Because as I know, when I have a " public static server str method()" on a view, I am not able to use/create normal x methods, or am I? just methods with SysComputedColumns?

  • Martin Dráb Profile Picture
    Martin Dráb 230,900 Most Valuable Professional on at
    RE: SysComputedColumn rounding

    Yes, you can use ROUND in a computed column method. You can use anything that produces correct T-SQL code (for the given context).

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,074 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,900 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans