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

Offline cognovice

  • Full Member
  • ***
  • Join Date: Sep 2015
  • Posts: 30
  • 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: 30
  • 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

 


       
Twittear