• Article

Creating Report Datasets from SQL Server Analysis Services

Reporting Services provides a data processing extension that supports report data retrieval from a Microsoft SQL Server Analysis Services data source. This data processing extension retrieves dimensions, hierarchies, levels, Key Performance Indicators (KPIs), measures, and attributes from a Microsoft SQL Server Analysis Services cube or metadata from Data Mining models for use as report data.

After you have connected to an Analysis Services data source, you can define a dataset that specifies the report data to retrieve during report processing by using either Multidimensional Expression (MDX) queries or Data Mining Prediction (DMX) queries. You can use an MDX graphical query designer or a DMX graphical query designer to build a query by browsing and selecting from the underlying data structures on the data source. When Reporting Services first connects to a Microsoft SQL Server Analysis Services data source, if it detects a valid cube, the default query designer is MDX. If there is no valid cube, but there is a valid Data Mining model, the default query designer is DMX. After you build a query, you can run the query to see sample data in the results pane. The query results are automatically retrieved as a flattened rowset, ready to be used as report data.

The query designer analyzes the query to determine the dataset fields. You can also manually edit the dataset field collection in the Report Data pane. For more information, see How to: Add, Edit, or Delete a Field in the Report Data Pane.

The text-based query designer is read-only for this data source. You cannot import .mdx or .dmx files and use them directly in a query. Result sets from Analysis Services cubes are retrieved through the XML for Analysis (XMLA) protocol as a flattened row set. Ragged hierarchies are not supported. For more information, see XML for Analysis Overview (XMLA) and Working with Ragged Hierarchies.

You can import queries from existing reports that use Analysis Services data sources. For more information, see How to: Retrieve Data from an Analysis Services Cube (MDX).

In This Section

  • Using the Analysis Services MDX Query Designer in Design Mode (Reporting Services)
    In the MDX query designer in Design mode, you can drag dimensions, members, member properties, measures, and KPIs from the metadata browser to the Data pane to build an MDX query. Drag calculated members from the CalculatedMembers pane to the Data pane to define additional dataset fields.

  • Using the Analysis Services MDX Query Designer in Query Mode (Reporting Services)
    In the MDX query designer in Query mode, you can drag dimensions, members, member properties, measures, and KPIs from the metadata browser to the Query pane to build an MDX query. You can edit MDX text directly in the Query pane. Drag calculated members from the CalculatedMembers pane to the Query pane to define additional dataset fields.

  • Using the Analysis Services DMX Query Designer (Reporting Services)
    In the DMX query designer in Design mode, you can select a data mining model, input tables, and select criteria to use to define fields for a dataset. In Query mode, you can view and edit the DMX query text directly. In Result mode, you can view the result of running the query.

  • Using Extended Field Properties for an Analysis Services Dataset
    You can use extended field properties supported by the Analysis Services data processing extension in your report through the built-in Fields collection. For properties that have values on the data source, you can access predefined property values such as FormattedValue, Color, or UniqueName.

For more information about the user interface, see Analysis Services MDX Query Designer User Interfaceand Analysis Services DMX Query Designer User Interface.

SQL Server 2000 Analysis Services

You can use the MDX query designer to write queries against a SQL Server 2000 Analysis Services database. DMX queries are not supported.

Parameters are not supported by the OLAP data provider for Analysis Services 2000.

Change History

Updated content

Ragged hierarchies are not supported.