locked
Rows not part of MDX result-set showing up with aggregate information RRS feed

  • Question

  • Sorry about the long post, but this is an odd problem that's kind of complicated.

     

    I'm putting together several reports that show data for different locations. The user selects a level from the Location hierarchy (City - State - Country - Continent), and the report displays info for the selected location along with all its descendants, sorted according to the hierarchy. I was able to accomplish this for one of my reports, but the other one is causing me trouble. When I run the MDX expression from the Data tab, I get the expected results, with all the aggregation working correctly. However, when I run the report additional rows appear that are performing aggregation and interfere with the values that are calculated for the expected aggregate rows.

     

    For example, let's say the user selects Florida (second-lowest level in hierarchy). Within Florida are a dozen cities (lowest hierarchy level). If I were using population as my Measure, the MDX would show a row entry for Florida with the sum of all the cities' populations, followed by a row for each of the dozen cities with their population. I have a different Table grouping for each of the hierarchy levels.

     

    When I run the report, I get the same result set show from the MDX. However, an additional row appears just below Florida, which has the sum of all the cities' populations but has no row-specific info, such as the city name, which all the other cities have and show. I have verified that this row belongs to my City grouping. This aggregate population value is correct, but because of this additional row, the SUM expression I'm using to aggregate for the State level row has a value that is 2x what it should be. If the user specifies a 3rd level Location hierarchy entry (Country), then 2 additional rows appear beneath the Country row; the bottow is a City grouping row with the correct Country aggregate value, and above that is a State grouping row with the same aggregate value. This causes the Country aggregate to be 3x the expected value. There is also an additional aggregating row below each State, like above. Go up another level and you have 3 additional aggregating rows that cause the rollup to 4x the expected value.

     

    I've spent way too long trying anything I could think of doing this, and am out of ideas. ANY help is appreciated.

    Wednesday, September 3, 2008 7:12 PM