consolidating member

Is it possible to add an extra member that consolidates all other members. For example, I'm looking at totals by department and account. I need an extra department that sums by account across all other departments. Like this:

Dept A

Account 1 $10

Account 2 $12

Dept B

Account 1 $15

Account 2 $11

Consolidated(A + B)

Account 1 $25

Account 2 $23

Assuming that Account and Department are separate dimensions, doesn't the "All" member, at the root of the Department hierarchy, fulfill this requirement - if not, could you explain why in some more detail?|||Yes, the "all" member does contain the data I need. My problem is in generating the final printed report. I guess I need to think about solving the problem with the reporting tool. I'm using reporting services. When I get to the end of a department, I need to display data from the "all" member which consolidates all sub departments. Then, when I get to the end of all departments, I need to display consolidating data for all departments.|||

Reporting Services does indeed present problems when dealing with an "All" member. This MSDN paper - though based on SQL Server 2000 - provides some guidance :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

>>

Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios.

...

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

...

>>