Handling Transactional-Level Data - Expert Cube Development with SSAS Multidimensional Models - page 122

There are two possible workarounds. The first is to create real members instead
of calculated members on a dimension, and use assignments to overwrite their
value in the MDX Script. For example, take a look at the DateTool dimension
described in Chapter 6 , Adding Calculations to the Cube , drillthrough is enabled on any
noncalculated member that belongs to a dimension, even if that dimension is not
related to any measure group. However, if the calculation in the MDX Script refers
to values from somewhere else in the cube, drillthrough does not reflect this and
always returns the same result, ignoring the possibility that it might not have any
connection at all to the value displayed in the cell.
For example, the DateTool dimension may include a member to
calculate the year-to-date sum of any measure. Therefore, looking at the
result for March 2009, we could expect that drillthrough would return the
set of fact table rows from January 1 to March 31. However, in reality, we
will only receive data from March 1 to March 31 if we look at the March
member; similarly, if we go down to the Day level and drillthrough on
March 31, we will get only the transactions from that day. In both cases,
we might prefer that drillthrough operations follow the calculation logic
as well as the physical coordinates, but it is not possible to customize the
way Analysis Services determines which rows should be returned.
The second workaround is to create an action of type rowset, defining as target
type all the cells and using the Condition expression to ensure it is only enabled for
the calculated measures we want to use it on. This type of action can have an MDX
DRILLTHROUGH statement that returns the data we want and we have full control over
the rows and columns returned by this action. Clients such as Excel support rowset
actions and display their results in the same way they do those of drillthrough
actions, because we always get a rowset as a result. However, in this case the MDX
query for the drillthrough is not built by the client but by the server and there are
some other differences in behavior. For example, the maximum rows returned by a
drillthrough cannot be controlled by the client if we are using a rowset action. More
information about this workaround is available on Mosha Pasumansky's blog in this
post: http://tinyurl.com/drillcalc .
As an alternative to drillthrough, it's also possible to implement
an action that returns a rowset and specify your own SQL for
the query that it runs. To do this, however, you need to create an
Analysis Services stored procedure in .NET and call it from the
Action expression. More information on how to do this can be
found here: http://tinyurl.com/ssas-sql