Filters in Aggregation Expressions -- sql server reporting services

sql server reporting services : Filters in Aggregation Expressions

jerrynixon NO[at]SPAM gmail.com
10/12/2004 1:38:47 PM

Pretend I have a table called Employees.

I want to know how many mend and women work for me.

Something like this:

=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
=Count(IIF(Fields!EmployeeGender.Value = "F", 1, 0), Nothing)

Similarly I could get today's and yesterday's orders.

Something like this:

=Sum(IIF(Fields!OrderDate = Now, Fields!OrderTotal, 0), Nothing)
=Sum(IIF(Fields!OrderDate = Now-1, Fields!OrderTotal, 0), Nothing)

Is this possible and if not - what could I do to accomplish this?

I would prefer these two items to appear in the same TableRow.

Robert Bruckner [MSFT]
10/12/2004 9:10:40 PM

As mentioned in the other thread "Does Sum not support Nothing scope?", you
may want to omit the scope argument of the aggregate function. Depending
where (header of groupings vs. detail) you move the textbox with the
aggregate function it will get different scopes if it is omitted.
If you always want to perform the aggregate calculations within a certain
containing scope (i.e. parent groups, parent data region scopes, data set
scope) you should explicitly specify them as scope argument.

--
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

jerrynixon NO[at]SPAM gmail.com
10/13/2004 9:07:15 AM

Thank you, that is good advice and it makes sense.

However, it does not answer why the following conditional aggregate fails:

=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0), Nothing)
=Count(IIF(Fields!EmployeeGender.Value = "M", 1, 0))

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

home · blog · groups

Questions? Comments? Contact the dn