August 18, 2013

Exist vs. Existing and Auto Exist–With Measures–Part 3

Please read the other two parts for continuity

Exist–With Measures

Most of the examples around the Existing keyword talks about the effect of Exists on Dimension . But what will happen if i use it against measure

Will that make any difference?

Existing keyword set the current context on measure calculation which is really invaluable

Let us check the below query

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]


image



As expected, the Calculated measure has summed up the sales related to Australia and Canada, which is way lower than total amount against a year.



Let us execute the below query and get the sales against different countries

with member SalesAmtAustraliaCanada as
Aggregate({[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Geography].[Geography].[Country].members on 1
from [Adventure Works]
image

As you can see, it displays the summed value against each country and they are same. Though theoretically this is correct, we would ideally want to get the data only against Australia and Canada for this new measure.


So what is happening here?


Calculated members doesn’t get affected by the current query context . If we need the current context then we need existing keyword

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Geography].[Geography].[Country].members on 1
from [Adventure Works]


image



What will happen if we  change the query back to Date dimension?

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada} on 0,
[Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]


image



It produces the same result as above because in the above query the current context for Geography dimension is “All Geographies”



let us confirm this with below query

with member SalesAmtAustraliaCanada as
Aggregate(existing {[Geography].[Geography].[Country].&[Australia],
[Geography].[Geography].[Country].&[Canada]
}
,[Measures].[Reseller Sales Amount]
)
member Geographycurrenthier as
[Geography].[Geography].membervalue
select {[Measures].[Reseller Sales Amount],SalesAmtAustraliaCanada,Geographycurrenthier} on 0,
[Date].[Calendar].[Calendar Year].members on 1
from [Adventure Works]


image



This is very useful technique for End user reporting.



Happy reading Smile

No comments:

Post a Comment