Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 wrong grand total in SSAS cube

Author  Topic 

jas2005
Starting Member

11 Posts

Posted - 2008-08-01 : 02:06:21
Dear All,
i have cube design which have a field called "Total" with formula (Amt* Rate) whereby 'Amt' is measurement member, 'Rate' is a calculated member as shown below

[Product ] [Total ] [Amt ] [Rate ]
A001 100 50 2
B001 150 300 0.5
C001 200 200 1
Grand Total: 1925 <--- wrong value, it should be 450
Because the grand total in cube browser calculated as 550 * 3.5 ( sum(amt)*sum(rate))

Is there any way i can rectify this error ?
Pls advice.

Thanks in advace
rgds
Jas

jas2005
Starting Member

11 Posts

Posted - 2008-08-03 : 22:06:04
Dear All,
sorry i have made a mistake statement on Amt & Rate field. Basically both the fields are Caculated members field in the cude. And, both of them are dynamics depend on the 'Fiscal Period' selected in the user selection field.

Pls help and advice ! Thanks

rgds
jas

~j~A~s
Go to Top of Page

dexter.knudson
Constraint Violating Yak Guru

260 Posts

Posted - 2008-08-04 : 03:18:25
Try changing it to sum(amt * rate)
Go to Top of Page

jas2005
Starting Member

11 Posts

Posted - 2008-08-05 : 02:03:43
I can't ,,cause AMT & Rate is identical calc. members.

~j~A~s
Go to Top of Page

urchandru
Starting Member

1 Post

Posted - 2009-02-06 : 05:11:28
I found d answer .,
try this,


CREATE MEMBER CURRENTCUBE.[MEASURES].[Fixed Value]
AS [Measures].[Fixed Cost Rate]*[Measures].[Actual Production],
FORMAT_STRING = "Standard",
VISIBLE = 1 ;

Scope ([Dim Machine].[Machine Name].[All],[Measures].[Fixed Value]) ;
This = Sum([Dim Machine].[Machine Name].[All].children) ;
End Scope ;


With Love
d one and only
Chandru
Go to Top of Page

tolisset
Starting Member

1 Post

Posted - 2010-11-21 : 18:23:32
Hi!

Thanks for the MDX, helped me a lot. I just wondering if it is possible to make it works when filtering.

Example:
Time Call
A1 1 * 2 = 1
A2 1 * 3 = 3
A3 1 * 2 = 2
Total = 1 + 3 + 2 = 6

When filtering A1 and A2, it shows 6 as the total instead of 4.
I tried updating the THIS to:

This = Sum([Dim Product].[English Description].CurrentMember.children) ;

But it showed me still 6.

It is possible to update the THIS somehow to update the grand total with filters?

Thanks a lot!
Go to Top of Page

rani109
Starting Member

1 Post

Posted - 2011-12-29 : 06:20:29
Working...

thank you !!!
Go to Top of Page

jvieira4
Starting Member

1 Post

Posted - 2013-11-20 : 11:14:00
Hi tolisset, did you find a solution??? I have exactly same problem here!! Thanks

quote:
Originally posted by tolisset

Hi!

Thanks for the MDX, helped me a lot. I just wondering if it is possible to make it works when filtering.

Example:
Time Call
A1 1 * 2 = 1
A2 1 * 3 = 3
A3 1 * 2 = 2
Total = 1 + 3 + 2 = 6

When filtering A1 and A2, it shows 6 as the total instead of 4.
I tried updating the THIS to:

This = Sum([Dim Product].[English Description].CurrentMember.children) ;

But it showed me still 6.

It is possible to update the THIS somehow to update the grand total with filters?

Thanks a lot!

Go to Top of Page
   

- Advertisement -