ssrs grouping on aggregate

Question

I have an existing SSRS report that shows some connections between projects and goals/ subgoals. The report data looks like:

Project      Goal     Subgoal
A             g1         1.1
A             g1         1.2
A             g2         2.1
B             g1         1.1
B             g1         1.2
C             g2         2.2
C             g3         3.1
C             g3         3.2
D             g1         1.1

What I need is a distinct count of projects(column 1), but based on the number of goals(Column 2) they are connected to. The expected result for this example would be another table:

Number of Goals          Count of Projects
1                                 2              (Projects B and D have 1 goal)
2                                 2              (Projects A and C have 2 goals)
3                                 0              (none of the projects have 3 goals)

I have tried grouping projects by CountDistinct(Fields!Goal.Value), but that is not allowed in SSRS. I also tried counting projects that have more than 1 goal associated:

= CountDistinct(IIF(CountDistinct(Fields!Goal.Value) > 0, Fields!Project.Value,Nothing))

Is it possible to add another dataset and develop a query for that one that queries the existing dataset? Not sure if that can be done.

Answer 1

This is naive way in T-SQL to get the dataset you need, I am sure about there is a more efficient way of doing but this was what first came to my mind, hope this can help you.

Use this query over the first table you put in your question.

WITH cte_countGoals
AS (SELECT
  Project,
  COUNT(DISTINCT Goal) TotalGoals
FROM Test --Your table name
GROUP BY Project)
SELECT DISTINCT
  a.TotalGoals,
  b.TotalProjects
FROM cte_countGoals a
INNER JOIN (SELECT
  TotalGoals,
  COUNT(TotalGoals) TotalProjects
FROM cte_countGoals
GROUP BY TotalGoals) b
  ON a.TotalGoals = b.TotalGoals

You have to change the Test table to the name of your table.

It will return a table like this:

I've added another project with three different goals.

Go to this SQL-fiddle to try it by yourself