If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Wrong Grand Total % on Cross tab Dimentional Report

Started by amit_pjoshi, 09 May 2016 06:21:47 AM

Previous topic - Next topic

amit_pjoshi

Hi,

Can anyone please help on following issue on Cross tab report build on Cube (Not relational)?

We are trying to calculate percentage in Grand Total for following calculation, it give correct result  on row level data (e.g. 24% , 68% etc..) but on Grand total it shows wrong values (e.g. -1483.8765934808%) , we tried almost all the ways values but not helping:

Syntax for calculation:

((Sales Value/ neutral) - Cost) / (Sales Value/ neutral)

Following are the original calculation based on above syntax:

( (total (
(total ([Sales Value]  within set (periodsToDate ( [Sales].[Time].[Fiscal].[Year] , [SelectedMonth])))
/ total ([C_neutral] within set [Currency ID],[Selected Year]))
-
(total ( [Sales Cost - Base]  within set (periodsToDate ( [Sales].[Time].[Fiscal].[Year] , [SelectedMonth])) ))) )
/
total ( total ([Sales Value]  within set (periodsToDate ( [Sales].[Time].[Fiscal].[Year] , [SelectedMonth] )) ) /  [C_neutral] within set [Currency ID],[Selected Year]))

Note:- We have also tried using "aggregate" function instead of" total"

Thanks & Regards, Amit Joshi

Corrigon

Have you tried setting the Solve Order of the Summary Row to a value greater than 1? Not sure if this will assist you, but it has helped me resolve issues with summary rows giving weird output in the past.

The link below is to a thread which expands upon Solve Order and how it functions.

Good luck!

http://www.cognoise.com/index.php?topic=21762.0