We were recently investigating a problem for a client regarding the use of Scope within MDX calculated members. The code in question was similar to this:
CREATE MEMBER
CURRENTCUBE.[Measures].[Test Measure To Date]
AS "NA", VISIBLE = 1;
Scope([Date].[Calendar].MEMBERS);
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Calendar].CurrentMember,
[Measures].[Test Measure]);
End Scope;
Scope([Date].[Fiscal].MEMBERS);
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Fiscal].CurrentMember,
[Measures].[Test Measure]);
End Scope;
Essentially the warehouse was providing a transaction table with credits and debits, this calculated measure was supposed to provide the current balance, summing all transactions to date (not just the current year/period etc, but the entire history). Scope is used to enable the calculation to work across two different date hierarchies, calendar and fiscal.
The problem was that even when the [Date].[Calendar] hierarchy was selected, the code still used the fiscal hierarchy to calculate the value.
This is caused by the fact that [Date].[Fiscal].MEMBERS includes the member [Date].[Fiscal].[All]. Consequently, even when the Fiscal hierarchy was not included in the query, its [All] member was effectively still within the scope. Thus the fiscal calculation was overriding the calendar calculation no matter what was selected.
The solution to this is to exclude [All] from the scope, which can be done by changing the code to the following:
CREATE MEMBER
CURRENTCUBE.[Measures].[Test Measure To Date]
AS "NA", VISIBLE = 1;
Scope(DESCENDANTS([Date].[Calendar],,AFTER));
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Calendar].CurrentMember,
[Measures].[Test Measure]);
End Scope;
Scope(DESCENDANTS([Date].[Fiscal],,AFTER));
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Fiscal].CurrentMember,
[Measures].[Test Measure]);
End Scope;
DESCENDANTS(xxx,,AFTER) is a simple way of identifying every descendent of the hierarchy AFTER the current member, which is [All] when not specified.
Problem solved, Frog-blog out.
Hi,
ive used the above method in the following context:
– two time dimensions(A, B), one calculated measure
– for time dimension A, calc measure should display its value
– for time dimension B, calc measure should = NULL
– for time dimension A, the calc measure returns the correct values.
– for time dimension B, the descendant levels return NULL. the [All] level however returns the calc measure’s total.
How can I make this NULL also, without breaking the correct functionality as far as dimension A is concerned?
Many thanks,
Peter
Hi Peter
The problem you have is that if time dimension B is not selected as part of a query, it is essentially selecting the [All] level by default. So if you scope the calculation to be null when Time B is [all] then it will always result in null, no matter whether [all] is physically specified or not.
Does that make any sense?… Essentially you will have to allow the calculation to take place for [All], otherwise the calculation can never take place under any circumstances.
Alex
Hi,
this post was very helpful and it helped in resolving a similar issue in my project, and it has imporved the performance too. thank you very much for this.
I have a requirement where the grand total needs to be not affected by anyfilters applied in the WHERE clause. is this techinically feasible?
for e.g.
With member MEASURES.Test AS
SUM([Dispatch Country].[Country].Allmembers,Measures.[Value])
SELECT MEASURES.Test on 0 from [ots]
would give me the correct sum but when i filter it the value will only be the filtered one
With member MEASURES.Test AS
SUM([Dispatch Country].[Country].Allmembers,Measures.[Statistical Value])
SELECT MEASURES.Test on 0 from [ots]
where [Commodity].[Commodity Hierarchy].[HS4].&[Cheese];
in fact i am using the SCOPE stament in my calculated measure, i do calculations at each level to have the leaf level values, but the grand total should always include the true total without the filtered. any help much appreciated
thank you
rakesh
Hi Rakesh
If you’re filtering [Commodity].[Commodity Hierarchy] in the WHERE clause, you can make a calculated measure ignore this by specifying [Commodity].[Commodity Hierarchy].ALL in the measure calculation. The following does what you want inthe Adventure Works (2008R2) cube
With member [Measures].[Full Order Count] AS
([Product].[Product Categories].[All Products],[Measures].[Order Count])
SELECT {[Measures].[Full Order Count], [Measures].[Order Count]} on 0
FROM [Adventure Works]
WHERE [Product].[Product Categories].[Subcategory].&[1];
Even though the WHERE clause is filtering the Product Categories hierarchy, the Member expression is overriding this by specifying [Product].[Product Categories].[All Products].
Avoid using SUM(Hierarchy.AllMembers, Value) – you don’t need to use SUM, just use the single value of the ‘ALL’ member. It’s faster and more reliable as AllMembers will return the aggregated levels of the hierarchy as well as the leaf values, causing you to double count values.
Hi Alex,
thanks for the reply, my issue is we use a Pivot control from devexpress to browse the cube and it doesnt have options to send mdx query nd fetch the values. however i shall try adding a calculated measure like this – ([Product].[Product Categories].[All Products],[Measures].[Order Count]) and then use that measure, need to see how it could be integrated with the pivot though
also i was scope statements like this
Scope(DESCENDANTS([Date].[Fiscal],,AFTER));
[Measures].[Test Measure To Date] =
SUM(NULL:[Date].[Fiscal].CurrentMember,
[Measures].[Test Measure]);
End Scope;
there is a set of rule i need to apply on the leaf level for which the above query works fine, but i also need to have to apply a separate set of rule for calculation on the Total – ALL level if i add another SCOPE with Dimension.Members it will not work since all the leaf members too will apply the same rule. any suggestion on how this can be handled.
basically i want to have another scope that acts only on the ALL level, not on the leave Nodes
any help much appreciated
thanks
rakesh
I have tried adding the code (with small mod for my hierarchy defs) to the XMLA for my cube, but it always returns only the 2nd scope item correctly. If I change the order i.e. put [Year-Week_Day] as second block, the 2nd one is now OK while 1st is not. Here’s my modified code:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Subscribers Test] AS “NA”,VISIBLE = 1;
SCOPE (DESCENDANTS([Dim Date].[Year-Week-Day],,AFTER));
[Measures].[Subscribers Test] = SUM(NULL:[Dim Date].[Year-Week-Day].CurrentMember,
(IIF(ISEMPTY([Measures].[Net Gain]),0,[Measures].[Net Gain])));
END SCOPE;
SCOPE (DESCENDANTS([Dim Date].[Year-Qtr-Mth-Date],,AFTER));
[Measures].[Subscribers Test] = SUM(NULL:[Dim Date].[Year-Qtr-Mth-Date].CurrentMember,
(IIF(ISEMPTY([Measures].[Net Gain]),0,[Measures].[Net Gain])));
END SCOPE;
What am I doing wrong? Also, when I try and build it within the BIDS environment, the calculated measure will fail.
BTW, I will be a very happy camper if I can get this to work!
Hi Paul
Sorry for the delay in replying, only just back after Christmas holidays.
The syntax looks ok so it seems like both hierarchies are being selected in the query – do you have default members set for the hierarchies?
Alex
Hi Alex
How to show first month of any year to selected month while browsing a cube based on year hierarchy.
could you please help me on that
Thanks
I left blank – doesn’t that mean it will default to ‘ALL”? Or do I need explicitly set?
ALL is the default, so that’s not the problem. There must be something else interfering with it.
I’d need to take a more detailed look to investigate – I’ll happily investigate if you can get me a backup of the cube, drop me an email at Alex at purple frog systems dot com if you want me to take a look.
Alex
I am experiencing the same problem as Paul above.
Do you recall a solution to the problem?
Hi David,
I don’t think we ever investigated further. There are a large number of ways of approaching this, depending on the nature of the cube and the other calculations involved. If you want to send me a cut down sanitised cube, and your query then I’d happily take a look.
Alex
Alex,
I have a problem a little like Rakesh’s which I am struggling to overcome.
My cube contains a calculated measure (headcount) which is defined like this:
SCOPE([Measures].[Headcount]);
— By Fiscal Year hierarchy
SCOPE([Date].[Fiscal Date].MEMBERS, [Date].[Year].[Year].MEMBERS);
THIS = Sum(
null : [Date].[By Fiscal Year].CurrentMember,
[Measures].[Starters] – [Measures].[Leavers]
);
END SCOPE;
END SCOPE;
I have simplified slightly as it is aggregated over three different date hierarchies but that’s not relevant for this issue.
My problem is that we have a Boolean “Last 12 periods” attribute in the date dimension allowing users to create Excel pivot tables, etc. showing just the last 12 periods. Trouble is, if it is used as a filter, it appears to effectively create a subcube containing just the 12 periods and THEN applies the above scope – so the result is just the headcount movement over that time period, not the actual totals the user expects.
Any thoughts on how to overcome this?
Thanks.
Hi Nigel
The problem, as you said, is that the [Last 12 periods].[True] member is being applied to the calculated measure.
Therefore you just need to force the calculation to override this and use all dates. Something like this…
SCOPE([Measures].[Headcount]);
— By Fiscal Year hierarchy
SCOPE([Date].[Fiscal Date].MEMBERS, [Date].[Year].[Year].MEMBERS);
THIS = Sum(
null : [Date].[By Fiscal Year].CurrentMember,
([Measures].[Starters], [Date].[Last 12 periods].[All])
– ([Measures].[Leavers], [Date].[Last 12 periods].[All])
);
END SCOPE;
END SCOPE;
This changes the Starters and Leavers measures into tuples that forcibly scope the measure to all dates, within the last 12 periods and those outside it.
Alex
Alex,
That’s weird – I’m certain I tried just that the other day but without success. Your reply prompted me to have another go (I’ve also had a few days away from the problem which may have helped!) and that solution works fine.
Must have made an error before without realising it – thanks very much for your suggestion.
Regards,
Nigel
Ah, no, now I’ve realised the difference…
Trying this out quickly this morning, I used the VS cube browser rather than Excel. I applied the filtering in the Dimension/Hierarchy/Operator/Filter Expression section at the top of the browser which returned the expected result.
Having just tried it (unsuccessfully) in Excel, I returned to the cube browser and, after re-confirming the result as I tried it this morning (just to make sure I’m not going mad!), I then applied the filter in the “Drop Filter Fields Here” section instead. This must be the equivalent of Excel’s “FILTERS” section as, like the latter, it produces a result only considering the cut-down cube, so again returns the headcount movement, rather than running totals.
Presumably which way you apply the filtering affects the query generated by the browser which, in turn, affects the result 🙁
Hi Nigel
This is an interesting topic – and shows off some of the subtle differences between different ways of writing MDX.
If you wanted to filter by ‘z’ then there are many different ways of doing it, including:
SELECT x ON 0, y ON 1 FROM [cube] WHERE z
or
SELECT x ON 0, y ON 1 FROM (SELECT z ON 0 FROM [cube])
They look like they do the same thing, but there are some big differences. For example the first sets the context, therefore the cube knows the ‘currentmember’ is z. In the second this is not the case.
Different cube browsers will generate different styles of MDX, which can materialise in different results. Just one of the many things we have to deal with,
Alex,
Very true – in this case the users are using Excel pivot tables to access the cube data so we are constrained by the way it decides to generate its MDX.
In fact, I now have a solution to this (with a lot of help from Chris Webb) which is described here:
http://social.msdn.microsoft.com/Forums/en-US/d2e374de-97c0-48a5-8e4a-89d86996f40e/measure-aggregated-from-day-one-when-filtered-by-time-dimension-attribute-doesnt-work?forum=sqlanalysisservices
Cheers,
Nigel
Great, thanks for the update. Enjoy the MDX course 🙂
I have a requirement to get the calculated member change the results based on the filter. But by doing the below the total is adding the Instances to all the products and environments and not the products and environments that I filter. What should I do. I only want the Calculated member to add the instances for the products and environemtns I filter and should not add everything in the table.
CREATE MEMBER CURRENTCUBE.[Measures].[Totals]
AS IIF(ISEMPTY([Measures].[Instances]),null,
([DimProduct].[Product].[ALL],[DimEnvironment].[Environment].[ALL],[Measures].[Instances])),
NON_EMPTY_BEHAVIOR = { [Instances] },
VISIBLE = 1 ;
eg:
FACT TABLE :
PRODUCT ENVIRONMENT Instances
A X 50
A Y 50
B X 50
B Y 50
C X 50
C Y 50
D X 50
D Y 50
filter
products :A,B,C
Environemtns : X,Y
Product Instances Totals
A 100 400
B 100 400
C 100 400
the Product D, which is not in the filter has 100 instances.
Expected Result
Product Instances Totals
A 100 300
B 100 300
C 100 300
The mail goal for this is to find the percentage of each product from the total selected. So I wasn thinking of adding another column later and scope it to divide Instances/Totals.
Please help.
Hi Raghav,
You should be able to get what you need by restructuring your MDX.
Put your products and environments filter into a subselect, which will limit the scope of the calculation.
SELECT xxxxx
FROM (SELECT {productA, productB, productC} ON 0 FROM (SELECT {EnvironmentX, EnvironmentY} ON 0 FROM [cubename]))
Or something similar. Your total calculations should then execute on the subcube, not the whole cube.
Alex
Good Morning Alex.
It is second time i read your wonderful blog.
I have a question…
The scenario of this calculation , is in a query MDX but how can write that in a cube design ???
Do i need to insert SCOPE Statement in Script View ???
Do i need to define measure [Measures].[Test Measure To Date] as a real measure in measure group and then assign a value with SCOPE in Script View or can i define a calculated measure in TAB Calculation ?
Sorry for silly question..
I think this post it is very good to solve problems with calculated measure and GRAND TOTALS wrong in Client Application as Excel..
Regards and sorry for easy question.
Yes you add the scope statement to the Calculations tab to include it in the cube.
You can create the initial measure in two ways:
1) Create a real measure in a measure group, sourced from the DSV
2) Use ‘CREATE MEMBER’ in the Calculations script to create a new measure
Either way, you can then apply scope to override the value of the measure in different situations.
So in my examples above, there are actually two stages:
CREATE MEMBER – creates a new measure which doesn’t already exist
SCOPE – override the new measure with a different value.
If you already had the measure defined in a measure group, then you would only need the 2nd part; the scope statement.
And there’s no such thing as a silly/simple question. The only thing that would be silly would be not asking a question. If you don’t understand something, then there will certainly be other people who also don’t understand, so thank you for helping them 🙂
Mauro,
This is another area where there are subtle differences between two approaches.
While the values produced using a real member and one created in the Calculations script will be the same, bear in mind if you will need to control access to the measure through roles’ “Dimension Data” tab settings that only the real measures will appear – those created in the Calculations script will be available to all users.
If needs be, create a real measure in the DSV (with a Null value) then override it in Calculations with a scope statement.
Hi Alex.
Many thanks for your clear explanation….
I think your blog is very useful to improve MDX and SSAS calculations.
Thanks again…
Regards.
Hi Alex.
Many thanks.
I think this reply is very useful to understand to solve ‘huge’ problem of wrong grand total for a calculated measures…
Thanks agains.
PS. I find this blog very useful indeed.
Hi Alex,
As usual excellent post!! I’ve a question related to Scope and Year calculation in my cube and would be really helpful to get your suggestions.
I’ve the DateTool Implemented in my Cube and enhancing the same I’ve created another member called “Outlook” (forecast) which has values 1+11, 2+10……11+1. For instance, when user selects 9+3, the measure should display 9 (Jan to Sep) month’s actual value and 3 (Oct to Dec) month’s outlook (forecast) value. To identify the source, I’ve Source Profile dimension which has Actual and Outlook values (1+11, 2+10……11+1). I managed to bring the Actual if Outlook (forecast) is not available. But it is showing for entire Calendar Years (2013, 14 & 15 whereas I’ve outlook only for 2014 year) 🙁 I want to show the actual only if Outlook (Forecast) is available for that year or else Null.
I’m stuck with this for the past few days and would be great help if you could point me to the right direction.
Thanks a lot for your help!
SCOPE ([DateTool].[Outlook].[9 + 3]);
THIS = IIF( IsEmpty( Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Source Name].&[9 + 3]}))
,Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Profile Level01].&[Actual]})
,Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Source Name].&[9 + 3]}));
FORE_COLOR(THIS) = IIF( IsEmpty(Aggregate({ [DateTool].[Outlook].[9 + 3]} * { [Source Profile].[Source Name].&[2 + 10]}) ), RGB(0,0,192), 0);
END SCOPE;
Hi Alex – great post. I did try DESCENDANTS(xxx,,AFTER) and works for a part of my solution. Here’s my issue, I have a Time hierarchy (Year-Quarter-Month-Date) and also a “current date” attribute (which is not part of the time hierarchy) in the time dimension. I am looking to create a calculated member called MTD Sales where i should have the option to select Current date and gross Sales or Date from the time hierarchy and gross Sales. This way the user has just one Measure field but has the flexibility to choose either the current date and get MTD Sales as of current date or any prior date from the time hierarchy so the MTD sales is as of that date. I have a scope statement and when i choose the date from the time hierarchy it works just fine. however when i try current date null value is returned.
CREATE MEMBER CURRENTCUBE.[Measures].[TestMTDGrossSales]
AS NULL,
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = ‘Fact Transaction View’ ;
SCOPE(Measures.[TestMTDGrossSales]);
SCOPE(DESCENDANTS([Time Dimension].[Time Hierarchy],,AFTER));
THIS=sum(periodstodate([Time Dimension].[Time Hierarchy].[Month],[Time Dimension].[Time Hierarchy].currentmember),[Measures].[Gross Sales Amount]);
END SCOPE;
SCOPE(DESCENDANTS([Time Dimension].[CURRENT DT],,AFTER));
THIS =Aggregate (MTD([Time Dimension].[CURRENT DT].currentmember),[Measures].[Gross Sales Amount]);
END SCOPE;
END SCOPE;
Any input is greatly appreciated.
Hi Alex,
Great posts! One question related to this post, I faced a similar case which use scope to compute the cumulated metrics. May I know how you handle this cumulated [Test Measure To Date] on ALL level finally ? In your last example which exclude the [ALL], then this metric will be always null on [Date].[Calender].[All]/[Date].[Fiscal].[All] which is un-expected for reports users right?
Thanks,
Tom
Hi, first sorry for my English. I hope you can help me. I have a cube with a dimensión (Parámetro) which have two members (Agua) and (Aire). I have written the next MDX sentence:
Scope([MP – PARÁMETRO].[Agua – Aire].[Agua],[Measures].[Num. Muestreos]);
this=iif([MP – PARÁMETRO].[Agua – Aire].[Agua].currentmember Is [MP – PARÁMETRO].[Agua – Aire].[Agua].[Total],null,[Measures].[Num. Muestreos]);
end scope;
I want that if I browse Agua, the result will be NULL but if I browse Agua and Aire like a hierarchy, I need Num Muestreos.
If I do this in rows and columns, I have the result but if I do this only in rows like a hierarchy, the result is null because the .currentmember is active.
Hope you’ll help me.
Thanks!!
Try using
Scope([MP – PARÁMETRO].[Agua – Aire].[All],[Measures].[Num. Muestreos]);
this=null;
end scope;
This just says that if you are browsing the All member then show null instead. The scope will not trigger when browsing the dimension members in a hierarchy, so will leave them unchanged.
Regards
Alex
Hi Alex,
I try this sentence but I have the same problema. If I browse [MP – PARÁMETRO].[Parámetro] on rows and [MP – PARÁMETRO].[Agua – Aire] on columns, I have the correct result but if I put both on rows, the result crashes.
Im trying to upload a photo with the problema but it’s impossible.
Have you understand me?
Thanks!
Regards!
Please help. I have inherited a cube with scope calculation and need add a new dimension but it does not work for me. Here is a the structure.
1. I have a dim table that is generated by this code:
SELECT 9 AS DateFilterKey, ‘Rolling 12 Month’ AS DateFilterName, 9 AS SortOrder
2. The table is added to the cube as a dim table but there is not a like to the fact table.
3. Within the the Calculation, there is this scope statement
SCOPE([Date Range].[Date Range].&[9]); // Rolling 12 Month
SCOPE(EXCEPT([GL Date].[Month].[Month], STRTOSET(‘{[GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(12) : [GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(1)}’)));
THIS = NULL;
END SCOPE;
END SCOPE;
I thought I can add this script to the table generating script
Union ALL
SELECT 10 AS DateFilterKey, ‘Rolling 18 Month’ AS DateFilterName, 10 AS SortOrder
and add a new scope statement just updating the month range
SCOPE([Date Range].[Date Range].&[10]); // Rolling 18 Month
SCOPE(EXCEPT([GL Date].[Month].[Month], STRTOSET(‘{[GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(18) : [GL Date].[Month].&[‘ + VBA!CStr(VBA!Year(VBA!Now())) + ‘]&[‘ + VBA!CStr(VBA!Month(VBA!Now())) + ‘].lag(1)}’)));
THIS = NULL;
END SCOPE;
END SCOPE;
I can process the dim table by itself successfully, but when I process the whole cube, I get the error:
The dimension ‘[Date Range]’ was not found in the cube.
I am new to SSAS. Have I missed a link some where? Thank you in advance
Hi Thanh
It looks like the original should also generate the same error, can you confirm whether the original code processes ok?
2. The table is added to the cube as a dim table but there is not a link to the fact table.
I have a hierarchy Establishments in the warehouse location dimension
(Establishment/Warehouse/Warehouse Location).
I want to exclude the lowest level for a calculated measure and use a SCOPE statement
CREATE MEMBER CURRENTCUBE.[Measures].[Occupied Locations] AS NULL,
VISIBLE = 1 , DISPLAY_FOLDER = ‘Location Occupancy’ ;
SCOPE(Descendants([Warehouse Location].[Establishments].[Establishment]
, [Warehouse Location].[Establishments].[Warehouse Location]
, BEFORE));
[Measures].[Occupied Locations] = FILTER([Warehouse Location].[Warehouse Location].Members
,[Measures].[Occupied YN]=1).count;
END SCOPE;
But when I deploy the script to the database I get an error ‘The END SCOPE statement does not match the opening SCOPE statement. An arbitrary shape of the sets is not allowed in the current context..
When I replace the Descendants function with e.g. [Warehouse Location].[Warehouse Location].Members , it works fine.
What am I doing wrong ?
I’m so happy that I’ve stumbled upon your blog. I have a better understanding of my issue now, and was hopeful that your solution would help resolve my issue. However, I’m still experiencing the same issue. That is, the second calculation is the only one that works, so depending on how I order them, I can get one or the other to function, but not both.
I am attempting to get the prior period value to use in a downstream calculation.
The measure I am creating:
CREATE MEMBER CURRENTCUBE.[Measures].[Net Actuals Prior Period]
AS NULL,
VISIBLE = 1,
DISPLAY_FOLDER = ‘Net Actuals’,
ASSOCIATED_MEASURE_GROUP = ‘Actuals’;
And the actual calculation:
// Net Actuals Prior Period Calculation
SCOPE( [Measures].[Net Actuals Prior Period] );
SCOPE(DESCENDANTS([DateTime].[Weekly],,AFTER));
THIS = ([DateTime].[Weekly].CurrentMember.PrevMember,
[Measures].[Net Actuals]);
END SCOPE;
SCOPE(DESCENDANTS([DateTime].[Fiscal Year],,AFTER));
THIS = ([DateTime].[Fiscal Year].CurrentMember.PrevMember,
[Measures].[Net Actuals]);
END SCOPE;
END SCOPE;
If I add the “NULL:” to my calculation, I get nothing but #:Value errors.
any insight would be greatly appreciated.
Hi Alex,
I have added 3 new attributes to the dimension in my SASS cube. When ever i am using my calculated measure with these new attributes in excel, it is eating up the server ram and throwing a memory pressure error. I have increased the RAM of my system from 8 GB to 32 GB but didnt get any luck. The calculated measure is having the below definition.
CREATE MEMBER CURRENTCUBE.[Measures].[# Devices]
AS ([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[IRS Eligible])
+
([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[IRS Eligible (no collections)])
+
([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[STaTS Eligible])
+
([Measures].[# Units], [FOR MEASURECAL].[RST Eligible].&[Excluded Device]),
VISIBLE = 1 ;
is there any issue with design of my calculation(it was working fine earlier with other attributes).
There are a couple of things to look at here:
– The most likely problem is an error in the script. If so it will likely try and calculate for every member in the cube and not make use of non empty functionality/performance. To test this just comment out most of the calculation and replace with a single value and check if it works, then gradually expand the calculation until you hit the problem.
– It could be a corruption in the cube, try redeploying and reprocessing.
– Do you have any aggregations using the FOR MEASURECAL dimension? If not try and add some. Although this would impact disk IO more than ram.
I’m also assuming # Units is a basic sum measure and not a calculation? If it’s a calculation then you’d need to include its functionality into the complexity assessment.
Hi Alex,
I have the below statement.
CREATE MEMBER CURRENTCUBE.[MEASURES].[Test Net Sales]
AS [MEASURES].[Net Sales]
, FORMAT_STRING = “#,#;(#,#)”, VISIBLE = 1;
SCOPE [Measures].[Test Net Sales];
SCOPE([Date].[Retail Week].&[FY15 Wk 4]);
this=NULL;
END SCOPE;
END SCOPE;
When I run a SELECT like
SELECT [Measures].[Test Net Sales] ON 0,
[Date].[Retail Hierarchy].[Retail Week].Members ON 1
FROM Cube
The output is supposed to show all the members of [Date].[Retail Hierarchy].[Retail Week] and [Test Net sales] measure except for the member [FY15 Wk 4] (It supposed to show NULL).
Example:
FY15 Wk 1 231315
FY15 Wk 2 548645
FY15 Wk 3 343434
FY15 Wk 4 (null)
FY15 Wk 5 784256
But its not working as expected. In my DEV environment I have 2 AS DBs one with full implementation and a test cube with less number of dimensions and measures but both the cube has the same Date dimension with the same hierarchies and everything. Strangely the above SELECT returns the expected result in test cube but not in the actual cube.
Given that both has the same DATE dimension, how could this give different results.
Thanks
HI Alex,
I Need your help please!
In my cube browser, I am showing the below results:
From Date To Date Amount Sum Amount per From Date – Needed one
2018-05-01 2018-05-02 100 1372
2018-05-01 2018-05-04 63 1372
2018-05-01 2018-05-05 45 1372
2018-05-01 2018-05-10 412 1372
2018-05-01 2018-05-17 752 1372
2018-05-06 2018-05-11 400 2770
2018-05-06 2018-05-15 752 2770
2018-05-06 2018-05-16 4 2770
2018-05-06 2018-05-20 754 2770
2018-05-06 2018-05-21 12 2770
2018-05-06 2018-05-26 782 2770
2018-05-06 2018-05-27 66 2770
How to create a calculated member to show the column ‘’Sum Amount per From Date’’?
Thanks a lot!
You just need to override the ‘To Date’ with all members, instead of the current selection. Assuming that ‘To Date’ is the name of your dimension and attribute, then try something like this:
CREATE MEMBER CURRENTCUBE.[Measures].[Amount per From Date]
AS ([Measures].[Amount], [To Date].[To Date].[ALL]);
Alex
Now i have all [To Date] dimension values (not aggregated) & [Amount per From Date] with null values
CREATE MEMBER CURRENTCUBE.[Measures].[Sum Mt Annoncé]
AS ([Measures].[Mt Annoncé]
,[Date Exploitation].[Date].[ALL]),
VISIBLE = 1 ;
Karim
I would need to see more details to understand what you’re doing. What MDX query are you running?
Below the MDX script, Thanks…
SELECT NON EMPTY { [Measures].[Mt Annoncé], [Measures].[Sum Mt Annoncé] } ON COLUMNS, NON EMPTY { ([Date Ramassage].[Date].[Date].ALLMEMBERS * [Date Exploitation].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Type Mouvement].[Type Mouvement].&[1] } ) ON COLUMNS FROM ( SELECT ( { [Date Ramassage].[Date Code].&[2018-05-01T00:00:00], [Date Ramassage].[Date Code].&[2018-05-06T00:00:00] } ) ON COLUMNS FROM [Comptage])) WHERE ( [Date Ramassage].[Date Code].CurrentMember, [Type Mouvement].[Type Mouvement].&[1] ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
Hi Alex,
No chance for the below example?
[From Date] [To Date] [Amount] [Sum Amount per From Date] – Needed one
2018-05-01 2018-05-02 100 1372
2018-05-01 2018-05-04 63 1372
2018-05-01 2018-05-05 45 1372
2018-05-01 2018-05-10 412 1372
2018-05-01 2018-05-17 752 1372
2018-05-06 2018-05-11 400 2770
2018-05-06 2018-05-15 752 2770
2018-05-06 2018-05-16 4 2770
2018-05-06 2018-05-20 754 2770
2018-05-06 2018-05-21 12 2770
2018-05-06 2018-05-26 782 2770
2018-05-06 2018-05-27 66 2770
I don’t understand what results you’re saying that you’re getting, but if you can send me a backup of the processed cube (via WeTransfer or similar) then I’ll take a closer look.
Hi Alex!
I’d be very much obliged if you could help me with this
I have a cube with for dimentions (lets call them A,B,C,D). For one of the calculated measure(let it be X) I need to use non-standard total for all attributes of all for dimentions, that would use the same non-standard totaling of the measure X
Because this measure has a formula that refer to other calculated measures, that use both summing and multiplication and should be calculated before aggregating (lets for simplicity be X = CM1 -CM2)
I created a special virtual temporary measure in the data source view (let’s call it M1) and initialized it with NULL so that to use it in SCOPE assignment. To force all attributes of a given dimention to use the SCOPE assignment I use the key attribute of the dimention, Berlow is the example of MDX that work well for all attributes of dimention A.
I first assign the formula to vurtual measure M1, and after use the value of M1 for getting non-standard totalling
SCOPE([Measures].[X],[A].[A].[All]);
SCOPE([Measures].[M1],[A].[A].[AKey].MEMBERS);
THIS = [Measures].CM1 – [Measures].CM2;
END SCOPE;
THIS = [Measures].[M1];
END SCOPE;
I need to do the same for others dimentions B,C,D (i.e. apply the same formula [Measures].CM1 – [Measures].CM2)
But when I try just to copy-paste the scope above for other 3 dimentions (i.e. just replace A with B, then C, then D)
I do not get the result I expect, because in that case somehow later scopes overiide the value of the 1st scope assignment
even if i use different virtual measures (i.e. not just M1, but M2, M3, M4)
I do not “feel” how the scope works in this particul;ar case
Thank you in advance
Pavel