-
currentMember: difference between AS2000 e AS2008
Good morning,
i am migrating from SS2000 / AS2000 to SS2008 / AS2008 and fixing my own olap tool to work correctly within but i am in trouble in time comparison.
In AS2000 when i would to compare a measures (e.g. Quantity) in a time period (e.g. 1st Jan until today in 2009,2010,2011), i execute an mdx like follow:
WITH
MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1]:[orderDate].currentmember.[5]},[measures].[quantity])+sum({[orderDate].currentmember.[6].[1]:[orderDate].currentmember.[6].[21]},[measures].[quantity])'
SELECT
{[orderDate].[All orderDate].[2009],[orderDate].[All orderDate].[2010],[orderDate].[All orderDate].[2011]} on columns,
non empty toggledrillstate({[nation].[All nation]},{[nation].[All nation]}) on rows
FROM orders
WHERE ([measures].[time_quantity])
or simplier, just for monthly comparison
WITH
MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1].[1]:[orderDate].currentmember.[1].[31]},[measures].[quantity])'
SELECT
{[orderDate].[All orderDate].[2009],[orderDate].[All orderDate].[2010],[orderDate].[All orderDate].[2011]} on columns,
non empty toggledrillstate({[nation].[All nation]},{[nation].[All nation]}) on rows
FROM orders
WHERE ([measures].[time_quantity])
In AS2008 when i run this query, an error occour with the following description.
Parser: Sintassi di '[1]' non corretta. (Parser: incorrect sintax of '[1]')
Where is the issue? Can't i use currentMember in WITH MEMBER?
Thanks for your support,
Cristian
-
Sql2k8 books online has syntax and samples under 'CurrentMember (MDX)'.
-
Maybe i didn't look in right way, but i had not found example for my scenario.
I try to look better
-
In order to be clear, i try to explain better the reason of currentMember usage and issue's origin
Think to this scenario
1. User require order quantity in 2011 from 1st january to 15 ; i can inquery in many way but i would use this one (in 2. i'll be more clear)
WITH
MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1].[1]:[orderDate].currentmember.[1].[15]},[measures].[quantity])'
SELECT
{[orderDate].[All orderDate].[2011]} on columns,
{[nation].[All nation]}) on rows
FROM orders
WHERE ([measures].[time_quantity])
2. User would compare quantity with same period of others year (i.e. 2010); with this query i have just to add requested year on column and currentMember calculate total for right period
WITH
MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1].[1]:[orderDate].currentmember.[1].[15]},[measures].[quantity])'
SELECT
{[orderDate].[All orderDate].[2010],
[orderDate].[All orderDate].[2011]} on columns,
{[nation].[All nation]}) on rows
FROM orders
WHERE ([measures].[time_quantity])
3. In this way i can easily add year to compare (and doing from a visual basic olap client is a good job!) and manage time period just from with member
WITH
MEMBER [measures].[time_quantity] as 'sum({[orderDate].currentmember.[1]:[orderDate].currentmember.[5]},[measures].[quantity])
+ sum({[orderDate].currentmember.[6].[1]:[orderDate].currentmember.[6].[21]},[measures].[quantity])'
SELECT
{[orderDate].[All orderDate].[2000],
[orderDate].[All orderDate].[2001],
[orderDate].[All orderDate].[2010],
[orderDate].[All orderDate].[2011]} on columns,
{[nation].[All nation]}) on rows
FROM orders
WHERE ([measures].[time_quantity])
-
If someone should be interested in, i had answer on Microsoft's Social
http://social.msdn.microsoft.com/For...0-53c30859a27c
WITH
MEMBER [measures].[time_quantity] as
sum(
{
--instead of [orderDate].currentmember.[1].[1]
strToMember("[orderDate].[" + [orderDate].currentMember.member_key + "].[1].[1]")
:
--instead of [orderDate].currentmember.[1].[31]
strToMember("[orderDate].[" + [orderDate].currentMember.member_key + "].[1].[31]")
}
,[measures].[quantity])
SELECT
{[orderDate].[All orderDate].[2009],[orderDate].[All orderDate].[2010],[orderDate].[All orderDate].[2011]} on columns,
non empty toggledrillstate({[nation].[All nation]},{[nation].[All nation]}) on rows
FROM orders
WHERE ([measures].[time_quantity])
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|