Dimensional Reporting Problem

[rperwin]

I running into an issue when creating a crosstab report against a transformer cube.

I am trying to use the Member Description as the rows of the crosstab.  If I have two members of the hierarchy (lets say A and B) that both have a Member Description of 'CARS', I would like to see CARS show up as a single line on my crosstab showing the total for A + B.  However, I am seeing two rows showing 'CARS' (1 row for A and a second row for B).

Does anyone know some logic to aggregate the rows into a single line?

Any help would be greatly appreciated.

Thanks

[Lynn]

I know the reason for the behavior is that they MUNs are different, but I don't know how to consolidate the data. I ended up modeling alternate drill paths in my cube, but it would be nice if there were some magical dimensional functions that would allow reporting the way you describe.

[tjohnson3050]

You can create a data item that is an aggregation of the other two members.

Aggregate(currentMeasure within set set([AA],[BB]))

[Lynn]

You can create a data item that is an aggregation of the other two members.

Aggregate(currentMeasure within set set([AA],[BB]))

True, but I was hoping to hit on a way to do it more generically for a level across all the duplicated items....something like the way auto aggregation works in a list report.

[rperwin]

Yes this is really an attempt at a quick fix to get around some data that is missing from the cube.  We are entering some contract information to the Member Description and would like to be able to aggregate on the contract itself.  If I have to enter sets then that would kill the dynamic ability of the report.

[tjohnson3050]

Depending on how complex your crosstab is, you *might* be able to have one query return results from cube, then base a second query in the report on the first query and use the second query as the source of the crosstab.

[MFGF]

Unfortunately, it's a limitation of reports that you cannot group on attributes of members. It's still a limitation in the latest 10.2 release too. You are not doing anything wrong, but you will probably have to use a technique like the one tjohnson suggested to get the result you are looking for.

Cheers!

MF.

[rperwin]

I did get a report to work by basing one query off of another (but tjohnoson is correct - it has to be a very simplistic query) a little too simplistic

I knew that you are not supposed to join dimensional queries together and I think I have tried something like what is mentioned above before (and failed miserably).  I am guessing because I was using too complex of queries.

Thanks for everyones help!