question

CamiP-9930 avatar image
0 Votes"
CamiP-9930 asked ZoeHui-MSFT commented

SSRS MATRIX Reference Total from one Dataset to a second dataset

I have a MATRIX report that has two datasets. In dataset one, there is a total line I named "GM". In dataset two, the total line is named "OE". Under OE, I want a calculation that takes GM-OE. Because it was not calculating correctly, I changed the line under OE to just give me ReportItems!GM.value to see what value was getting filled there, it is using the last column total for the value in each column before it. I want JAN GM to reference JAN line under OE, not MAY's GM value. Any ideas??

See screenshot:

104471-image.png


sql-server-reporting-services
image.png (190.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

ZoeHui-MSFT avatar image
0 Votes"
ZoeHui-MSFT answered ZoeHui-MSFT commented

Hi @CamiP-9930,

I'd suggest that you may deal with the data with t-sql before design the report to get the total GM and OE vaule.

Suppose that the DataSet2 also have the month value.

You may add the third matrix with month.

Use lookup function to calculate GM-OE and refer the correct month like shown.

 =Fields!GMtotal.Value-lookup(Fields!month.Value,Fields!month2.Value,Fields!OEtotal.Value,"DataSet2")

Hope it could give you some ideas.

Regards,

Zoe


If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October




· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

If you have any updates, don't forget to tell us.

0 Votes 0 ·