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

5
votes

I know we can't use an Aggregate Function for a filter in a matrix.

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

  1. Category
  2. Subcategory
  3. 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 subcategory and Product the folmula for Calc_Member is as I've mentioned before:

[Measures].[Internet Average Sales Amount]

But for Category Level I want Average 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 row groups, and add total for each group. For Category Level 1 want to use an Aggregate Function in ssrs (to use Formlula that's defined in analysis service) and again it works fine.

But if I want to use a Filter on some member (e.g category, Subcategory, Product) it throw this error:

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

And this error is accepteable too, but what I dont undrestand is: if I use an Aggregate Function in Matrix1 and I use a Filter in Matrix2 it throw the error as well.

Why?

1
reporting-servicesfiltermatrixssrs-2008aggregate
It's very hard to understand your question as is. Perhaps you can edit it and add some more code samples, tell us what you've tried, why it doesn't work, etc? As it stands we can't really help you. - Jeroen
Thanks for editing. I've tried to update it some more, hopefully without changing the meaning. Still, the question is a wee bit hard to understand - probably a language barrier? Perhaps someone else can see what's going on and answer it for you... - Jeroen
Hi Jeroen Thank you very very much for following.forget my previous question .please see this if possible.put A matrix in ssrs an use Aggregate Function in it. and Then put another Matrix and Use Filter in it.after that you can see the error Thanks. - abianari
FWIW I've given up using SSRS against SSAS. I think it's a poorly conceived and executed implementation and has so many traps and gotchas like this. - Mike Honey

1 Answers

0
votes

Can you join the aggregate to the dataset and then use that as a filter? (apparently i need 50 reputation to comment)

for example: 

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