Reporting Services - Professional SQL Server Analysis Services 2005 with MDX - page 913

tion functions such as sum, count, distinct count, etc. For a detailed list of the
aggregation functions supported by Reporting Services 2005 please refer to
the product's documentation. In addition to these aggregate functions, it sup-
ports custom aggregates supported by data providers. If a data provider such
as Analysis Services supports custom aggregates then Reporting Services
has the ability to retrieve that data for the aggregate rows by the aggregate
function called Aggregate. Custom Aggregates are also referred to as Server
Aggregates.
Examples of custom aggregates in Analysis Services 2005 are semi-additive
measures which use aggregate functions such as ByAccouunt,
FirstNonEmpty, LastNonEmpty, FirstChild, LastChild, AverageofChildren,
None. Some of the custom aggregate functions supported by Analysis Ser-
vices 2005 are also supported by Reporting Services 2005. However we re-
commend you use custom aggregates for improved performance; in this way
Reporting Services does not have to calculate the aggregate once again, for
that was already done by Analysis Services. In this section you create a re-
port that uses customer custom aggregate function to retrieve semi-additive
measures from Analysis Services 2005. Follow the steps below to generate a
custom aggregate report.
1. Add a new report item to the Reporting Services project you used in
the last section called CustomAggregate.rdl.
2. Switch to the Data tab and create a new connection to the database
AnalysisServices2005Tutorial.
3. The measure Fact Internet Sales - Unit Price in the cube Adven-
tureWorksDW is a semi-additive measure with aggregate function
FirstNonEmpty. Drag and drop this measure from the Metadata pane
to the Result pane. Drag and drop the hierarchy Products from the
Product dimension and Calendar Year hierarchy from the Order Date
dimension. Your Result pane should now include columns Calendar
Year, the two levels of the Products hierarchy Model Name and
Product Name, and the measure Fact Internet Sales - Unit Price as
shown in Figure 17-33 .