I am using Sql Server Analysis Services 2008 to build a cube. I have the following scenario and was wondering if anyone had any ideas on how to accomplish it. The cube is displayed over the web using a third party tool(Active Analysis). There is role based security so the user only see's their data. I am trying to allow the user to see their data, and also a total of all the other data. For example, the user currently only has access to see the total population of Massachusetts, but should also see a row below it with the total population of all states.
Year
States 2009 2010 2011
MA 1,000,000.00 1,050,000.00 1,102,500.00
All States(?) 365,000,000.00 383,250,000.00 402,412,500.00
You can edit the role and on the Dimension Data tab, you probably have Enable Visual Totals selected which prevents the user from seeing the total of all states. When you clear the checkbox, the user still sees only MA but the total should be all states.