Aggregation with nestged groups in SQL Server Reporting Services

0
votes

Let's say we have the following data structure in SSRS report:

SSRS data structure

Disctricts are grouped by city and cities are grouped by country. And then at the country level I'd like to get SUM of population over cities (population is an attribute of a city and districts do not have population associated with them). At the moment, when aggregation is done with =SUM(Fields!city_population.Value) expression, population for each city is taken into account as many times as the number of districts it has associated - that is not correct.

I was thinking of getting data for summation from a supplementary dataset not having division of cities by districts (with the help of LookupSet function). But it probably won't support another grouping level of countries by year which is planned to be done further (can't figure out what can be used as a lookup key in such layout). Is there a way around this situation without lookups?

Update: It needs to be mentioned that City-District is a single entity corresponding to "city district or just the city if it has no districts". This entity was created in data source view as a named query using LEFT JOIN (an approach discussed in this question).

2
reporting-servicesaggregatessrs-2008-r2reportbuilder3.0

2 Answers

0
votes

It seems you don't have the right grouping settings in the tablix, a Lookup function is not required in this case.

Add a new tablix component to your report, drag and drop Country field to the Row Groups Pane above details default group.

enter image description here

Right click the Country column and select Insert Column / Inside Group:

enter image description here

In the created column type the header title and use the below expression 

=SUM(Fields!city_population.Value)

Then drag and drop City field above details and below Country:

enter image description here

Finally drag and drop District field to right most column:

enter image description here

Let me know if this helps.

0
votes

From what I have read I think you are saying that as population is recorded against City, if a City has say 3 districts, then your dataset would return the City 3 times each with the same population. If this is the case and if the Expression for population is currently SUM(Fields!Population.Value) or similar, try changing it to Max(Fields!Population.Value). It's not a 'pure' solution but I think the results will be what you want and it's a quick fix.