Dynamic columns in SSRS which depends on the data in SQL Server?

Question

I have two tables as below.

Vehicle  Brand    Acquired Year 
A001     Toyota    2018            
A002     BMW       2019
A003     Honda     2019

Vehicle     Maintenance Year     Maintenance Cost
A001            2018                5000
A001            2019                7000
A001            2020                3000
A002            2020                8000
A003            2019                3000
A003            2020                2000

I would like to have a report like this:

Vehicle     Brand     Acquired Year     2018     2019     2020     
A001        Toyota        2018          5000     7000     3000
A002        BMW           2019           0         0      8000
A003        Honda         2019           0       3000     2000

Where the columns after the first three columns are dynamic and depends on the Maintenance Year in table 2. The data for those columns are the Maintenance Cost for the corresponding Maintenance Year.

Can someone provide some guides on this?

Answer 1

I recreated your sample data with the following dataset query.

DECLARE @v TABLE(Vehicle varchar(10), Brand varchar(10), [Acquired Year] int)
DECLARE @m TABLE(Vehicle varchar(10), [Maintainence Year] int, [Maintainence Cost] int)

INSERT INTO @v VALUES
('A001', 'Toyota', 2018),
('A002', 'BMW', 2019),
('A003', 'Honda', 2019)

INSERT INTO @m VALUES
('A001', 2018, 5000),
('A001', 2019, 7000),
('A001', 2020, 3000),
('A002', 2020, 8000),
('A003', 2019, 3000),
('A003', 2020, 2000)

SELECT 
    v.Vehicle, v.Brand, v.[Acquired Year], m.[Maintainence Year], m.[Maintainence Cost]
    FROM @v v 
    JOIN @m m on v.Vehicle = m.Vehicle

This gives us 6 rows in total to the output from the dataset looks like this..

Vehicle Brand   Acquired Year   Maintainence Year   Maintainence Cost
A001    Toyota  2018            2018                5000
A001    Toyota  2018            2019                7000
A001    Toyota  2018            2020                3000
A002    BMW     2019            2020                8000
A003    Honda   2019            2019                3000
A003    Honda   2019            2020                2000

Note: We do NOT need to do anything dynamic here, the report's matrix control will do that for us, if the number of years increases, so with the number of columns in the report without us having to do anything.

Create a new report. Add a new dataset and set the dataset query to the script above or something that gives you the same layout of results from your actual data.

Add a matrix control to your report then

• Drag Vehicle from your dataset field list to the 'Rows' placeholder
• Drag Maintenance_Year to the 'Columns' placeholder
• Drag Maintenance_Cost to the 'Data' Placeholder

• Next, right-click the Vehicle text box and choose "Insert column => Inside Group - Right", then click the new cell and choose 'Brand' from the list

• Repeat with Acquired Year. When you do this, as Year is numeric, SSRS will add a SUM expression, you need to change this. Right click the expression and change it from

=SUM(Fields!Acquired_Year.Value) to =FIRST(Fields!Acquired_Year.Value)

The final design should look like this.

When we run the report we get this.