Multidimensional Expression (MDX) queries or Data Mining Prediction (DMX) queries can only be generated by a Reporting Services query designer because a result set must be retrieved as a flattened rowset. If you want to create a dataset in Report Builder 2.0, you can create a new query or you can import an existing query from a file or from another report. Only the .sql and .rdl file types are supported.
If you have two datasets in the report, the following list describes how you can use multiple datasets in a report:
- You display the data from each dataset using a separate data region. For more information, see Working with Data Regions (Report Builder 2.0).
- You can link more than one data region to a dataset and provide multiple views of the same data. For more information, see Linking Multiple Data Regions to the Same Dataset (Report Builder 2.0).
- You can use datasets to provide a drop-down list of available values or default values for a report parameter. For more information, see Adding Parameters to Your Report (Report Builder 2.0).
- You can link related data from multiple datasets by using parameters with drillthrough reports or subreports. For example, a sales report can show summary data for all stores, and a drillthrough link can specify the store identifier as a parameter to a report with a dataset query that retrieves the individual sales for the specified store. For more information, see Adding Drillthrough Reports (Report Builder 2.0) and Adding Subreports (Report Builder 2.0).
- You cannot display detail data from multiple datasets in a single data region. However, you can display aggregate or built-in function values for multiple datasets within a data region. For more information, see Using Built-in Report and Aggregate Functions in Expressions (Report Builder 2.0). If you need to combine detail data from multiple datasets into one data region, you must rewrite your query to retrieve the data as a single dataset.
If you have any more questions, please let me know.
***Xiao Min Tan***Microsoft Online Community***
- Marked as answer by Xiao-Min Tan – MSFTModerator Monday, February 02, 2009 1:43 AM