Custom marker/colour for ONE SERIES ONLY on chart

• FRD

  SSC Enthusiast

  Points: 191

  More actions

  May 17, 2011 at 5:18 am

  #239750

  I am trying to create a scatter chart. The Y-co-ordinate is an experimental reading; the values are arranged in a categorical series along the X-axis, but I have added an X-coordinate value to the dataset to allow me to 'jitter' overlapping points to either side of the category centre.

  The dataset looks roughly like this:

  ROW_NUMBER|Y-value|Category|X-value

  1 2.3 'A' 0.8

  2 2.3 'A' 1.2

  3 3 'B' 2

  This allows me to mark the categories by X-position but also marker and colour, and makes for a very pretty and readable chart.

  I now need to add a marker for the median. Medians are hideous to implement in SSRS but reasonable in T-SQL, so I UNIONed a median series at the end of my dataset like this:

  ROW_NUMBER|Y-value|Category|X-value

  1 2.3 'Median' 1

  2 3 'Median' 2

  This graphs almost perfectly, but I just need to change a couple of things for this series only. The marker should be a horizontal line (not a filled shape) that appears to cut between the points. It should be black. On the legend, the 'Median' series should appear either first or last. These remaining series should retain their 'Auto' settings for these properties. How can I do this? I am working in Visual Studio.

• gsc_dba

  SSCertifiable

  Points: 5407

  More actions

  June 2, 2011 at 3:22 am

  #1332969

  Try the Switch function on the Series Properties, Markers, Marker Type -> create an expression with something like:

  =Switch(Fields!YourCategoryValue.Value="Median", "Cross", Fields!YourCategoryValue.Value="Average", "Circle")

  This may be useful too:

  http://www.keirgordon.com/post/SQL-Reporting-Services-Switch-Statement.aspx

  Basically boils down to use IIF for two, SWITCH for three or more.

  Edit: this should work in your example

  =IIF(Fields!YourCategoryValue.Value="Median", "Cross", "Circle")

  gsc_dba