grouping question

Hi,

I have a cube which has a "Product" dimension that has 4 levels which are:

- High Level Product Name
- Mid Level Product Name
- Low Level Product Name
- Product Name

My fact table has measures for all 4 levels.

I have a report with an MDX data source. The report contains a table control which has 2 columns, "product name" and a measure column.

In my report I have defined two groups, one for product level 1, and another for product level 2. This will mean that my data will be grouped by products as follows: (below products a, b and c are level 1, x, y and z are level 2)...

Product Measure
- Prod A $1,500.00
- Prod B $1,200.00
- Prod X $400.00
- Prod Y $400.00
- Prod Z $400.00
- Prod C

Running the report works ok, but for some reason an additional unwanted total row is appended at the beggining of each of my level 2 groups which shows the total for that group. To explain what I mean, the report looks like this...

Product Measure
- Prod A $1,500.00
- Prod B $1,200.00
$1,200.00
- Prod X $400.00
- Prod Y $400.00
- Prod Z $400.00
- Prod C

Note that under product B a blank row has been added with the total. I don't want this and I can't figure out how to get rid of it. Anyone know what I am doing wrong?

If it helps my query is below (reporting services matches the product names to group them)...

SELECT
non empty {[Measures].[Ytd Average Capital] } ON columns,
non empty { [Product].[High Level Product Name].members, [Product].[Mid Level Product Name].members,[Product].[Low Level Product Name].members, [Product].[Product Name].members } ON rows
FROM
[MyCube]

Thanks,

Lachlan

Hi,

I fixed my problem by changing my query to the following...

SELECT
non empty {[Measures].[Ytd Average Capital] } ON columns,
non empty {[Product].[Product Name].members} ON ROWS
FROM tacticaleva

Notice I now only select the lowest level product [Product].[ProductName] rather than selecting all of my product levels in one mdx query.

I then changed the measure expressions in the product group rows to use =Sum(Fields!Measures_Ytd_Average_Capital.Value) rather than just the measure without Sum(). The lowest level product is not in a group it's just in a row so I didnt need to use Sum(), I just used =Fields!Measures_Ytd_Average_Capital.Value.

I'm not sure why it works this way and not the first way that I tried it, but I'm new to both MDX and SSRS and still learning :)

Lachlan