Problem...

I have 2 row groups

Year and QTY

for every Year (2003,2004 etc) there are a number of cities with their own QTY

For instance

City1 has QTY = 20 for Year 2003

City2 has QTY = 30 for Year 2003

When I sum only one City1 I want the column QTY to show 20 for Year 2003 and to show 50 if both City1 and City2 are selected.

The problem is when I sum let say City 1 it shows me 100 instead of 20 because the column group consists of 5 rows in dataset query with QTY 20, so if I sum 2 cities of the same Year it will give me 100(City1) + X(City2).   

Did it make sense or do you need more input?

I think I know what you mean, a snippet of data will always help.

Sounds like you have got your group by and where clauses wrong...

I get too much data from result set but it is needed though since I need the column field Activities.

As you can see on the image I can't get the total of each city. I want it to be 240 for Year 2006 on the Qty column. The expression for Qty column is Fields!Quantity_Number.Value. If I do it like Sum(Fields!Quantity_Number.Value) it will give me 485(97*5 since there are 5 rows in dataset with 97 because of Activities)  if only first city is selected from City Parameter dropdown and 1200(97*5 + 143*5) if 2 cities are selected as image

Hi Jhonny86,

I think what you have is probably correct in the report, however your source data is not perfect for what you want.

Can you edit the source data to remove the duplication (something like select distinct)? In this case, the activities will be doing nothing for your data set.

Regards,

• Proposed as answer by Qiuyun YuMicrosoft contingent staff, Moderator 1 hour 2 minutes ago

Hi Jhonny86,,

According to your description, you want to sum the [Qty] values for based on the selected values in the parameter.

In your scenario, you can use DISTINCT keyword to return only distinct (different) values in query designer. Then create a tablix group on [Year] and create a city parameter. Please refer to screenshots below:

If report deisgn above doesn't meet your requirements, please share sample data and expected results for our analysis.

If you have any question, please feel free to ask.

Best regards,
Qiuyun Yu