lentzi
asked on
Top N filter for an MDX DataSet in Reporting Services
Reporting Services: Can you please tell me how do I use the filter option from the DataSource tab in order to retrieve the top N records from the dataset.
I tried several ways, see below, none of them work, I keep getting the error that "cannot evaluate filterx expression/filter values".
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Category_Category_De scription" >
<DataField>[Category].[Cat egory Description].[MEMBER_CAPTI ON]</DataF ield>
<rd:TypeName>System.String </rd:TypeN ame>
</Field>
<Field Name="Measures_Amount">
<DataField>[Measures].[Amo unt]</Data Field>
</Field>
</Fields>
<Filters>
<Filter>
<FilterExpression>=RowNumb er()</Filt erExpressi on>
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>2</FilterValu e>
</FilterValues>
</Filter>
</Filters>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>select
{[Measures].[Amount]} on columns,
{[Category].[Category Description].Members} on rows
FROM
Budget</CommandText>
</Query>
</DataSet>
or with:
<Filter>
<FilterExpression>=Fields! Category_C ategory_De scription. Value</Fil terExpress ion>
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>2</FilterValu e>
</FilterValues>
</Filter>
I tried several ways, see below, none of them work, I keep getting the error that "cannot evaluate filterx expression/filter values".
<DataSets>
<DataSet Name="FoodMart_2000">
<Fields>
<Field Name="Category_Category_De
<DataField>[Category].[Cat
<rd:TypeName>System.String
</Field>
<Field Name="Measures_Amount">
<DataField>[Measures].[Amo
</Field>
</Fields>
<Filters>
<Filter>
<FilterExpression>=RowNumb
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>2</FilterValu
</FilterValues>
</Filter>
</Filters>
<Query>
<DataSourceName>FoodMart 2000</DataSourceName>
<CommandText>select
{[Measures].[Amount]} on columns,
{[Category].[Category Description].Members} on rows
FROM
Budget</CommandText>
</Query>
</DataSet>
or with:
<Filter>
<FilterExpression>=Fields!
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>2</FilterValu
</FilterValues>
</Filter>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
<Filter>
<FilterExpression>=Fields!
<Operator>TopN</Operator>
<FilterValues>
<FilterValue>=2</FilterVal
</FilterValues>
</Filter>