reporting services - Line Chart with multiple colors for single line and multiple icons

Question

need a Line chart as below that is filtered by Country->Customers->MachineType->MachineModel->Parts.

The Chart represents Consumed Material (In Tons) of Machines in Y-Axis and X-Axis is over time (Year-Months-Day).

My data is coming from a ML model that stores value for past 3 months and next 365 days.

1. How can I show the actual and predicted value in the same line with different colors? I can't use two lines to represent this.

2. How can I display multiple (around 100) icons ( Represented as Xs in below Picture) on the same line to represent another field value maintenance dates of Parts.

Answer 1

You can do this quite easily with a single dataset and a single line if you get the dataset right.

I've mocked up a small sample of data using the following...

This assumes two tables, one with material use and another with maintenance days

DECLARE @t TABLE(DateStamp date, Amount int)

INSERT INTO @t VALUES
    ('2021-01-01', 10),
    ('2021-01-02', 11),
    ('2021-01-03', 12),
    ('2021-01-04', 14),
    ('2021-01-05', 16),
    ('2021-01-06', 18),
    ('2021-01-07', 20),
    ('2021-01-08', 21),
    ('2021-01-09', 22),
    ('2021-01-10', 23),
    ('2021-01-11', 24),
    ('2021-01-12', 25),
    ('2021-01-13', 26)

DECLARE @m TABLE(DateStamp date)

INSERT INTO @m VALUES
    ('2021-01-01'),
    ('2021-01-02'),
    ('2021-01-06'),
    ('2021-01-07')


SELECT t.*, CASE WHEN m.DateStamp IS NULL THEN 0 ELSE 1 END as Maintenance
    FROM @t t 
        LEFT JOIN @m m on t.DateStamp = m.DateStamp
    

Using this as my dataset query I get the following data returned.

Now all I did was add a line chart with amount as the value and datestamp as the category group.

I then clicked the series line and changed the following properties...

Color

=IIF(Fields!DateStamp.Value <= Today(), "Blue", "Red")

MarketType

=IIF(Fields!Maintenance.Value=1, "Diamond", Nothing)

... and finally set the maker size to 15pt for clarity in this example.

This gave me the following output