sql server - Problems with drawing a graph with SSRS charts (x over y)

Question

I tried several things but I am not getting to the point where I want the report to be. I need to draw a graph with the SSRS chart which draws x over y like in the image below. It shows a hysteresis curve which goes up, then right and then returns to go left and down. x axis is QValue and y axis is PValue.

The values of the procedure are returned as follows (only small part of the data):

Pos.    PValue  QValue
1300    421,44  2,54
1350    434,81  8,825
1400    448,88  11,78
1450    452,91  19,89
1500    452,56  20,38
1550    452,52  19,95
1600    452,32  20,19
1650    451,97  20,23
1700    452,19  19,88
1750    452,07  20,13
1800    452,28  20,24
1850    451,93  20,29
1900    452,08  20,04

The problems I'm having are related to be forced to aggregate the values. I used line chart and scatter. Scatter chart points are not connected and looks very bad and line chart is not working. As you see in the second image the points are not connected in their order (Pos.) and this makes a bad looking and the line is not going back.

I hope my explanation is okay. If you need more sample data or further explanation feel free to ask.

question from:https://stackoverflow.com/questions/65903674/problems-with-drawing-a-graph-with-ssrs-charts-x-over-y

Answer 1

This is not an answer as such, but it might point you in the right direction.

I took your sample data and converted it into a linestring. I created a dataset with the following...

declare @d TABLE(Pos int, PValue decimal (10,2), QValue decimal(10,2))

INSERT INTO @d VALUES

(1300,    421.44,  2.54),
(1350,    434.81,  8.825),
(1400,    448.88,  11.78),
(1450,    452.91,  19.89),
(1500,    452.56,  20.38),
(1550,    452.52,  19.95),
(1600,    452.32,  20.19),
(1650,    451.97,  20.23),
(1700,    452.19,  19.88),
(1750,    452.07,  20.13),
(1800,    452.28,  20.24),
(1850,    451.93,  20.29),
(1900,    452.08,  20.04)

declare @s varchar(max) = 'LINESTRING('

SELECT @s = 
    @s + 
    CASE @s WHEN 'LINESTRING(' THEN '' ELSE ', ' END 
    + CAST(d.PValue as varchar(10)) + ' ' + CAST(d.QValue as varchar(10))
FROM @d d
ORDER BY d.Pos 

SET @s = @s + ')'

DECLARE @g geometry  
SET @g = geometry::STGeomFromText(@s,0);  

SELECT @g

I then added a map to a report and selected SQL Spatial data, chose the dataset and accepted all the defaults. A quick adjustment of the zoom level and it drew the line succesfully. As I said not sure about plotting axes etc and lining them all up but might be worth further investigation.

The end result looked like this in preview. I suspect it looks wrong as the data sample is not great for a demo but this took 10 minutes so worth trying I guess.