Aggregate functions not allowed in the dataset filter

Hi,

I was trying to filter a dataset based on a condition like this: Fields!SalesAmt.value <= Sum(Fields!SalesAmt.Value)*0.05. This is nothing but it filters those SalesAmt that are less than 5% of the total sales amount. However SSRS doesn't allow to use aggregate functions in the dataset and data region filter.

Is there another way to do this?

Sincerely,

--Amde

You're trying to hide the rows correct? if so then try this

=IIF(Fields!SalesAmt.value

<= Sum(Fields!SalesAmt.Value)*0.05, true,false)
true meaning that if it is true then hid the row.

Hook this into a parameter and you have a dynamically filterable

report.

-Fap

|||Sorrt about the font size.

Here it is again.

You're trying to hide the rows correct? if so then try this

=IIF(Fields!SalesAmt.value

<= Sum(Fields!SalesAmt.Value)*0.05, true,false)
true meaning that if it is true then hid the row.

Hook this into a parameter and you have a dynamically filterable

report.

-Fap

|||

Dear Fap,

That was not my question. I just want to filter the data based on the condition I specified earlier. Please read my question carefully and let me know if you have any idea.

Sincerely,

--Amde

|||Aggregates are not supported in filter expressions (as you have seen), but there are few ways that you can work around this. Here are the first two that come to mind.

1. Filter the data returned directly in the SQL query. This can make the query more complicated. See http://msdn2.microsoft.com/en-us/library/ms179270.aspx for more information on filtering rows.

2. Use a hidden report parameter that has its default value populated by a second dataset whose query just computes and returns the result of the Aggregate. Then use the hidden report parameter in the filter expression where you were using the aggregate.

Sample Query:
SELECT Sum(SalesAmt)*.05 AS AggregateResult FROM SalesTable

Filter Expression:
Fields!SalesAmt.Value <= Parameters!AggregateResult.Value

Ian|||

Hi,

What you have said make sense, but, the thing is I am using MDX query instead of T-Sql. The link that you sent to me is helpful. It would be more helpful if you can send me similar link that uses mdx query.

Sincerely,

--Amde

|||I'm not an expert using MDX, but one way to filter the data would be to create and add another calculated member to the query, and then filter based on the result of that member. For example, create another member that will calculate whether or not the the row should be included.

The new member calculation would look something like

WITH MEMBER [Measures].[Include] AS ([Measures].[SalesAmt] <= Sum([SalesData].[SetOfAllRow], [Measures].[SalesAmt])*0.05)

Make sure to add [Measures].[Include] to the SELECT set. Then you can use this field in the data region's filter expressions. The filter expression would look something like

Fields!Include.Value = True

Or, you can use the Filter function in the query to retrive only a subset of the data. Here is more information on filtering data using MDX queries.

Filter Function:
http://msdn2.microsoft.com/en-us/library/ms146037.aspx

Slicer Axes:
http://msdn2.microsoft.com/en-us/library/ms146047.aspx