下面的查询:
EVALUATE
CALCULATETABLE(
'Product Subcategory',
'Product Category'[Product Category Name] = @Category )
要注意,支持参数查询的有:
- OLE DB for OLAP MSOLAP driver with ADOMD.NET library
- OLE DB for OLAP MSOLAP driver with ADOMD library
- OLE DB MSOLAP driver with ADO connection
- XMLA Execute Command
半支持的有:
- Reporting Services with Analysis Services driver -支持使用DMX编辑器,而且必须手动插入参数。不支持REPORT BUILDER
- Reporting Services with OLE DB driver - 可以替代的连接字符为:例如,CONNECT TYPE: OLEDB CONNECT STRING: PROVIDER=MSOLAP;DATA SOURCE=LOCALHOST\TABULAR;CATALOG=ADVENTUREWORKDW;COMMAND TEXT:= "EVALUATE CALCULATETABLE(
'Product Subcategory',
'Product Category'[Product Category Name] = """ & Parameters!Category.Value & """ )"
不支持的有:
- OLE DB MSOLAP driver with ADO.NET connection
XMLA的例子:
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Statement>
EVALUATE
CALCULATETABLE(
'Product Subcategory',
'Product Category'[Product Category Name] = @Category )
</Statement>
</Command>
<Properties>
<PropertyList>
<Catalog>AdventureWorks Tabular Model SQL 2012</Catalog>
</PropertyList>
</Properties>
<Parameters>
<Parameter>
<Name>Category</Name>
<Value>Bikes</Value>
</Parameter>
</Parameters>
</Execute>
ADOMD使用C#的例子:
using System;using Microsoft.AnalysisServices.AdomdClient; class Program { static void Main(string[] args) { string connectionString =@"Provider=MSOLAP;Data Source=localhost;Catalog=AdventureWorks Tabular Model SQL 2012"; string queryString = @"EVALUATECALCULATETABLE( VALUES('Product Subcategory'[Product Subcategory Name]), 'Product Category'[Product Category Name] = @Category )"; AdomdConnection connection = new AdomdConnection(); connection.ConnectionString = connectionString; connection.Open(); AdomdCommand cmd = new AdomdCommand(queryString); cmd.Parameters.Add("Category", "Bikes"); cmd.Connection = connection; using (var reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader[0]); } } }}