Author Topic: Unable to get total of a calculated commission payable column in crosstab report  (Read 316 times)

Offline cognovice

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 32
  • Forum Citizenship: +0/-0
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

Offline hespora

  • Statesman
  • ******
  • Join Date: Nov 2015
  • Posts: 383
  • Forum Citizenship: +22/-0
As best I can tell that *should* work. You might want to try to amend your definition to
Code: [Select]
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.

Offline cognovice

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 32
  • Forum Citizenship: +0/-0
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

Offline cognovice

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 32
  • Forum Citizenship: +0/-0
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.

« Last Edit: 21 Feb 2019 11:19:00 pm by cognovice »

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,822
  • Forum Citizenship: +262/-1
    • Paul's Cognos Blog
is this relational or dimensional?

Offline cognovice

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 32
  • Forum Citizenship: +0/-0
Relational

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,822
  • Forum Citizenship: +262/-1
    • Paul's Cognos Blog
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?

 



       
Twittear