Multiple Y values for a single X in SSRS line\scatter chart

chartsreporting-servicessql-server-2008ssrs-2008

I've looked online but didn't find the answer I needed.

In SSRS 2008 I have a data set that returns results similiar to this:

Category Value
-------- -----
18       35
18       42
26       45
30       50
30       42
40       50

As you can see, a single category (or X axis) value (such as 18) may have multiple series (or Y axis) values (for 18 those values are 35 and 42).
When trying to plot this in a SSRS-2008 report chart, I am only able to display a single Y value for each X – max,min,average,first etc.

What I do need is to display a chart similiar to this:
MyChart http://img34.imageshack.us/img34/3990/chartexk.png.
Do you have any ideas on how to implement this?

Thanks for any answer!

Best Answer

Following is not a perfect solution. This is more of a hack to achieve the results. If you decide to use it, please be cautious with this approach. This may not yield the expected results all the time.

Since SSRS doesn't differentiate two values on the same axes on a line chart, you can do something as shown in the following example. The example uses SSRS 2008 R2 and the query used in the report dataset will work only with SQL Server 2005 or above.

Step-by-step process:

In an SQL Server database, create a table named dbo.ReportData using the scripts provided under the SQL Scripts section.
Populate the table with data as shown in screenshot #1. I have used the data provided in the question but also added an identity column to maintain the order.
Create an SSRS report and create a dataset named ReportData with the following query: SELECT Category + RANK() OVER(PARTITION BY Category ORDER BY Id) * .0001 AS Fraction, Category, Value FROM dbo.ReportData ORDER BY Id. Refer screenshot #2.
The above given dataset query partitions the rows by the column Category and then assigns a rank within each category based on the column Id. This ranking value is multiplied by .0001 and then value in Category column is added to this fraction. This calculated column almost gives the same value as Category but differs by 1 thousandth of a fraction by keeping the values unique. Refer screenshot #3 for the output of this query.
On the report, place a Chart control of Line type and configure the Chart Data section as shown in screenshot #4. The Fraction column, which is the newly calculated column should be in the Category Groups section and the Value column should be in the Values section of the Chart.
Right-click on the X axis and select Horizontal Axis Properties... Refer screenshot #5.
Configure the Horizontal Axis Properties as shown in screenshot #6. For the Maximum dropdown, click on the Function (fx) button and configure the expression as shown in screenshot #7.
For the data shown in screenshot #1, report execution would yield the results shown in screenshot #8. Similar to the chart provided in the question.
Let's rearrange the rows in the table and also will add few more additional rows as shown in screenshot #9.
For the data shown in screenshot #9, the report dataset query would yield the result shown in screenshot #10 and the report execution would yield the output shown in screenshot #11.

Hope that helps.

SQL Scripts:

CREATE TABLE [dbo].[ReportData](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Category] [int] NOT NULL,
    [Value] [int] NOT NULL,
CONSTRAINT [PK_ReportData] PRIMARY KEY CLUSTERED ([Id] ASC)) ON [PRIMARY]
GO

Screenshot #1: