I need to bring back a calculated field representing a dimension member's
uniquename in my MDX query so I can support drillthrough links in Reporting
Services. So I added "[Partner].currentmember.UniqueName" as a calculated
member and included it in the Measures being selected. The problem is that I
suddenly get a whole bunch of additional data that should not be there by
virtue of the WHERE clause, though the AMT field is empty.

So now I'm trying to something like this:

WITH
MEMBER [Measures].[PartnerUniqueName] AS 'IIF(ISEMPTY([Measures].[Act
Amt]), NULL, [Partner].currentmember.UniqueName)'
SELECT
NON EMPTY {[Measures].[ACT AMT], [Measures].[PartnerUniqueName]} ON
COLUMNS,
NON EMPTY {[Ledger].Children} ON ROWS
FROM Activity
WHERE ([Fund].[Fund2], [Transaction].[Investment])

so that when the ACT AMT field is empty, the UniqueName will also be empty,
but it doesn't compile because of the NULL. Is there another way to skin this
cat? I can filter out the null values in the report, but the performance is
now much slower because the data set is huge.

Thanks,

--Crandaddy

I have modified the query to run in MDX Sample Application.

WITH
MEMBER [Measures].[PatnerUN] as '[Partner].currentmember.UniqueName'
MEMBER [Measures].[PartnerUniqueName] AS 'IIF(ISEMPTY([Measures].[Act
Amt]), NULL, [Measures].[PatnerUN])'
SELECT
NON EMPTY {[Measures].[ACT AMT], [Measures].[PartnerUniqueName]} ON
COLUMNS,
NON EMPTY {[Ledger].Children*[Partner].Members} ON ROWS
FROM Activity
WHERE ([Fund].[Fund2], [Transaction].[Investment])


In the above query,
[Partner].currentmember.UniqueName will return only
1. "All Partner" when Partner dimension has "All Levels" property set
to true.
or
2. First Member of the Partner dimension when "All Levels" property set
to false.


To get the correct Partner Unique Name, you need to include Partner
([Partner].Members) in Axis Specification.