Saturday, February 25, 2012

Problems with Aggregate Expressions using Scope

Hi, I'm having the following problem when creating a matrix report. I'm trying to get the number of companies contacted per person to display in each cell. I have 3 group rows,

The first group, grpPerson =Fields!PersonID.Value
The second, grpCompany =Fields!CompanyID.Value with parent group = grpPerson
The thrid, grpOffice =Fields!OfficeID.Value with parent group = grpCompany

I have spent numerous hours trying different combinations of adding row groups, adding column groups, tried every possible combenation of aggregate functions, and scops, each give me different results, neither of them are what I am expecting.
Below is an example output of the following expression that I have in my data cell:
=Count(Fields!PersonID.Value, ("grpCompany")

My expected result is the number of rows per person, for the company for each month. i.e person1 called company1 5 times in the month of Feb. So each office should show the same some in the Month of Feb06. I will use this sum later to get the percentage of offices that the person called each month, and this count will alow me to do that.

However, the result below is what's being displayed. It appears the show, in the top row for each company, the number of companies the person called, not filtered by month. Then for the other rows of the company, it's showing the total number of rows returned from the whole dataset. I have tried just about every aggregate function possible. Any help to solve this problem would be great! Thanks!

Feb06 | Mar06 | Apr06 | Total
person1 | company1 | office1 20 20 20 500
| office2 500 500 500 500
| office3 500 500 500 500
person2 | company1 | office1 15 15 15 500
| office2 500 500 500 500
| office3 500 500 500 500

I’m also trying to do this using one dataset without using an aggregate within the SQL query that returns the number of times a person contacted a company each month because it significantly slows it down. I'm trying to do this all within reporting services. Any help to solve this problem would be great! Thanks!
Have you tried just =Count(Fields!PersonID.Value)? When you specify a explicit scope in the aggregate, e.g. grpCompany, it will return the sum for that group, regardless of the month in this case.|||Yes, I have tried Count(Fields!PersonID.Value), and it will give me the correct sum for the specific office of the company for the month, not the company count as a whole for the month. Is this even possible?
|||In that case, you can add a subtotal to the Office row group (right click on the row group and select subtotal). Then in the cells under the subtotal row, you should get the company count for the month.|||Thanks for your help so far, but that will give me the total number of office rows in the query. I'm looking to retrieve the number of rows the query returned per person. ie. person1 had 5 companies, person2 had 15 companies ect.

In addition I would like each cell for the person to be able to display the total value. Once I can do that I will be able to divide the total number of person calls by the number of times a person called an office,

No comments:

Post a Comment