Issue with Column-wise Sum Calculation in Report

Hello,

Does changing Cell Evaluation Order correct the error?

If not , the Value calculation may be causing problem. What do the figures look like when the Value calculation is removed?

It’s hard to make an exact suggestion with all column headers blurred, and Print Control, Visible formula hidden. (I can’t match up columns in the report to specific columns in the Column set.)

Laura

​@Laura02  When the Value calculation is removed the entire Column is getting Blank. In Column Value, we are calculating based on the Account Code condition and in the Cell Evaluation Order, a Column is selected. 

Please look into the attached ARM Report. To review consider Columns Q .

Thank you.

Hello,

I’ve downloaded your report and printed in my Demo. I am getting mostly $0 using your If statement.

I tried a few changes:

1. I separated your Iif statement into two parts, to try and narrow what is causing the blanks and zeros.
2. I added a very simple if statement to see whether I could get one account to correctly print.
3. Your rows are all using Account Class  in the Data Source, as they should. I added a few lines listing one account at a time, in case Acumatica uses Account Number from the Data Source of the Row. The result is still zero or blank in every case except COGS - for some reason that prints in two of my tests.

I hope someone else can add to your thread.  My results seem to imply that using @AccountCode in calculations does not work:

I’m sorry I couldn’t be more helpful.

Laura

Hello,

Today I tried my advice to change Cell Evaluation Order in column Q from Column to Row. This change made no difference.

The report has only Row Set and Column Set, no Unit Set.

I checked Sales Totals in Excel.  GL column totals are correct when printed from Sales Dem,o but the calculated Columns contain 0 in nearly every row and blank or 0 in all totals. Why?  (What am I missing, Community experts?)

Can @AccountCode be successfully used in formulas? All formulas have successfully validated.

Col. Q Value formula:=IIf(    (@AccountCode >= '50000' AND @AccountCode < '60000') OR (@AccountCode='41000') , E+H , E*(H100248/(H100248+K100248))+H)

Col. R: =IIf(    (@AccountCode >= 50000 AND @AccountCode < 60000) OR (@AccountCode=41000) , E + H , 0)

Col S: =IIf(    (@AccountCode < 50000 OR @AccountCode > 60000) AND (@AccountCode <>41000) , E*(H100248/(H100248+K100248))+H,0)

Col T : =IIf(@AccountCode = '40000',B,0)

Thank you!

Laura

Hi ​@Laura02 

I sincerely appreciate your efforts in investigating the issue. However, if we switch the cell evaluation to  ROW the logic at the COLUMN level will no longer be evaluated as intended.

I believe we might be overlooking an alternative approach that could provide a simpler and more effective solution.

@Cummunity Experts please jump in and assist.

Thank you.

Hi ​@parveza33 were you able to find a solution? Thank you!