Hi, I have a stacked bar chart showing my values by geographical region. Within each region I have x-number of DataAreaIDs. I want the user to be able to see the distinct DataAreaIDs via a mouse over on each bar. I therefore entered this formula in the tooltip option for the regional bar but it only displays #Error: "=join(Fields!DataAreaID.Value,", ")". I appreciate your help! LSM

Hi LSM, Join function cannot be used to concatenate field value from dataset, Fields! DataAreaID.Value returns all values for that row rather than an array of values. This is the root cause of your scenario. I suggest you using T-SQL to add a new column to show multiple DataAreaID for each region in your dataset, and then type in this expression in the tooltip for the region bar: Fields!ColumnName.Value If you have any question, please feel free to ask. Thanks, Eileen

Hi Eileen, Thanks for your reply which would bring me some of the way. My only problem is that sometimes the user has not selected all the possible DataAreaIDs within a region, so then the info box should only show the selected items within that region of course. Regards, LSM Lars Schott Madsen

Hi LSM, Is the DataAreaIDs as a parameter passed to the report? In this case, you can use Join function like following: "=join (Parameters!DataAreaID.Value,", ")". If you have any question, please feel free to ask. Thanks, Eileen

Hi Eileen That will not work entirely - I was not clear enough in my description. The dimension on the x-axis and therefore the key to splitting the values into seperate bars is the region-dimension. Therefore - if the user selects x-number of regions those DataAreaIDs appering in the tooltip for each bar should only be those DataAreaIDs that belong to the specific region. Using the parameter selection on DataAreaIDs will show the same list for each bar. I would love to upload an image illustrating the issue but I can't figure out how - sorry. Regards, LSMLars Schott Madsen

Hi LSM, Based on my understanding, each stacked bar in the Chart should have different tooltips set to differernt DataAreaID which associated to Regoins, and every region always has one distinguished DataAreaID associated, right? In that case, I'd suggest you develop your DataSet query to include DataAreaID from Database as well. So that, when setting the Regions to Category or Series field, we can always set the tooltips to =Fields!DataAreaID.Value. With that configured, each stack bar will have tooltips set to their own DataAreaID. If you have any question, please feel fre to ask. Eileen.

Hi, That was in fact my first attempt since DataAreaID and Region is part of the same dataset. But how will you then handle to get multible DataAreaIDs into the same string in the tooltip? Imagine that I have selected US-LR, CA-CA, CN-TIA in the list below. Now - the chart will show to bars - one for each region. The tool tip for North America should show multiple DataAreaIDs = US-LR, CA-CA. Region DataAreaID North America US-LR North America US-GF North America CA-CA China CN-TIA If I use your suggestion "fields!DataareaID.Value" I only get one (the first) DataAreaID within that region, even though multible DataAreaIDs within the Region are available and selected As you mentioned earlier "join" will not work with field values from the data set Thanks for all your patience LSM Lars Schott Madsen

Hi LSM, Thank you for the reply. Maybe, we can workaround this by creating an function to combine all DataAreaID based on Region. Please refer to the code below, CREATE FUNCTION[dbo].[GetDataAreaIDs] ( @Region NVARCHAR(255) ) RETURNS NVARCHAR(2550) AS BEGIN DECLARE@strDataAreaIDs NVARCHAR(2550) SET@strDataAreaIDs = '' SELECT@strDataAreaIDs=@strDataAreaIDs + ISNULL(DataAreaID,'') + ',' FROM DATABASE WHERE Region = @Region RETURN@strDataAreaIDs END After that, we can setup the DS as follows, SELECT Region, dbo.GetDataAreaIDs(Region) FROM [DATABASE] GROUP BY Region or SELECT Region, dbo.GetDataAreaIDs(Region) FROM [DATABASE] Let me know if you have any questions. Thanks, Eileen

Hi Eileen I learned something there from your function ! But - I don't see how this should work together with the user selection of DataAreaIDs. I reckon that the function would make the following query from the example I posted earlier: Region dbo.GetDataAreaIDs North America US-LR, US-GF, CA-CA China CN-TIA But - since the DataAreaID is a report parameter the user could run the report by selecting less than the full set of DataAreaIDs within the region - thus the tooltip for the regional bar should only show the DataAreaIDs that was actually selected (e.g. US-LR, US-GF excluding CA-CA) Please prove me wrong LSM Lars Schott Madsen

Hi LSM, Let's try follows, CREATE FUNCTION[dbo].[GetDataAreaIDs] ( @Region NVARCHAR(255), @DataAreaIDs NVARCHAR(1000) ) RETURNS NVARCHAR(2550) AS BEGIN DECLARE @strDataAreaIDs NVARCHAR(2550) SET @strDataAreaIDs = '' SELECT @strDataAreaIDs=@strDataAreaIDs + ISNULL(DataAreaID,'') + ',' FROM DATABASE WHERE Region = @Region AND DataAreaID IN @DataAreaIDs RETURN @strDataAreaIDs END Next, add a Parameter to Dataset, open Dataset, click Parameters tab, click Add button, and set the parameter name, e.g. @CombinedDataAreaIDs, set the value to expr =Join(Parameters!ReportParameterForDataAreaID.Value,",") At last, set the Dataset query like this, SELECT Region, dbo.GetDataAreaIDs(Region, @CombinedDataAreaIDs) FROM [DATABASE] GROUP BY Region Let me know if that works. Thanks, Eileen