Hi,
Is it possible to change the way that MDX is generated by the PivotGrid when filters are applied?
Currently when I apply a Month filter in my time dimension for e.g., this is done by adding the filter value as a sub-cube query in the from clause, rather than placing it in the WHERE clause.
So instead of having
SELECT x ON 0, y ON 1
FROM <cube>
WHERE ([time].[month].&[z])
The pivot grid generates
SELECT x ON 0, y ON 1
FROM (SELECT [time].[month].&[z] FROM <cube>)
This is generally fine, but when we have calculated measures in the cube which rely on knowing where you are in the time hierarchy, (i.e. [time].[month].currentmember) then this distorts the calculation because the scope is handled in the sub-cube and the main query believes that [time].[month].currentmember is 'All' rather than Month Z as the query above suggests.
Is there a way to tell the grid to use the former method? Failing that, how can we access the time filter from the calculations?