Unable to get total of a calculated commission payable column in crosstab report

[cognovice]

Hi All,
I have created a crosstab report with a data item called comm payable.  This particular column is a calculated column ([Value MTD] * [Comm %] / 100).  I have also another column called as Total Comm Payable.  This has to sum up all the comm payable for each month and add them up together and total for the year.  Data item calculation is total([Comm Payable]).  When I run the report, my comm payable column is calculating the correct commission amount for each month, but when it comes to Total comm Payable, it is not doing it correctly for some of them.  It is very inconsistent. 

I have attached the design of the report and also the output highlighting the incorrect total.  could you please assist me where I am going wrong.

Regards,
R

[hespora]

As best I can tell that *should* work. You might want to try to amend your definition to

Code Select Expand


total(
  [Comm Payable]
  for [Invoice Rep Code], [Year]
)
Sometimes, it helps telling an aggregate function at exactly which levels it should do partial sums, even though that should be implicit by the crosstab design.

[cognovice]

Thank you for your response.

I created the data item as per your suggestion and dropped it next to comm payable column at period level.  When I generate the report output, this gives me one single same total for all the rows.  Am I missing something?

Regards,
R

[cognovice]

Hi All,
I can see that a lot of people have viewed my query but had only one response which didn't work.  Isn't there any solution to my query.

[CognosPaul]

is this relational or dimensional?

[cognovice]

Relational

[CognosPaul]

The problem might be coming from inconsistent aggregation usage.

In general it's a good idea to either use inline aggregation functions or using the aggregate type. Mixing between the two can lead to unexpected results, especially like this. Instead of adding total([Comm Payable]), what happens if you put [Comm Payable] under the node?