Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


MDX query set does not display correct value


MDX query set does not display correct value

Author
Message
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 78
Hello Experts,

Can you please suggest me the alternate solution of following MDX query issue please its very Urgent:

The following set of COMP_YTD_CP is works fine with single brand but when user selectes multiple brand it always takes min value of the First Invoice Month irrespective of current memebre of the brand
For e.g: Brand First Invoice Month
B1 201202
B2 201107
B3 201101
B4 201204
In this case, for all the brands first invoice date is considerd as 201101 inspite of different first invoice dates mentioned above next to the brand.


WITH

SET SP
AS STRTOMEMBER(“[Time].[Fiscal Hierarchy].&[201204]”)

MEMBER [Measures].[First Invoice Year] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalYear].Members,
([Measures].[Sales Quantity])
),
1
).Item(0).properties("key", TYPED)

SET ALL_Months_Selected_Year
AS Descendants(Ancestor(SP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])

//--Getting Month previous to the First Invoice Month and then getting PeriodsToDates
SET ALL_Months_Till_First_Invoiced_Month
AS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear],
PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,
Head( NonEmpty( EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity]) ), 1).Item(0)))

SET COMP_SET_SUBSTRACT_CP
AS EXCEPT(ALL_Months_Selected_Year, ALL_Months_Till_First_Invoiced_Month)

SET COMP_YTD_CP
AS IIF([Measures].[Selected Year]-1 > [Measures].[First Invoice Year],
SP.item(0) ,
IIF([Measures].[Selected Year]=[Measures].[First Invoice Year] ,
NULL,
INTERSECT(SP, COMP_SET_SUBSTRACT_CP))
)
MEMBER [MEASURES].[SALES YTD CP COMP]
AS SUM(COMP_YTD_CP, [Measures].[Revenues])
SELECT
{[MEASURES].[SALES YTD CP COMP]} ON COLUMNS,
Nonempty( {[Brand].[Brand].[Brand Major].members
* [Customer].[Country].[Country]* [Currency].[Currency].[Currency]})on rows FROM [Cube1]
PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 2404
Firstly, for the love of all that is holy please format your code and put it in a code block before posting it here :-D

WITH
SET SP AS
StrToMember([Time].[Fiscal Hierarchy].&[201204])

MEMBER [Measures].[First Invoice Year] AS
Head
(
NonEmpty
(
(EXISTING
[Time].[Fiscal Hierarchy].[FiscalYear].MEMBERS)
,[Measures].[Sales Quantity]
)
,1
).Item(0).Properties("key"
,TYPED)

SET ALL_Months_Selected_Year AS
Descendants
(
Ancestor
(
SP.Item(0)
,[Time].[Fiscal Hierarchy].[FiscalYear]
)
,[Time].[Fiscal Hierarchy].[FiscalMonth]
)

//--Getting Month previous to the First Invoice Month and then getting PeriodsToDates
SET ALL_Months_Till_First_Invoiced_Month AS
PeriodsToDate
(
[Time].[Fiscal Hierarchy].[FiscalYear]
,ParallelPeriod
(
[Time].[Fiscal Hierarchy].[FiscalYear]
,-1
,Head
(
NonEmpty
(
(EXISTING
[Time].[Fiscal Hierarchy].[FiscalMonth].MEMBERS)
,[Measures].[JBA Sales Quantity]
)
,1
).Item(0)
)
)

SET COMP_SET_SUBSTRACT_CP AS
Except
(
ALL_Months_Selected_Year
,ALL_Months_Till_First_Invoiced_Month
)

SET COMP_YTD_CP AS
IIF
(
[Measures].[Selected Year] - 1 > [Measures].[First Invoice Year]
,SP.Item(0)
,IIF
(
[Measures].[Selected Year] = [Measures].[First Invoice Year]
,NULL
,Intersect
(
SP
,COMP_SET_SUBSTRACT_CP
)
)
)

MEMBER [MEASURES].[SALES YTD CP COMP] AS
Sum
(
COMP_YTD_CP
,[Measures].[Revenues]
)

SELECT
{[MEASURES].[SALES YTD CP COMP]} ON COLUMNS
,NonEmpty
(
{
[Brand].[Brand].[Brand Major].MEMBERS*
[Customer].[Country].[Country]*
[Currency].[Currency].[Currency]
}
) ON ROWS
FROM [Cube1];



That's better.

Right, this query is pretty static. How are the users viewing the results (excel, a parametrized report etc.) as this will help us answer your question?

On first glance your calculations and sets don't appear to take brand into account. This could be it. However without knowing how the cube is being accessed (and the query that is being sent to the cube) it's difficult to tell.

Download MDX Studio for formatting and much more.




I'm on LinkedIn
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 78
Thank you so much sir for response.

This is parameterized query to use in SSRS.

Also as suggested, we are trying make the cross join with Brand in Set but we are getting following issue:

The [Measures].[First Invoice Date1] and [Measures].[First Invoice Date2] shows different result even the value passed is same
==============================================================
Code:-

Set test
AS StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')

MEMBER [Measures].[First Invoice Date1]
AS test.item(0).name

MEMBER [Measures].[First Invoice Date2]
As StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']').name
==================================================================
FYI:
The value passing in string [Measures].[First Invoice Date] = 201207 (I.e. July 2012)
But from viewing test set using the [Measures].[First Invoice Date1] shows the Jan 2011 But the [Measures].[First Invoice Date2] shows the correct value as July 2012

Regards,
Amit J
PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 2404

The value passing in string [Measures].[First Invoice Date] = 201207 (I.e. July 2012)


What MDX are you using to set this measure?

Can you post the full MDX of the report (make sensitive things generic) and please wrap it in a code block!!! ;-)




