Help replicating Excel report (I'm getting different results :( )

[qvixote]

Hello!

First of all, I want to tell you that I work on Chilean Ministry of Health, so if you help me, you are helping to improve the health and life quality of people in Chile.  ;D

I was asked to reproduce an excel report to cognos (see the first attached image). The table shows summary results for many kind of health benefits (rows) per year (columns). There's also a column with a weighting factor (PONDERADOR). All those values come from a Transformer Power Cube.

At the bottom of the table there's a calculation called INDICADOR AGREGADO DE ACTIVIDAD, which is an indicator of accomplishment (percent) and in the first attached image it's highlighted for 2009. The excel file calculates this indicator using the weighting factor and 2013 as base. The formula is:

Code Select Expand

=(SUMPRODUCT($C7:$C30;D7:D30)/SUMPRODUCT($C7:$C30;$H7:$H30))*100

It means that first it multiplies 2009 value per weighting factor, sum those products, multiplies 2013 per weighting factor and sum those products too, then divide the first sum of products by the second one, and finally multiplies per 100 to show result as a percent.

To accomplish this calculation, I did the following calculates measures in Report Studio query:
POND: [PONDERADOR] * [VALOR] (first measure is the weighting factor and second is the value)
BASE_PONDERADA: [PONDERADOR]*[2013] (first is the weighting factor and second a member of the corresponding hierarchy level)
IAAC: [POND] / [BASE_PONDERADA]

Then I made a crosstable (see second attached image) but the result is different than excel for the same values (see the third attached image).  :(

I tried changing the Resolution Order property, but it doesn't change anything.

Is that a correct way to try to emulate the excel function?

[qvixote]

Second image

[qvixote]

Third image.

[BigChris]

Basic question, but which one is right? Looking at the data in your attachments, the Cognos report looks right, but it's hard to judge from your excel snapshot.

[qvixote]

Thanks, BigChris!

Excel report is the original, it's been made historically by someone else, and Cognos report needs to show the same results shown in excel, in this case, 85.9% is the spected result for 2009, but Cognos report is showing 88.9%.

I need to replicate the Excel SUMPRODUCT behavior.

[BigChris]

What I'm getting at is, have you verified that 85.9% is correct? going by the figures, it looks to me like 88.9% is the right value...

[qvixote]

Yes, 85.9% is correct.

I've made a table (attached) and figured out that the problem is the order in wich Report Studio is calculating things. At the bottom of the table is a row called resume. In the manually calculated columns (in fact, excel calculated) the number is the result of a sum function. In the others columns (highlighted in red) the result -calculated by RS- clearly is the product between weighting factor's sum and value's sum, and the same with 2013-base. I think this is the spected behavior on most cases.

In my case, to obtain the desired result, I need RS to sum upper rows instead of multiplying per weighting factor. I can't imagine how to reach this, because those calculations shown in the attached table are not present in the final table, but in calculated measures.