Aggregate function "Aggregate" cannot be used in a report that contains any filters

Question

I know that we cannot use the aggregate function for a filter in a matrix.

Here is the situation in AdventureWorks. I have three levels:

• Category
• category
• Product

And 1 Calc_Member:

=With MEMBER Calc_Member as [Measures].[Internet Average Sales Amount] 

And actually:

 [Measures].[Internet Average Sales Amount]= [Measures].[Internet Sales Amount]/[Measures].[Internet Order Count] 

I want different aggregations for each level in the hierarchy. For the subcategory and folmula product for Calc_Member , as I mentioned earlier:

 [Measures].[Internet Average Sales Amount] 

But for the category level, I want the Average number of related subcategories:

 WITH MEMBER Calc_Member AS Average(existing [Product].[Subcategory].[Subcategory].members , [Measures].[Internet Average Sales Amount]) 

In Analysis Services, this works fine, but I have a report in Reporting Services based on this dataset:

• category
• subcaegory
• product
• Calc_member

In ssrs, I use a matrix with three groups of rows and add a total for each group. For category 1 level, you must use the aggregation function in ssrs (use Formlula, which is defined in the analysis service), and again it works fine.

But if I want to use a filter for a member (for example, category, subcategory, product), it throws this error:

Aggregate function "Aggregate" cannot be used in a report containing any filters

And this error is also acceptable, but what I am not doing is undrestand: if I use the aggregate function in Matrix1 and I use Filter in Matrix2, it also throws an error.

Why?

Answer 1

Can you join an aggregate to a dataset and then use it as a filter? (apparently I need 50 rep for comments)

eg:

  SELECT item, quantity, aggregateQuantity FROM TABLE T1 LEFT JOIN (SELECT item, SUM(Quantity) AS aggregateQuantity FROM TABLE) T2 ON T1.item = T2.item