0

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).

Community
  • 1
  • 1
Andrey Grachev
  • 1,259
  • 1
  • 14
  • 22

2 Answers2

0

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

halfer
  • 19,824
  • 17
  • 99
  • 186
alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • Alejandro, thank you for the answer. I have group structure matching with what you describe (it was created in reverse order starting from district then up to city and then up to country but that shouldn't matter right?). I forgot to mention that city-district is a single entity corresponding to 'city district or just the city if it has no districts' (created as a named query with left join) - I will update the question with this, maybe this is the reason for such aggregation behavior. – Andrey Grachev Feb 08 '17 at 19:40
  • @AndreyGrachev, what is the structure of your dataset. i.e, what are the columns you get when you run the query? – alejandro zuleta Feb 08 '17 at 19:55
  • the dataset is obtained from smdl model. This model has foreign key relationship between city City-District entity (a named query with left join between City and District table) and Country entity (Country table). As for column names - this is a simplified structure related the problem I'm trying to solve and the problem is actually not about cities, countries, etc. so I don't have exact column names. This is more like an abstract question - the results that I currently get actually match with my understanding of how it should work. It's just not what I'm trying to achieve. – Andrey Grachev Feb 08 '17 at 20:22
0

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.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • such an approach does work if population is displayed for each city. But I need to display sum of populations of cities for a country. Tried the following expression: SUM(MAX(FIELDS!city_population.Value,"group_city"), "group_country"). The idea was to get max value in the context of 'city' group (min or avg would work as well) and then apply summation in the context of 'country' group. But such an expression at 'country' level gives an error saying that child group context can't be applied for MAX. – Andrey Grachev Feb 10 '17 at 14:43