CODEWITHSUNDEEP
mdx
James Culshaw
James Culshaw

Reputation: 1057

Wrong totals in MDX result

I have the following MDX Query:

SELECT  { [Measures].[Work Order Count Housekeeping Per Sq Ft],  
          [Measures].[Work Order Count], 
          [Measures].[House Keeping Square Footage]} ON 0,  
        { (  [Location].[Entity Location Name].Members ) } ON 1 
FROM [Work Order] 
WHERE ( {  [Department].[Department Name].[Housekeeping]}, 
        { [Location].[Entity Location ID].[12280], [Location].[Entity Location ID].[14067], [Location].[Entity Location ID].[15092]}, 
        {  [Event Start Dates].[Date Key].[20160705] :  [Event Start Dates].[Date Key].[20180705]   }, 
        { [Owner Entity].[Entity ID].[12279], [Owner Entity].[Entity ID].[12280], [Owner Entity].[Entity ID].[14067], [Owner Entity].[Entity ID].[15092]}, 
        { [Work Order Days Open].[Days Open].[1] : [Work Order Days Open].[Days Open].[250] }, 
        { [Work Order Days Overdue].[Days Overdue].[1] : [Work Order Days Overdue].[Days Overdue].[250] } )

This is what I get as a result:

enter image description here

I was expecting (*and need) the All values to be 6.42857, 45 and 7 rather than the values I get.

What am I doing wrong in my query?

Upvotes: 1

Views: 37

Answers (1)

whytheq
whytheq

Reputation: 35557

ALL will always be ALL

Seems like you want a new ALL that is the aggregation of the 3 members selected in the WHERE clause.

You can create an ALLcustom member using a WITH clause:

WITH
SET LocationSet AS
{ [Location].[Entity Location ID].[12280], 
[Location].[Entity Location ID].[14067], 
[Location].[Entity Location ID].[15092]}
MEMBER Location].[Entity Location ID].[All].ALLcustom AS
AGGREGATE ( LocationSet )
SET [Locations] AS
{LocationSet,
 [Location].[Entity Location ID].[All].ALLcustom
}
SELECT  { [Measures].[Work Order Count Housekeeping Per Sq Ft],  
          [Measures].[Work Order Count], 
          [Measures].[House Keeping Square Footage]} ON 0,  
        [Locations] ON 1 
FROM [Work Order] 
WHERE ( {  [Department].[Department Name].[Housekeeping]}, 
        {  [Event Start Dates].[Date Key].[20160705] :  [Event Start Dates].[Date Key].[20180705]   }, 
        { [Owner Entity].[Entity ID].[12279], [Owner Entity].[Entity ID].[12280], [Owner Entity].[Entity ID].[14067], [Owner Entity].[Entity ID].[15092]}, 
        { [Work Order Days Open].[Days Open].[1] : [Work Order Days Open].[Days Open].[250] }, 
        { [Work Order Days Overdue].[Days Overdue].[1] : [Work Order Days Overdue].[Days Overdue].[250] } )

Upvotes: 1

Related Questions