Hi

I am not sure if this is possible or not, we have two datasets and each one has an additional calculated field which breaks the value JobBilledExVAT into three years.

So it reports for example how much we billed in May 2015, 2014 and 2013.

I have got the table to display this when reporting on one dataset by grouping by year

The datasets both look similar to this 

SELECT
  Practice.ibvSalesByJob.JobBilledExVAT
  ,Practice.ibvSalesByJob.[Year]
FROM
  Practice.ibvSalesByJob
  INNER JOIN Practice.idvJobType
    ON Practice.ibvSalesByJob.JobType = Practice.idvJobType.VisibleId
WHERE
  Practice.idvJobType.VisibleId IN (@ABSJobs)
  AND Practice.ibvSalesByJob.ClientCategory1 = N'NC'
  AND Practice.ibvSalesByJob.Period = @Period
  AND Practice.ibvSalesByJob.[Year] >= @Pre2Year

The calculated field is 

=Switch(Fields!Year.Value = Parameters!Year.Value ,"15", Fields!Year.Value = Parameters!Pre1Year.Value ,"14", Fields!Year.Value = Parameters!Pre2Year.Value ,"13")

The field I am looking to add together is below

=Sum(Fields!JobBilledExVAT.Value)

If I do the below on the dataset associated with the matrix it displays the sum for each year correctly, I would however like to add the Sum(Fields!JobBilledExVAT.Value) from a different dataset (ABS1M) for the relevant year.

I tried this

=Sum(Fields!JobBilledExVAT.Value) + Sum(Fields!JobBilledExVAT.Value, "ABS1M")

However this adds 2015, 14 and 13 from ABS1M and doesn't add them individually if that makes sense?

With the calculated field it only shows Year under the main dataset heading not 15, 14 and 13 separately.

I was think of using LOOKUP however I don't know how this would work as 15, 14 and 13 are not displayed under the dataset.

Does anybody know of a way around this?  Any advice appreciated.

Hi Cheers,

Per my understanding that have create two matrix which data comes from two datasets and now you want to sum the value "JobBilledExVAT" based on the group level from this two different matrix, right?

I have tried to analysis about the requirements but still need more information, I assumed you want to sum the result like below:
=Sum(Fields!JobBilledExVAT.Value"Columngroup1") + Sum(Fields!JobBilledExVAT.Value, "Columngroup2")
Matrix1
          13  14  15
row1    1    2    3
row2    2    3    4
Sum    3     5    7

Matrix2
          13  14  15
row1    4    5    6
row2    7    8    9
Sum    11  13  15

So want you want is the get the sum total as below
                    13      14     15
SumTotal:  3+11   5+13   7+15

If the query result from the two dataset have an unique relationship field, we can use the lookup function to achieve this or we can modify the query to join the record and get the sum total.

Please try to provide snapshot of the two matrix, sample data from the table, relationship between the two matrix and also the expect result you want to achieve to help better analysis about the requirements.

Regards,
Vicky Liu

Hi Vicky

Sorry for the delay but thanks for taking the time to look into it.

Basically both datasets have the same fields so a lookup should hopefully be possible.

Both report JobBilledExVAT and Year but also have a calculated Month field which splits the JobBilledExVAT into years so 13, 14 and 15.

Below is the matrix's shown separately

Matrix 1 

Year | JobBilledExVAT

13 | 2,000

14 | 4,000

15 | 6,000

Matrix 2 

Year | JobBilledExVAT

13 | 3,000

14 | 5,000

15 | 7,000

I currently use =Sum(Fields!JobBilledExVAT.Value) to bring through the sum total for each year.

I would like to merge them so it showed as follows

Merged Matrix

Year | JobBilledExVAT

13 | 5,000

14 | 9,000

15 | 13,000

Hope that helps, sorry for the delay in getting back it was my birthday yesterday so was off work with the family.

Cheers

Just wanted to report I have managed to sort it with the following

=Sum(Fields!JobBilledExVAT.Value) + LOOKUP(Fields!Year.Value,Fields!Year.Value,Fields!JobBilledExVAT.Value,"ABS1M")

Thanks for your help with this, appreciated.

First of all, a column is nothing like field. But that tells us you do not think in SQL yet and miss 1950's COBOL. Then you have un-normalized data; a column is atomic, so cramming three years into it violates 1NF (First Normal Form). YEAR is a reserved word and a violation of ISO-11179; this is called an attribute property and has to be <something>_year&