I think I've found a bug in SSRS or maybe I'm missing something.
Basically if I use a lookup in a text box between two charts, the legend values in the second chart are incorrect, the lookup function will not find a unqiue match but that 'should' be OK as the documentation says it will return the first value. In fact the textbox that uses the lookup is returning what I would expect, it's the chart that immediately follows it that goes wrong.
I'll start with the result and then briefly cover how to reproduce this.
In the image below you will see 2 pie charts, the first is filtered (via the Chart filters) on Chart =1 and the second on Chart =2 . They both get their data from the same dataset (dsCharts), the results of which are shown in the table below the charts.
On the second chart I added some more values to the legend so I can see exactly which record is being shown but this was only after I spotted the issue, the default legend properties still show this issue.
This was built using VS2015 and SSRS 2016. The deployed version also shows the same bug so it's not just a Visual Studio thing.
The top legend entry in the 2nd chart should show "Mega brand" but it's showing the last entry from the chart above. The actual pie data is correct (48%)
The green text boxes use a lookup and, if the lookup does not get a unqiue value this issue occurs.
If I move the text box below the 2nd chart it works as expected.
The LOOKUP function is as follows..
=LOOKUP(2, Fields!Chart.Value, Fields!Title.Value, "dsCharts")
As you can see this will not return a unique value (that's OK though andf the correct value is being returned).
If I change the lookup to return a unique value like this...
=LOOKUP(3, Fields!Chart.Value + Fields!rnk.Value, Fields!Title.Value, "dsCharts")
The everything works. (Above is just adding chart and rnk colums so three will always get the first title from the chart 2 rows)
In summary: if I move the text box below the 2nd chart it works just fine. If I change the lookup to get a unique value it also works fine.
THE QUESTION
Am I missing something here? This certainly does not feel like a 'by-design feature".
The link below is the RDL in case anyone wants to test it. (You'll need to change the connection info to an actual server/database but other than that it should just work.
https://1drv.ms/u/s!AvWOxxoIjY1UlxJBS7uwiAc4godK
Here's the SQL to create the dataset dsCharts in case you want to start from scratch.
DECLARE @t TABLE (CHart int, SegID int, Caption varchar(50), PeriodID int, PeriodDesc varchar(10), Title varchar(50), Others int, Amount float, rnk int, PcShare float)
INSERT INTO @t VALUES
(1, 4, 'Mega Brand', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 0, 2641.02756210156, 1, 0.4486715547),
(1, 5, 'Big Brand', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 0, 887.87929700944, 2, 0.1508375718),
(1, 311, 'Average Brand', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 0, 405.48776698076, 3, 0.0688863794),
(1, 3, 'Mediocre Brand', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 0, 399.3852463092, 4, 0.0678496513),
(1, 297, 'Some other brand', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 0, 264.67433915592, 5, 0.0449642589),
(1, 148, 'Cheap stuff', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 0, 342.83011182646, 8888, 0.0582417697),
(1, 141, 'Others', 2017000, '2017', 'Top 5 Brands Volume Share, 2017', 1, 945.042844878088, 9999, 0.1605488138),
(2, 4, 'Mega-Brand', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 0, 4231.13539284372, 1, 0.4825376523),
(2, 5, 'Big-Brand', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 0, 1311.73933399895, 2, 0.1495966354),
(2, 311, 'Average Brand', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 0, 675.765818425164, 3, 0.077067364),
(2, 3, 'Mediocre Brand', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 0, 561.11919104568, 4, 0.0639925485),
(2, 286, 'Smaller brand', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 0, 398.869530857333, 5, 0.0454888698),
(2, 148, 'Cheap stuff', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 0, 398.733393643842, 8888, 0.0454733441),
(2, 141, 'Others', 2017000, '2017', 'Top 5 Brands Value Share, 2017', 1, 1191.14560398701, 9999, 0.1358435857)
SELECT * FROM @t
To reproduce the layout must be as in the images above, the lookups in the two text boxes are as stated above but the first one uses 1 as the lookup value (1st function parameter) instead of 2.
