locked
Time Measure Group causes extra rows to appear RRS feed

  • Question

  • Morning

    Im using Analysis Services 2005.  The report team required a measure called Working Days which is 0 for weekends or days the company is closed and 1 when it's open. This is to allow us calculate some projections and targets.

    I created a named query FACT_Calendar based off the time dimension table DIM_Calendar and created a measure group Calendar . The Calendar measure group is only linked to Calendar dimension.  They are linked on Calendar_key in both objects in dsv and dimension usage.

    The problem  is when I use the Calender measure group along with other dimensions and measures.  For example:

    Dimension (in row): Sales People
    Measure (in data area): Order Count
    Filtered by: Calendar Month & Year = August 2009.

    This returns 25 agents.  Once I add the Working Days measure it becomes 35. The additional rows have an Order Count of blank.

    I tried changing the Calendar measure to IgnoreUnrelatedDimensions = False but now get blanks returned for Working Days .

    P.S I do have some calculated members based on the Working Days measure but I'm not convinced these are causing the issue as it appears when they aren't in use.

    Thanks in advance
    • Edited by EKOner Thursday, September 24, 2009 9:22 AM Additional Info
    Thursday, September 24, 2009 9:21 AM

Answers

  • hi,

    excel only hides completly empty rows (e.g. all measures are NULL/Empty)
    WorkingDays is always filled regardless of the selection on rows becuse the Workdays-Measure is only related to Dim_Calendar
    so it shows the same value for all other dimension that you put on rows

    because WorkingDays always has a value all rows are shown because excel only hides completly empty rows which is not the case any more once you use WorkingDays

    you could create a calculated member as follows
    CREATE MEMBER CURRENTCUBE.[Measures].[Working Days 2] AS
    IIf(IsEmpty([Measures].[Order Count], NULL, [Measures].[Working Days])

    if you use this measure it should work as expected

    greets,
    gerhard

    - www.pmOne.com -
    • Marked as answer by EKOner Friday, September 25, 2009 7:54 AM
    Thursday, September 24, 2009 9:48 AM
    Answerer

All replies

  • hi,

    excel only hides completly empty rows (e.g. all measures are NULL/Empty)
    WorkingDays is always filled regardless of the selection on rows becuse the Workdays-Measure is only related to Dim_Calendar
    so it shows the same value for all other dimension that you put on rows

    because WorkingDays always has a value all rows are shown because excel only hides completly empty rows which is not the case any more once you use WorkingDays

    you could create a calculated member as follows
    CREATE MEMBER CURRENTCUBE.[Measures].[Working Days 2] AS
    IIf(IsEmpty([Measures].[Order Count], NULL, [Measures].[Working Days])

    if you use this measure it should work as expected

    greets,
    gerhard

    - www.pmOne.com -
    • Marked as answer by EKOner Friday, September 25, 2009 7:54 AM
    Thursday, September 24, 2009 9:48 AM
    Answerer
  • Excellent, will try that. Thank you
    Thursday, September 24, 2009 9:49 AM