September 4, 2020 at 5:02 pm
I have not come across where I needed to do this, I have a report that uses multiple datsets, " I have done this before, but I need to take the value from one dataset field and subtract it from another, as you know when you bind the table to ds1 you get scope errors, I need to do something like this. (fields!xxxx.value, "ds1") - (fields!xxxxx,value,"ds2"). When I select the other dataset not bound to the table only selection I get is Sum(Fields!xxxx.value, "ds2") but I don't want the some, I want the individual field value. Any ideas? Thank you in advance.
September 4, 2020 at 6:44 pm
I think the easiest way would be to join the tables in the query, but that may not be possible. Failing that, I think your second dataset would need a LOOKUP.
Basically you are saying that column A in dataset 1 should be subtracted from column A in dataset 2. But what order should these be in? Does row 1 match up between the 2? What if SQL or SSRS reorders the data prior to presenting it for any reason? How can it know which columns should match?
That is where LOOKUP comes in. Basically lets you do a JOIN between the 2 tables to get a value.
Official docs on it - https://docs.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-2017
but basically, the first 2 arguments are the "ON A=A" part of the join, the third argument is what column you want to get, and the last one is the dataset that contains that column. This website has a good example on how to use LOOKUP:
https://www.tutorialgateway.org/ssrs-lookup-function/
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply