none
Drillthrough Condition in Analysis Services 2008 R2

    Question

  • Drillthrough in Analysis Services 2008 R2 

    Issue: “Condition” is not functioning in Action Drillthrough as desired.

    Objective: To get the drillthrough detail based on some criteria

    Below is the Drillthrough output form the browser. I want to eliminate the 0 values OR  to eliminate “No” values  as highlighted in red below.

    Cube – Action

    Below is the DrillThorugh Action which I have created to get the desire output as shown above

    If I use Condition on Dimension like [Sur_Resp].[Survey Response Result].&[Yes], Condtion will not be satisified and Drillthrough returns all data ( without filter condition)

    After giving "[Measures].[Total Responses]=1" in the condition, the Drillthrough option doesn't showup when trying to use Drillthru option using Browser or In MS Excel.
    • Moved by Naomi N Thursday, March 15, 2012 7:19 PM SSAS question (From:Forums Issues (not product support))
    Thursday, March 15, 2012 1:29 PM

Answers

  • As you can see from what you did, it is a workaround as the DrillThrough functionality is limited in its implementation. There are no conditions you can apply to restrict the resultset. So yes, you are correct - if you add another measure and that has some value, it will be an issue.

    Preserve will not have any Performance impact - in fact it is better in cases you have to do a non-empty counts, there is no need to check for the measure value being 0 and converting them to NULL in MDX expressions


    -Remember to mark as helpful/the answer if you agree with the post.

    Friday, March 16, 2012 1:16 PM
  • Is the measure value coming from the underlying data source? If it is, and it is set to 0 in the fact table, you can look at converting it to NULL, and set the measure property to Preserve in the cube measure group. This way it will stay NULL, and not get converted to 0 in the cube.

    The DrillThrough Action will filter out all NULL values and that should take care of it

    hth


    -Remember to mark as helpful/the answer if you agree with the post.

    • Marked as answer by SriMicro Friday, March 16, 2012 12:27 PM
    • Unmarked as answer by SriMicro Friday, March 16, 2012 12:27 PM
    • Proposed as answer by Jerry NeeModerator Monday, March 19, 2012 6:08 AM
    • Marked as answer by Jerry NeeModerator Thursday, April 05, 2012 9:18 AM
    Thursday, March 15, 2012 8:05 PM

All replies

  • Objective: To get the drillthrough detail based on some criteria
    Below is the Drillthrough output form the browser. I want to eliminate the 0 values OR  to eliminate “No” values  as highlighted in red below.

    Cube Action - below is the Drillthrough Action which I have created to get the desire output as shown above

    After giving  “[Measures].[Total Responses]=1” in the condition, the DrillThorugh option doesn’t show upwhen trying to run using Browser or MS Excel.
    Even after giving condition on Dimension like
     [Sur_Resp].[Survey Respose Result].&[Yes] , Drillthrough returns data without any conditions satisfied.

    Please let me know if you need any further info.

    Please help. Thanks in advance.


    Srikanth

    Thursday, March 15, 2012 2:47 PM
  • Is the measure value coming from the underlying data source? If it is, and it is set to 0 in the fact table, you can look at converting it to NULL, and set the measure property to Preserve in the cube measure group. This way it will stay NULL, and not get converted to 0 in the cube.

    The DrillThrough Action will filter out all NULL values and that should take care of it

    hth


    -Remember to mark as helpful/the answer if you agree with the post.

    • Marked as answer by SriMicro Friday, March 16, 2012 12:27 PM
    • Unmarked as answer by SriMicro Friday, March 16, 2012 12:27 PM
    • Proposed as answer by Jerry NeeModerator Monday, March 19, 2012 6:08 AM
    • Marked as answer by Jerry NeeModerator Thursday, April 05, 2012 9:18 AM
    Thursday, March 15, 2012 8:05 PM
  • Hi Naveen,

    Thanks a ton for the help.

    I modifed as you suggested. It's working fine however if I try to include other Measures in the Drillthrough Columns, the result wouldn't be as desired.

    And will the Preserve property hit the performance?

    Thanks in Advance
    Sri


    Srikanth

    Friday, March 16, 2012 12:30 PM
  • As you can see from what you did, it is a workaround as the DrillThrough functionality is limited in its implementation. There are no conditions you can apply to restrict the resultset. So yes, you are correct - if you add another measure and that has some value, it will be an issue.

    Preserve will not have any Performance impact - in fact it is better in cases you have to do a non-empty counts, there is no need to check for the measure value being 0 and converting them to NULL in MDX expressions


    -Remember to mark as helpful/the answer if you agree with the post.

    Friday, March 16, 2012 1:16 PM