0

In SSRS 2005 I am reporting on all available posts by regional office, listed by region,office,vacancyID. I then display a total per office on how many people started in a particular vacancyID by doing a Count(VacancyStartID). In the same group row with the Count(VacancyStartID) I need to display SUM(VacancyID). However at present this does not give the correct SUM, because some vacancies have multiple VacancyStartID's and hence the vacancyID is listed few times, like so:

office vacancyID Number_of_vacancies       VacancyStartID (person who started a job)

 1         1              2                4567

 1         1              2                5678


Totals:                   4 (needs to be 2)    2

P.S. Note:These questions are not applicable in this instance: How can I remove duplicate rows?

How do I remove "duplicate" rows from a view?

Using multiple COUNTs and SUMs in a single SQL statement

Community
  • 1
  • 1
Fet
  • 728
  • 3
  • 19
  • 33

2 Answers2

1

If it's in the Underlying SQL Server call...

You can do ...SUM(DISTINCT VacancyID)... like you can COUNT (DISTINCT ..)

Edit:

SELECT
    col1, col2, SUM(DISTINCT Number_of_vacancies)  as foo, COUNT (VacancyStartID) as bar
FROM
    MyView
...

If it's in the table or for a cell in the report, then there is no equivalent in the SSRS SUM function.

gbn
  • 422,506
  • 82
  • 585
  • 676
-1

Do some grouping already in your query and then make a group with a simple Count in SSRS.

Gerrie Schenck
  • 22,148
  • 20
  • 68
  • 95