Solved

Top N filter for an MDX DataSet in Reporting Services

Posted on 2004-08-18
3
735 Views
Last Modified: 2008-01-09
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_Description">
          <DataField>[Category].[Category Description].[MEMBER_CAPTION]</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="Measures_Amount">
          <DataField>[Measures].[Amount]</DataField>
        </Field>
      </Fields>
      <Filters>
        <Filter>
          <FilterExpression>=RowNumber()</FilterExpression>
          <Operator>TopN</Operator>
          <FilterValues>
            <FilterValue>2</FilterValue>
          </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_Category_Description.Value</FilterExpression>
          <Operator>TopN</Operator>
          <FilterValues>
            <FilterValue>2</FilterValue>
          </FilterValues>
        </Filter>

0
Comment
Question by:lentzi
3 Comments
 

Author Comment

by:lentzi
ID: 11900501
Found the answer on Microsoft's forum, but I will leave it here for the others:
<Filter>
          <FilterExpression>=Fields!Category_Category_Description.Value</FilterExpression>
          <Operator>TopN</Operator>
          <FilterValues>
            <FilterValue>=2</FilterValue>
          </FilterValues>
        </Filter>
0
 
LVL 1

Accepted Solution

by:
DarthMod earned 0 total points
ID: 12142695
Submitted to PAQ with points refunded (500)

DarthMod
Community Support Moderator
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now