Setting default member of attribute hierarchy in MDX script. Default member not taken into account while using user defined hierarchy

hi all

i have date dimension (type time) attributes
- [fiscalyear] (type years)
- [fiscalmonth] (type months)
- [fiscalweek] (type weeks)

in addition attributes used in natural hierarchy, have attribute [pertype] containing 1 member comming relational table 'wtd' corresponds 'current date'. other members of attribute hierarchy calculated members (defined in mdx script). examples:

--last year

 

create member currentcube.[date].[pertype].[ly] as null,
visible = 1;
scope([date].[pertype].[ly]);
scope(descendants([date].[fiscal].[all], [date].[fiscal].[year], self_and_after));
this = ([measures].currentmember, [date].[pertype].[wtd], parallelperiod([date].[fiscal].[year], 1));
end scope;
end scope;

--month date

 

create member currentcube.[date].[pertype].[mtd] as null,
visible = 1;
scope([date].[pertype].[mtd]);
scope(descendants([date].[fiscal].[all], [date].[fiscal].[week], self_and_after));
this = aggregate(crossjoin({[date].[pertype].[wtd]}, mtd([date].[fiscal].currentmember)));
end scope;
end scope;

--year date

 

create member currentcube.[date].[pertype].[ytd] as null,
visible = 1;
scope([date].[pertype].[ytd]);
scope(descendants([date].[fiscal].[all], [date].[fiscal].[period], self_and_after));
this = aggregate(crossjoin({[date].[pertype].[wtd]}, ytd([date].[fiscal].currentmember)));
end scope;
end scope;

the defalut member of fiscalweek attribute hierarchy set dynamically in mdx script:

alter cube currentcube update dimension [date].[fiscalweek], default_member =
filter(
[date].[fiscalweek].members,
[date].[fiscalweek].properties( "fiscalweekstartdate", typed) <= dateadd("d", -2, cdate(cstr(month(now())) + "/" + cstr(day(now())) + "/" + cstr(year(now()))))
and
[date].[fiscalweek].properties( "fiscalweekenddate", typed) >= dateadd("d", -2, cdate(cstr(month(now())) + "/" + cstr(day(now())) + "/" + cstr(year(now()))))
)(0).prevmember;

if run following query:

with member
measures.x [date].[fiscal].defaultmember.name
measures.y [date].[fiscalweek].defaultmember.name
select
{
measures.x,
measures.y
} on axis(0)
from [glweekly]

it gives me correctly default member set on mdx script.

i order statements in mdx script default period (week) set @ beginning of script (just after calculate).

i not understand why creating following calculated member obliged specify [date].[fiscal].currentmember in tuple have correct results:

member [account].[coa].[standard engagement revenue (mtd)] ([account].[coa].[standard engagement revenue], [date].[pertype].[mtd], [date].[fiscal].currentmember)

i expect that:

([account].[coa].[standard engagement revenue], [date].[pertype].[mtd])

is sufficient.

if default week specified in slicer using member of natural hierachy (=> [date].[fiscal].x) works.

why can't ssas use default member if must defined in mdx script?

can explains me this. lot in advance.

 

well, since there no response you, though might add wanted in previous post.

i believe date.week attribute "similar" date.[fiscal week] , date.fiscal.week level. means if put member in tuple, act same way having default member in tuple. which, noticed before, forces current coordinate evaluated required. similarity caused by relation. when put member hierarchy in tuple, there implicite adjustment in the hierarchy holding default member. current member of hierarchy forced member happens default member - same week. without it, root member, or single regular member named wtd, forcing member current member in hierarchy. new date.week hierarchy assisting in process because related.

let's analyze my example. referring part of cake having zodiac sign can achieved by either using sign or referring to, let say, decoration on cake. if there 12 different decorations, each per 1 sign, refering particular decoration you're achieving same effect referring sign. because related (1:1).

i believe week hierarchies related , because of - works.

hope helps.

regards,


tomislav piasevoli
business intelligence specialist
www.softpro.hr

SQL Server  >  SQL Server Analysis Services