SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 wrong grand total in SSAS cube
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jas2005
Starting Member

Malaysia
11 Posts

Posted - 08/01/2008 :  02:06:21  Show Profile  Reply with Quote
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

Malaysia
11 Posts

Posted - 08/03/2008 :  22:06:04  Show Profile  Reply with Quote
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

Australia
260 Posts

Posted - 08/04/2008 :  03:18:25  Show Profile  Reply with Quote
Try changing it to sum(amt * rate)
Go to Top of Page

jas2005
Starting Member

Malaysia
11 Posts

Posted - 08/05/2008 :  02:03:43  Show Profile  Reply with Quote
I can't ,,cause AMT & Rate is identical calc. members.

~j~A~s
Go to Top of Page

urchandru
Starting Member

1 Posts

Posted - 02/06/2009 :  05:11:28  Show Profile  Send urchandru a Yahoo! Message  Reply with Quote
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

Sweden
1 Posts

Posted - 11/21/2010 :  18:23:32  Show Profile  Reply with Quote
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 Posts

Posted - 12/29/2011 :  06:20:29  Show Profile  Reply with Quote
Working...

thank you !!!
Go to Top of Page

jvieira4
Starting Member

1 Posts

Posted - 11/20/2013 :  11:14:00  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000