Search code examples
sqldynamicreporting-serviceschartsreportbuilder3.0

Dynamically adding additional bars to bar chart in SQL report builder

I'm trying to create a bar chart in Report Builder 3.0 with multiple series. The stored procedure for it uses a dynamic pivot and outputs additional columns if more series are added to the data. I'd like these additional columns to appear as additional sets of bars in my chart, but I'm unsure of how to approach this problem and haven't had much success googling for an answer.

Thanks for any help.

Solution

  • SSRS does not like datasets where the columns change. It relies on a fixed structure.

    The way to approach this is to NOT pivot your data but let SSRS aggregate it in the chart. If you also need a pivoted table of data then just use a standard matrix control and that will do the job for you.

    As an example I created a test data set like this.

    DECLARE @t TABLE (SeriesName varchar(10), CategoryName varchar(10), DateYear int, Amount int)

INSERT INTO @t VALUES
('Fruit', 'Apple', 2015, 10),
('Fruit', 'Apple', 2016, 12),
('Fruit', 'Apple', 2017, 14),
('Fruit', 'Orange', 2015, 8),
('Fruit', 'Orange', 2016, 10),
('Fruit', 'Orange', 2017, 12),
('Vegetable', 'Carrot', 2015, 7),
('Vegetable', 'Carrot', 2016, 6),
('Vegetable', 'Carrot', 2017, 5),
('Vegetable', 'Lettuce', 2015, 15),
('Vegetable', 'Lettuce', 2016, 14),
('Vegetable', 'Lettuce', 2017, 13)

select * from @t

    Then a just created a simple column chart with DateYear and SeriesName as the Series groups and CategoryName as the Category Group. Then added Amount as Values.

    The design looks like this... enter image description here

    And the output looks like this,

    enter image description here

    This may not be exactly what you need but hopefully will point you in the right direction.