Reporting - Summing by attribute?

[MikeF]

Hi,

Just checking to see if anyone's got a better solution to an additional reporting requirement that's been requested. We have a project dimension that lists (surprise, surprise) projects. There are some additional attributes that will be attached to those projects such as sector and location. The request is to report summaries based on those attributes, effectively treating them as quasi-dimensions.

I can already create activeforms that filter with MDX to get a subset based on an attribute, but can't quite see how to get a table like report of all attribute values.

So far I've considered creating single dynamic subsets based on the attributes and using those in the DBRWs, or using a TI to create another consolidation hierarchy on the dimension and report off that hierarchy.

Anyone have any other cunning plans for this situation?

Cheers,

Mike

[gtonkin]

Why not creating a Reporting hierarchy in you Projects Dimension?
Create a Total with children based on the unique list of attributes then assign each project to its parent in this hierarchy using TI. Just remember to flatten and rebuild this hierarchy as and when required.

The only issue with this approach is frequent changes to the attributes.

In the alternative, create the various subsets using MDX, name appropriately then reference the subset name in your DBRW to bring back the total.
The drawbacks here are lots of subsets and having to create additional ones if a new attribute is added.

I would generally go with the first option as TI can rebuild as and when required reducing manual intervention.

[Wim Gielis]

You could also add the attributes as dimensions (your "quasi-dimensions"), in the same cube or a reporting cube.
This could be useful if users want to be able to change attributes but do not want to change historical data when the attribute was different.