Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

MDX query set does not display correct value Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 2:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
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]
Post #1509687
Posted Wednesday, October 30, 2013 4:27 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 557, Visits: 1,074
Firstly, for the love of all that is holy please format your code and put it in a code block before posting it here
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
Post #1509707
Posted Wednesday, October 30, 2013 8:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
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
Post #1509810
Posted Wednesday, October 30, 2013 8:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 557, Visits: 1,074

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
Post #1509817
Posted Wednesday, October 30, 2013 8:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
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
Post #1509824
Posted Wednesday, October 30, 2013 9:02 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 557, Visits: 1,074
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
Post #1509825
Posted Wednesday, October 30, 2013 9:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
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]
Post #1509828
Posted Wednesday, October 30, 2013 9:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
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)? ?
Post #1509831
Posted Wednesday, October 30, 2013 9:20 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
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]

Post #1509835
Posted Wednesday, October 30, 2013 9:21 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 9:54 AM
Points: 557, Visits: 1,074
It's "[" code="SQL" "]" your code "[" /code "]"

Without the quotes around the square brackets.





I'm on LinkedIn
Post #1509836
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse