how to include MEMBER_UNIQUE_NAME in excel pivot table reports?

Question

i need to include the MEMBER_UNIQUE_NAME in my pivot table reports in excel.

i have a user defined hierarchy in the "row levels" area of my pivot table.  i created a calculated measure that is set to [My].[Hierarchy].CurrentMember.MEMBER_UNIQUE_NAME.  when i add this to the values area of my pivot table in excel, it works perfectly, showing the member unique name correctly at each level of the expanded hierarchy.

however, in some cases, i need to put another hierarchy in the column labels area of the pivot table with a single measure in the values area.  typically this is a dates hierarchy, and the measure is broken out across dates in the report.  but when i add my member unique name measure, it is displayed for every member of the hierarchy in the column labels area.

any ideas how i can get the member unique name to be displayed in a single column in excel, for all levels of the hierarchy in the row labels area, when i have a hierarchy in the column labels area of the pivot table?

Answer 1

You'll have to create an asymmetic set on your column axis. Using Excel 2010, it's possible but with previous versions of Excel, not so much. An MDX statement would look something like the following.

WITH MEMBER [Measures].[Geography Unique Name] AS
	[Geography].[Geography].CurrentMember.MEMBER_KEY
SELECT	Union (
		CROSSJOIN(
			{ [Measures].[Geography Unique Name] },
			{ [Date].[Calendar].[All Periods] }
		),
		CROSSJOIN(
			{ [Measures].[Reseller Sales Amount] },
			{ [Date].[Calendar].[Calendar Year].Members }
		)
	) ON COLUMNS,
	{
		[Geography].[Geography].[Country].Members 
	} ON ROWS
FROM	[Adventure Works]

HTH, Martin

---

http://martinmason.wordpress.com

Answer 2

this actually works almost perfectly.  i just made created a set from mdx in excel 2010 with this mdx:

Union (
		CROSSJOIN(
			{ [Measures].[GL - GL Accounts By GL Account Category Member Unique Name] },
			{ [Date Default].[Dates].[All Dates] }
		),
		CROSSJOIN(
			{ [Measures].[GL Transaction Amount] },
			{ [Date Default].[Dates].[Year].Members }
		),
		CROSSJOIN(
			{ [Measures].[GL Transaction Amount] },
			{ [Date Default].[Dates].[Quarter].Members }
		)
)

the report is now identical to my previous report, but with the member unique name in a single column.  awesome.  however, i am missing some functionality, which is the ability to expand and collapse the dates in the columns.  is that just not going to be possible?

Answer 3

i think i have it working the way i need now.  i just created a calculated member scoped to the dates all member:

SCOPE([Date Default].[Dates].[All Dates], [Measures].[GL Transaction Amount]);
THIS = IIF([Measures].[GL Transaction Amount] <> 0, [GL Account].[GL Accounts By GL Account Category].CurrentMember.MEMBER_UNIQUE_NAME, null); 
END SCOPE;

because i never need a "grand total" for rows when i have the dates hierarchy in the column labels area, it works to just overwrite the grand total of my measure with the member unique name like this.  and this allows me to use excel's expand/collapse functionality for the dates hierarchy.

i guess that at some point i might need this kind of functionality, while also needing the grand total for rows, so it would be interesting to know how i might go about that (while maintaining expand/collapse for the columns hierarchy in excel)...  but for now, i think i have what i need.  thanks for getting me pointed in the right direction.

Answer 4

Yes. That'll be a limitation of using asymmetric sets in this fashion.

---

http://martinmason.wordpress.com