locked
How to supress rows with 0 values. All measures, fact and calculated, are 0 RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    The cube going into browser or Excel, shows the following.

       Measure                     Measure                 Calculated                            

        TotalRevenue(measure)  TotalCost(measure)  GrossMargin(calculated)        Gross Margin%(calculated)                   

        $#,##0;($#,##0)         $#,##0;($#,##0)   "$#,##0;($#,##0)"              "#,##0.0 %;(#,##0.0 %)"

    The excel gives me..

    A   $552,198                      ($437,190)                            $115,008                                              20.8%

    B             $0                                  $0                                       $0                                        

    How can I suppress/hide the row with $0 values and NULL.  So the user does not need to filter, in Excel.

    Thanks for your help  

    MariaLuz Munoz

    Thursday, July 2, 2015 11:46 PM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    If it is a calculation, simply do a test like IIF(Measures.MyValue<>0, Measures.MyValue, null)

    If it is a physical measure, then you have the option of converting nulls in the fact table to null. Arithmetically, null=0 in the cube. So, the aggregations will be equivalent, but a count(), avg() etc will be different. The NullProcessing property on the measure is important. Preserve, will keep it as null, while NullOrZero will convert a null fact measure to zero.

    I like translating zeroes to nulls in the fact table, as this makes the cube a lot smaller. Just check that a zero is equivalent to a null. Ie, you don't need to count values including zeroes but excluding nulls.

    Hope that helps,

    Richard

    Friday, July 3, 2015 3:38 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    If it is a calculation, simply do a test like IIF(Measures.MyValue<>0, Measures.MyValue, null)

    If it is a physical measure, then you have the option of converting nulls in the fact table to null. Arithmetically, null=0 in the cube. So, the aggregations will be equivalent, but a count(), avg() etc will be different. The NullProcessing property on the measure is important. Preserve, will keep it as null, while NullOrZero will convert a null fact measure to zero.

    I like translating zeroes to nulls in the fact table, as this makes the cube a lot smaller. Just check that a zero is equivalent to a null. Ie, you don't need to count values including zeroes but excluding nulls.

    Hope that helps,

    Richard

    Friday, July 3, 2015 3:38 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Maria,

    I think we can not hide or suppress the entire row. If you don't want these records restrict in the DSV itself. Because there may be chance of some of the columns are  having $0 but we have valid amounts an another column. 

    Thanks and Regards Rajesh

    Friday, July 3, 2015 5:26 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Maria,

    According to your description, you want to hide the records with 0 or null values. Right?

    In Analysis Services, to hide these 0 value records is filtering the tuple(using the dimension members slicing the measures). We can only achieve this on MDX query level. Please use NON EMPTY() on the tuple level like:

    NONEMPTY([DIM].[DIM].members,[Measures].[Measure])

    For more information, please refer to an article below:

    MDX : Non Empty v/s NonEmpty

    Regards,

    Simon Hou
    TechNet Community Support


    Sunday, July 5, 2015 1:37 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Richard, Thanks for the reply.

    I have followed your suggestion to get the physical and calculated measure to NULL when these are 0. I'm still testing, and so far so good.  

    Monday, July 6, 2015 7:07 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Maria,

    I am also facing the same problem. I would like to hide entire rows | Column if the rows | columns hahing Zeros or nulls.Can you please reply.

    Thanks,

    Narendra

    Monday, August 24, 2020 12:29 PM