Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Defining Report Datasets for Analysis Services Multidimensional and Data Mining Prediction Data

Updated: 14 April 2006

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 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 using either Multidimensional Expression (MDX) queries or Data Mining Prediction (DMX) queries. The query results are converted to a flattened rowset for use in the report. For instructions about creating Analysis Services datasets, see How to: Create a Dataset (Report Designer).

There are two query designers, each with several modes. To switch between MDX and DMX modes, use the toolbar buttons Command Type MDX (Change to MDX query view) and Command Type DMX (Change to DMX query language view). To change between Design and Query modes in the MDX query designer, use the toolbar button Design Mode (Switch to Design mode). To change among Design, Query, and Result modes in the DMX query designer, right-click in the Query Design pane, and choose a mode.

When you design a report in Report Designer, you can use the graphical query designer to build an MDX or DMX query by browsing and selecting from the underlying data structures on the data source. The query designer analyzes the query to determine the dataset fields. You can also manually edit the dataset field collection through the Fields tab on the Dataset dialog box. When you view a report, the dataset fields are retrieved from the data source as the report data.

Using Analysis Services MDX Query Designer in Design Mode

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 Calculated Members pane to the Data pane to define additional dataset fields.

Using Analysis Services MDX Query Designer in Query Mode

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 Calculated Members pane to the Query pane to define additional dataset fields.

Using Analysis Services DMX Query Designer

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 Database

You can use extended field properties supported by the Analysis Services data processing extension in your report through the global 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.

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.

Release History

14 April 2006

New content:
  • Added In This Section.
Changed content:
  • Created two subtopics for Query and Design mode and moved appropriate content.
  • Updated introduction.

Community Additions

ADD
Show:
© 2015 Microsoft