1

I am working on a ssrs report with column grouping. the followin is my scenario.

Matrix 1:

ID     2012  2013
1      20    40
1      30    50

Total  50    90

Matrix 2:

ID     2012  2013
1      60    70
1      60    80

Total  120   150

I need the sum of matrix1 and matrix2 like below:

ID     2012  2013
1      170   240

But I got the result like :

ID     2012  2013
1      410   410

I have applied column grouping in all the 3 matrices and gave the expression to get sum for matrix 3 as: =Sum(Fields!amount1.Value, "dsmatrix1") + Sum(Fields!Tamount1.Value, "dsmatrix2")

Please help me to get a solution for this.

Thanks!

user2395176
  • 195
  • 1
  • 1
  • 11
  • Do you have any control on the actual Datasets that are generated, e.g. if they're coming from a database, can you update that code? – Ian Preston Sep 26 '13 at 13:24
  • Hi, I have a Parameter as year(like 202, 2013 and that used for column grouping). No other parameters, I am not sure whether I need to specify anything specific while adding column with grouping. – user2395176 Sep 26 '13 at 16:25

1 Answers1

0

I think I know what's going on. Correct me if I'm wrong.

Based on what I'm seeing, I'm guessing that Matrix 1 and Matrix 2 only have three fields each, an ID field, an amount field (being "amount1" or "Tamount1"), and a year field.

Your column grouping is manipulating the display of the data to show all values broken out by year. This works fine when looking at data from a single dataset. However, your formula is specifying that the sum of everything in the Amount1 field of dsmatrix1 and the Tamount1 field of dsmatrix2 should be added. This does not take into account the column grouping. Your expression is essentially taking all of the values from both datasets and adding them together.

Not knowing more about your query structure or how the data is filtered, my best guess is that you need another SQL dataset. In this case, you would take the queries from your two previous datasets and union them with the "Union All" command. Note that you will want to use Union All and not just Union. More on that here: What is the difference between UNION and UNION ALL?

Your end result should look something like this:

--This will be your dsmatrix1 query copied and pasted
Select ...

Union All

--This will be your dsmatrix2 query copied and pasted
Select ...

--Place one single Order by clause at the bottom
Order by ...

Note: for your two queries to be unioned properly, you'll need to make sure that each have the same number of fields, each with the same data types. Then you can point your third matrix to the new dataset.

Hope that helps!

Community
  • 1
  • 1
Christopher Brown
  • 2,170
  • 1
  • 13
  • 19