[SOLVED] SSAS adding a custom attribute member to a cube for reporting

SSAS adding a custom attribute member to a cube for reporting

We have a cube with many measures and one of the main dimensions is a 'site' dimension with about a dozen sites.

I've been asked to add a 'site' that is basically ALL the sites.

They don't want the actual hierarchy,

I tried defining the ALLMEMBER attribute but they don't want another hierarchy level, they want an actual attribute member at the same level. They want the whole displayed as another site next to the actual site values

Example:

Right now with the all member attribute I can show

All Sites - 140
  +
    New York   45
    Chicago    60
    Boston     35

But what they want is this:

New York   45
Chicago    60
Boston     35
All Sites  140

I'm not sure where to go from here in BIDS. It's not really a named dataset, nor a calculated measure. It's really a custom attribute member.

Solution

You can define a calculated Dimension member in your cube calculation script.

You'd need something like: Create member Site.site.AllSiteCalc AS SUM(Site.site.children,Measures.currentmember)

Once you've processed the cube this should become available in ssms,excel or to use in a query