BIDS Helper Drill Through with Distinct

Topics: Standing Discussions
Feb 19, 2013 at 5:03 PM
Edited Feb 19, 2013 at 5:15 PM
First - thanks guys for BIDS Helper with the drill through capability.

I have a calculated measured for counting distinct employee for a time period. Whether the dimension member is year / month / week, this works just fine. However, when user is drilling through at the year granularity, I need to show distinct list of employee for that period of time. Because I am keeping data at the month granularity, I am getting back multiple entries for the same employee for that year. How do you suggest implementing a distinct drill through?

Second question - if I upload an excel spreadsheet up to sharepoint, the drill through mechanism is not available at all. Is there a way around this?

Thanks in advance for your responses.
Coordinator
Feb 21, 2013 at 1:20 AM
I don't think BIDS Helper will help much with this.

I would probably build a rowset action. The action would have an action expression which builds a string, the string being a MDX query that picks up the current context using the ASSP assembly. It would look something like:
"select {[Measures].CurrentMember} on 0, NON EMPTY [Employee].[Employee].[Employee].Members on 1 from (select (" + ASSP.CurrentCellAttributes([Measures].CurrentMember) + ") on 0 from [CubeName])"

See http://asstoredprocedures.codeplex.com/wikipage?title=Drillthrough&referringTitle=Home

As for SharePoint, I don't think drillthough is possible. You might be able to use a =CUBEVALUE formula and a =HYPERLINK formula to link a cell to some other web page or report. But I don't believe you can initiate drillthrough. Excel Services 2013 does support refreshing a QueryTable, so if you have already done a drillthrough in Excel and the drillthrough sheet to Excel Services, I think you can refresh it. But you can't change the drillthrough filters, so it's of limited value.
Feb 21, 2013 at 4:56 PM
Thanks for your detailed reply!