June 14, 2016 at 9:05 am
I need to create an MDX query returning allmembers from a hierarchy as I need the value for the All level of the Hierarchy. When I do this with the ALLMEMBERS function (null) is returned as the member name rather than All. Is it possible for me to set something in the cube to return a label for the All member used in this way.
You can see an example query below which queries the AdventureWorks cube.
SELECT NON EMPTY { [Measures].[Reseller Sales Count], [Measures].[Reseller Sales-Sales Amount] } ON COLUMNS, NON EMPTY { ([Reseller].[Business Type].Allmembers) } ON ROWS FROM [Analysis Services Tutorial]
What I want to see is 'All' rather than (null)
June 14, 2016 at 4:34 pm
You could use the "AllMemberName" property of the hierarchy. See https://msdn.microsoft.com/en-us/library/ms174497.aspx
June 15, 2016 at 2:37 am
Hi,
This doesn't solve the problem. It only seems to change the label of the dimension when browsing the dimension. Writing an MDX query against the attribute hierarchy with ALLMEMBERS returns (null) in the query results (in SSMS/SSDT/Excel) regardless of AttributeAllMemberName.
I need member name present as the user needs to slice on it. I can't use Excel pivot aggregation as the measure is a distinct count hence need to calculate it in SSAS.
Not sure if this is a bug in SSAS or related to the fact ALL is not stored in the dimension.
Only thing I can think of is to write VBA in Excel to replace NULL with ALL. But this is not ideal.
June 16, 2016 at 1:54 am
This problem went away. Power Pivot solved the problem with a DAX calculation.
June 16, 2016 at 8:35 am
leehbi (6/16/2016)
This problem went away. Power Pivot solved the problem with a DAX calculation.
Interesting...might have been an Excel nuance?
June 17, 2016 at 3:16 am
It was not an Excel thing - saw the same results with SSMS. I'm pretty sure it's related to the fact that ALL isn't a member name in strictest of sense.
You can see this issue for yourself with Adventure Works.
June 17, 2016 at 7:56 am
leehbi (6/17/2016)
It was not an Excel thing - saw the same results with SSMS. I'm pretty sure it's related to the fact that ALL isn't a member name in strictest of sense.You can see this issue for yourself with Adventure Works.
I've run this query against both the multimensional and tabular versions of the adventureworks samples and cannot reproduce the issue (the defined All Member name shows up each time). Strange.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply