Search code examples
sql-serverreporting-servicesssrs-tablix

How to get multiple spline line for a single group ssrs graph

I like to achieve something like this. enter image description here Following is my dataset

CREATE TABLE #MonthData
(
temperature VARCHAR(200),
monthValue  VARCHAR(200),
StartTime DATETIME
)

INSERT INTO #MonthData VALUES
('89'   ,'5-April' , '2021-04-05 08:51:50.080'),
('0'    ,'5-April' , '2021-04-05 08:56:50.533'),
('95'   ,'5-April' , '2021-04-05 09:01:51.457'),
('90'   ,'5-April' , '2021-04-05 09:06:02.070'),
('113'  ,'5-April' , '2021-04-05 09:11:02.797'),
('101'  ,'5-April' , '2021-04-05 09:16:02.727'),
('100'  ,'5-April' , '2021-04-05 09:17:28.217'),
('96'   ,'5-April' , '2021-04-05 09:22:28.517'),
('91'   ,'5-April' , '2021-04-05 09:27:28.680'),
('89'   ,'5-April' , '2021-04-05 09:32:28.707'),
('89'   ,'5-April' , '2021-04-05 09:37:28.893'),
('88'   ,'5-April' , '2021-04-05 09:42:29.680'),
('111'  ,'6-April' , '2021-04-06 00:47:51.467'),
('101'  ,'6-April' , '2021-04-06 00:52:01.310'),
('94'   ,'6-April' , '2021-04-06 00:52:31.647'),
('106'  ,'6-April' , '2021-04-06 00:53:00.910'),
('99'   ,'6-April' , '2021-04-06 00:54:01.797'),
('93'   ,'6-April' , '2021-04-06 00:58:01.960'),
('89'   ,'6-April' , '2021-04-06 00:58:32.193'),
('89'   ,'6-April' , '2021-04-06 00:59:31.497') 

SELECT * FROM  #MonthData ORDER BY StartTime

DROP TABLE #MonthData

I tried following

enter image description here

I group category by Start time.

enter image description here

Still the x-axis value is getting repeated.

enter image description here

Solution

  • The closest I could get to quickly was this...

    enter image description here

    To get this I only used the StartTime field as the category group. I did NOT use the MonthValue field at all.. (I also changed the temperature column to be numeric).

    The on the horizontal axis properties I set as follows

    enter image description here

    Once the axis type is scalar things work as expected, I changed the interval type to Days and the interval to 1 .

    This almost worked but the first date was not shown, so I used an expression to set the minimum axis value to be midnight of the earliest StartTime with

    =DATEVALUE(MIN(Fields!StartTime.Value))

    This basically returns the date part with a time of 00:00:00

    If you want to only show the day and month then just format the axis label with a date format like d MMMM . You can access the format property in the properties pane under LabelsFormat