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.
| Author |
Topic |
|
QAZAFI
Yak Posting Veteran
50 Posts |
 Posted - 2010-03-06 : 08:26:22
|
| Hi ThereI have a stored fact called [YTD Actual Balance] which is equal to Year to date values of another stored Fact [Actual balance]. The problem with [YTD Actual Balance] is when I put a time dimension the total value of year has shown the sum of all periods but in reallity it should be equal to the last period(12) because we are calculating YTD values. I have tried to use scope statement to set the value of year as the value of Period 12 using following statementScope([Dimension FINANCIAL PERIODS].[Years-Month Structure].[Financial Year] .Members,[Measures].[YTD Actual Balance] ); This = ([Dimension FINANCIAL PERIODS].[Years-Month Structure].[Financial Period].&[12]); End Scope;But it always show #VALUE! For year now. But if I changed this scope statement with the following Scope([Dimension FINANCIAL PERIODS].[Years-Month Structure].[Financial Year] .Members,[Measures].[YTD Actual Balance] ); This = ([Dimension FINANCIAL PERIODS].[Years-Month Structure].[Financial Period].&[2008]&[12]); End Scope;It set all the years value with the value of 2008 period 12. Can you please let me know what mistake I am doing in scope statement? I will really appreciate that.I will look forward to hear from you soon Many thanksSyed |
|
|
QAZAFI
Yak Posting Veteran
50 Posts |
Posted - 2010-03-07 : 20:06:22
|
| Hi There I have found a way but not sure whether this is the best way to do. This is what I didScope([Dimension FINANCIAL PERIODS].[Years-Month Structure].[Financial Year].Members,[Measures].[YTD Actual Balance]); This = ([Dimension FINANCIAL PERIODS].[Years-Month Structure].CurrentMember.LastChild);End Scope;Please advice me if you think there is any other way which is better then this.Many thanksSyed |
 |
|
|
|
|
|
Scope Statement