This seems like an easy MDX question, but I haven't figured it out yet.

I need to pass the integer value of the current fiscal year (or in some
cases the current fiscal quarter) into an MDX query (or use a sub-expression
of course). How do you get MDX to tell you the current fiscal year of "now"?

Thanks,
Terry

______________________
Terry Smith
Senior Architect
Insight Ecosystems
http://www.insightecosystems.com

Good question...

One way would be to use StrToMember to build the current fiscal year member.

What I have done in the past is define a custom time set and build it on the
fly or if you need current fiscal year, build it as a custom set in the
cube, then select it.


"Terry Smith" <TerrySmith (AT) discussions (DOT) microsoft.com> wrote

If you are "passing in" a fiscal year or quarter you probably want to
use the StrToMember() function as Tim suggests.

In terms of getting the current time, I have pasted a very simple query
below that runs against the Adventure Works DW sample database and
demonstrates using the Year(), Month() and Now() functions. Hopefully
this will get you started.

WITH
MEMBER measures.CalYear AS Year(Now())
MEMBER measures.FinYear AS IIF(Month(now()) > 6
, Year(Now())
,Year(Now()) -1)
SELEDT {measures.CalYear
, measures.Finyear} on columns
FROM [Adventure Works]


--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <#XAM8QUpGHA.1796 (AT) TK2MSFTNGP03 (DOT) phx.gbl>,
Tim (AT) MindYourSpammy (DOT) spam says...

Hi Darren,

I suppose, that the first question was addressed to a little another
problem:
How to get existing DimensionMember from some Time Dimension Level matching
current date?

Hiere is one of possible solutions.

select
Exists([Date].[Fiscal].[Fiscal Year].members,
Filter([Date].[Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = VBA![Date]()))
on 0,
{} on 1
from [Adventure Works]

Because in Adventure Works the last filled year is 2004
this query as running example


select
Exists([Date].[Fiscal].[Fiscal Year].members,
Filter([Date].[Date].[Date].members,
[Date].[Date].CurrentMember.MemberValue = VBA!DateAdd("yyyy", -2,
VBA![Date]())))
on 0,
{} on 1
from [Adventure Works]

the generic form is

select
Exists(<<LevelName>>.members,
Filter(<<Key Attribute Hierarchy>>.members,
<<Key Attribute Hierarchy>>.CurrentMember.MemberValue = VBA![Date]()))
on 0,
{} on 1
from <<CubeName>>

All what you need is Key Attribute Hierarchy Name. It is easy to get with
MDSCHEMA_HIERARCHIES.


Thanks,
Vladimir Chtepa


"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> schrieb im Newsbeitrag
news:MPG.1f1f870c71b0191498996b (AT) news (DOT) microsoft.com...

You are probably right, that is probably a better interpretation of the
original question.

Building a unique name for the current date as a string and using the
StrToMember() function is another common approach. Particularly if you
find yourself querying a cube without a date attribute.

StrToMember("[Date].[Fiscal].[Fiscal Year].&["
+ CStr(Year(Now())) + "]")

Of course if you want this example to work in Adventure Works you have
to subtract 2 from the year to bring it back to a date that exists in
the cube.

StrToMember("[Date].[Fiscal].[Fiscal Year].&["
+ CStr(Year(Now()) - 2) + "]")


--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <uJcabddpGHA.3584 (AT) TK2MSFTNGP03 (DOT) phx.gbl>, vc.nospam@diacom-
systemhaus.nospam.de says...

Hi Darren,

you approach based on the knowlege how is the uniquename build.
Generally it isn't good approach. As AS Developers (Mosha&Co) more times
have told, don't try to parse or build unique names. It is a AS's thing,
isn't user's.

My approach is based on the knowlege, that key attribute have a type
DateTime of the MEMBER_VALUE built-in property.

It's easy to check with MDSCHEMA_HIERARCHIES and MDSCHEMA_PROPERTIES whether
is the condition true.

Thanks,
Vladimir Chtepa

"Darren Gosbell" <jam (AT) newsgroups (DOT) nospam> schrieb im Newsbeitrag
news:MPG.1f20f9617cd01e8398996d (AT) news (DOT) microsoft.com...

It's not the best approach, just an alternative. Sometimes you find
yourself working with existing, sub-optimal dimensions and need to
resort to cruder methods. Particularly users of AS2k that do not have
the Exists() function.

You certainly can check the schema rowsets to find out information about
the key attribute, but you can't do this inside an MDX expression. So
you still end up embedding some degree of knowledge about the cubes
schema in an expression.

Cheers
Darren

In article <uB$TemopGHA.2464 (AT) TK2MSFTNGP03 (DOT) phx.gbl>, vc.nospam@diacom-
systemhaus.nospam.de says...