I'm on LinkedIn
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 78
Hi,

We are using following code for measure:

MEMBER [Measures].[FID] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[Sales Quantity])
),
1
).Item(0).properties("key", TYPED)


MEMBER [Measures].[First Invoice Date]AS
MIN(NonEmpty(
(
{[Brand].[Brand].currentmember} *
{[Customer].[Country].currentmember}*
{[Currency].[Currency].[Currency]}
),[Measures].[FID]),
[Measures].[FID])

Note:- In [Measures].[FID] it shows the different Invoice Month for diffrenet currencies irrespective of same brand and country so user requested to get min of FID so created 2nd [Measures].[First Invoice Date].

Regards,
Amit
PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 2404
Ok, but once again:
Can you post the full MDX of the report (make sensitive things generic) and please wrap it in a code block.

I suspect it's something to do with the MIN statement, but can't be certain.




I'm on LinkedIn
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 78
WITH

SET WTDCP
--AS StrToMember(@Week)
AS StrToMember("[Time].[Fiscal Hierarchy].&[2013043]")

--YTD
MEMBER [Measures].[First Month of Year]
AS ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalMonth]).PROPERTIES("key", TYPED) = ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalYear]).FIRSTCHILD.FIRSTCHILD.FIRSTCHILD.PROPERTIES("key", TYPED)

SET YTDCP
AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER))

SET YTDPP
AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear],1,Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER)))


-- Start for FID logic

MEMBER [Measures].[FID] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)

--If two brads have different FID because of different currency so puting logic to
--get Minimum of FID out of multiple FID for diff currencies.
MEMBER [Measures].[First Invoice Date]AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FID]),
[Measures].[FID])

--End of logic


MEMBER [Measures].[FIY] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalYear].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)


MEMBER [Measures].[First Invoice Year] AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FIY]),
[Measures].[FIY])



MEMBER [Measures].[Selected Year]
AS Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]).properties("key", TYPED)

SET ALL_Months_Selected_Year
AS Descendants(Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])

Set test
as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')

MEMBER [Measures].[First Invoice Date1]
as test.item(0).name

MEMBER [Measures].[First Invoice Date2]
as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']').name


SET ALL_Months_Till_First_Invoiced_Month
AS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear],
PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,test.item(0)))


SET COMPYTD_SET_SUBSTRACT_CP
AS EXCEPT(ALL_Months_Selected_Year, ALL_Months_Till_First_Invoiced_Month)

SET COMP_YTD_CP
AS
IIF([Measures].[Selected Year] - 1 >
[Measures].[First Invoice Year],
YTDCP,
IIF([Measures].[Selected Year] =
[Measures].[First Invoice Year],
NULL,
Intersect(YTDCP, COMPYTD_SET_SUBSTRACT_CP)
))



SELECT {
[Measures].[First Invoice Date],
[Measures].[First Invoice Date1],
[Measures].[First Invoice Date2]
} on columns,
Nonempty( {[Brand].[Brand].[Brand Major].members
* [Customer].[Country].[Country]
* [Currency].[Currency].[Currency]}) on rows
FROM [VFE]

amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 78
Hello

I'm sorry don't know how to past query under code block, is this someting under under IFCode (XML code)? ?
amit_pjoshi
amit_pjoshi
SSC Rookie
SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)SSC Rookie (28 reputation)

Group: General Forum Members
Points: 28 Visits: 78
WITH 

SET WTDCP
--AS StrToMember(@Week)
AS StrToMember("[Time].[Fiscal Hierarchy].&[2013043]")

--YTD
MEMBER [Measures].[First Month of Year]
AS ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalMonth]).PROPERTIES("key", TYPED) = ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalYear]).FIRSTCHILD.FIRSTCHILD.FIRSTCHILD.PROPERTIES("key", TYPED)

SET YTDCP
AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER))

SET YTDPP
AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear],1,Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER)))


-- Start for FID logic

MEMBER [Measures].[FID] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)

--If two brads have different FID because of different currency so puting logic to
--get Minimum of FID out of multiple FID for diff currencies.
MEMBER [Measures].[First Invoice Date]AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FID]),
[Measures].[FID])

--End of logic


MEMBER [Measures].[FIY] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalYear].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)


MEMBER [Measures].[First Invoice Year] AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FIY]),
[Measures].[FIY])



MEMBER [Measures].[Selected Year]
AS Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]).properties("key", TYPED)

SET ALL_Months_Selected_Year
AS Descendants(Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])

Set test
as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')

MEMBER [Measures].[First Invoice Date1]
as test.item(0).name

MEMBER [Measures].[First Invoice Date2]
as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']').name


SET ALL_Months_Till_First_Invoiced_Month
AS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear],
PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,test.item(0)))


SET COMPYTD_SET_SUBSTRACT_CP
AS EXCEPT(ALL_Months_Selected_Year, ALL_Months_Till_First_Invoiced_Month)

SET COMP_YTD_CP
AS
IIF([Measures].[Selected Year] - 1 >
[Measures].[First Invoice Year],
YTDCP,
IIF([Measures].[Selected Year] =
[Measures].[First Invoice Year],
NULL,
Intersect(YTDCP, COMPYTD_SET_SUBSTRACT_CP)
))



SELECT {
[Measures].[First Invoice Date],
[Measures].[First Invoice Date1],
[Measures].[First Invoice Date2]
} on columns,
Nonempty( {[Brand].[Brand].[Brand Major].members
* [Customer].[Country].[Country]
* [Currency].[Currency].[Currency]}) on rows
FROM [VFE]


PB_BI
PB_BI
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 2404
It's "[" code="SQL" "]" your code "[" /code "]"

Without the quotes around the square brackets.




I'm on LinkedIn
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search