I'm trying to figure out if there is any way in Report Builder 3.0 to concatenate or merge 2 different "DataSet" objects which both have exactly the same column names into one set of data, for the purposes of displaying in a pie chart.
My SQL query looks similar to this for both data sets:
DECLARE @DateCreatedStart date = GETDATE()-14
DECLARE @DateCreatedEnd date = GETDATE()
SELECT i.Source, COUNT(*) AS Count_of_Source
FROM [DBName].[dbo].[Items] i
WHERE (i.DateCreated BETWEEN @DateCreatedStart AND @DateCreatedEnd)
GROUP BY i.Source
So I end up with 2 data sets which both have Source and Count_of_Source columns.
Edit: I've found out that I can't use the Lookup function how I thought I might be able to. It just looks in one dataset and finds a match in another dataset, and pulls a value from the same row in a similar manner to Excel's vlookup/hlookup.
Edit more info: I found this answer https://mcmap.net/q/1211161/-use-2-data-sets-in-one-chart-in-ssrs that showed me I could use a scope parameter on my expression to specify the name of my second data set. I added a second values member that uses an expression =SUM(Fields!Count_of_Source.Value, "DataSet2"), but the chart looks completely wrong and I don't know how to fix it at the moment. It looks like the single row from my second data set is being repeated n times, where n is equal to the number of unique Source values from my first data set. No idea what is going on there.