3.1 Deski formula doesn't convert correctly to 4.1 Webi

[Former Member]

I converted a 3.1 Deski report to 4.1 Webi using the Report Conversion Tool. The primary calculation context is not working correctly in Webi and I can't seem to get it to work.

Deski formula:                   =Sum(<Net Total 2>ForEach <Line Item Number Char>)

Converted Webi formula:  =Sum([Net Total 2] ForEach ([Line Item Number Char]))

[Net Total 2] is the last column on the right (Net Total). [Line Item Number Char] is the second column on left (L.I.). The way this works in Deski is that it sums each L.I. amount, while disregarding duplicate L.I. amounts. So in the example below, it sums 280+702+350+358.40 = 2750.80. Note: This is similar to the functionality in Crystal reports where you can use a Running Total or variables to evaluate only when the L.I. changes. In Webi, it sums all amounts (including duplicates) for a total of 3380.80. I'm not sure why it's doing this since the calculation context is the same in both tools. I can't seem to get any calculation context or formula to replicate this. Any ideas?

[amitrathi239]

Hi,

use this.

=Sum(Max([Net Total 2]) ForEach([Line Item Number Char]))

Amit

[Former Member]

Thanks Amit - almost there now. The formula works when I add it to the report block as a column (Test Total) , but I get a MULTIVALUE error when I try to display it in a separate header ("Invoice Total:" outside the report block):

[amitrathi239]

Hi,

What is the datatype of this variable? also try to put the sum function for this.

Also if you do not want to show the duplicate records in the report and right click on table->Format table->General->uncheck the option "Avoid Duplicate row aggregation"

Amit

[Former Member]

The variable is a number datatype. I did do a Sum at the end of the block and that works fine (=Sum(Max([Net Total 2]) ForEach([Line Item Number Char]))). But that only works within the block. I need to display this Summed amount in a separate cell outside of the report block. When I use this formula in an independent cell, I get the MULTIVALUE error.

The display of duplicates is what we want - it's just getting the summary amount displayed in an independent cell that's not working, but it works in Deski

[amitrathi239]

Hi,

Try to include one by one all dimension which are used in the  block.

=Sum(Max([Net Total 2]) ForEach([Line Item Number Char];[Performed By]))

If it will not work then try add other dimension and see.

Amit

[Former Member]

I tried using all the dimensions, but still get same error message

[Former Member]

Hi William,

To troubleshoot it further, can you do below:

1. Drag an empty vertical table in another tab of report. (It will come as empty vertical column).

2. Put the formula inside this column and check what values are getting displayed.

3. Check "Avoid duplicate row aggregation" option in case you get #MULTIVALUE. (This option will work in this case as we are putting the formula in table and not in a cell).

Share the results so that we can decide on how we can avoid #MULTIVALUE.

Regards,

Yuvraj

[Former Member]

I followed the steps. The total is correct and repeats for all the records in the column.

[amitrathi239]

Hi,

Try to put max()  in your formula and see if multi value is coming or not.

Amit

[Former Member]

Hi,

Not sure I follow. The existing formula is: =Sum(Max([Net Total 2]) ForEach ([Line Item Number Char])). So are you saying to change this to =Max(Sum(Max([Net Total 2]) ForEach([Line Item Number Char]))). Also, are you saying to make this change on the original tab in the free standing cell?

Thank you for the help

[amitrathi239]

Hi,

in the blank cell use this =Max(Sum(Max([Net Total 2]) ForEach([Line Item Number Char]))).

Also what is happening if you drag this formula in the table column and uncheck the option "Avoid duplicate row aggregation" option.

Amit

[Former Member]

Using this in a blank cell =Max(Sum(Max([Net Total 2]) ForEach([Line Item Number Char]))) still produces the same error.

Adding the formula in the table column and uncheck the option "Avoid duplicate row aggregation" option produces the same error.

[Former Member]

Hi,

What if you try only this:

=Max( ([Net Total 2]) ForEach([Line Item Number Char]))

Or

=Max( ([Net Total 2]) ) ForEach([Line Item Number Char])

Regards,

Yuvraj

[Former Member]

Both formulas corrected the error, but don't produce the correct total. I have pasted below the analysis:

[Former Member]

Hi William,

Seems like one dimension is missing from calculation context, try below and check:

=Max( ([Net Total 2]) ForEach([Line Item Number Char];[Performed By]))

Regards,

Yuvraj

[Former Member]

Just try below formula:

=Sum([Net Total 2] in ([Line Item Number Char]))

[Former Member]

Yes, I tried that already. It still sums the duplicates.