2012-10-31

SSRS - Scatter Chart - Create bound line

There is a scatter chart made with 75th, average and 25th bound line in Excel (chart type: Scatter with Straight Lines), the problem is I need to execute some manual steps to illustrate this chart. First of all, refresh two data sources which come from SQL Server, then modify criteria for identifying plots and modify calculation for those line.



Then I start trying to draw this chart in Crystal Reports, I can technically make it similar to above, but it needs two same size charts which overlapped each other, set up data value for simulating those lines in each of them, and manually configure some settings.

Finally, I find the way to create this chart in SSRS. In the Excel, it needs two datasets or worksheets to achieve the goal, then I try to merge two datasets into one, so I add new column group, in this column, I use DB_Data, Client_Data, 75th_Bound, Avg_Bound and 25th_Bound to identify data group.

1. Drag chart item in the Body area → Choose Scatter as chart type.
2. Click chart
→ Click Add field button in Values section of Chart Data control panel
→ Choose the field I want to display in the Y axis
→ Right-click new added field, click Series Properties
→ In Series Data\Value field, click expression button and type the script as follows:
=IIF(Fields!group.Value <> "75th_Bound" AND Fields!group.Value <> "Avg_Bound" AND Fields!group.Value <> "25th_Bound",
    'This cretira is used for get rid of any data which it's value is too small, prevent lower bound line overlaps with x axis
    IIF(Fields!Y_Axis_Field_DB_Data.Value > Parameters!y_axis_interval.Value/10, Fields!Y_Axis_Field_DB_Data.Value,
        Fields!Y_Axis_Field_Client_Data.Value),
    NOTHING)
→ In Series Data\Category field, choose the field I want to display in the X axis
→ In Fill\Pick color, click expression button and input script:
=IIF(Fields!group.Value="DB_Data", "#7dff0000",
    IIF(Fields!name.Value="Machine2", "Orange", "Blue")
    )

3. Go to Properties window of new added field
→ Expand Label section → Choose expression in Label field and type:
=IIF(Fields!group.Value="DB_Data" OR Fields!group.Value="75th_Data" OR Fields!group.Value="Avg_Data" OR Fields!group.Value="25th_Data", NOTHING, Fields!name.Value)
So far, this chart has basic plots for identifying data, then I have to think about how to identify which records position on those lines and dynamically display them. I also need to prevent some extremely large value or abnormal records that would potentially extend chart area.

For my logic, I use triangle calculation to help me identify position.
1. 75 Percent Line (lower bound Line of top 25 percent): Order by angle(=Y/X) value, sort largest to smallest, catch the smallest value of top 25 percent group.
2. Average Line (upper bound line of bottom 50 percent): Order by angle(=Y/X) value, sort smallest to largest, catch the largest value of bottom 50 percent group.
3. 25 Percent Line (upper bound Line of bottom 25 percent): Order by angle(=Y/X) value, sort largest to smallest, catch the largest value of bottom 25 percent group.
, then I start to write the query:

    --For debugging
    /*
    SET @period    = 2012
    SET @x_axis_max = 70
    SET @x_axis_interval = 10
    SET @y_axis_max    = 450000
    SET @y_axis_interval = 50000
    */

    DECLARE   
            @axis_density            FLOAT,
            @angle_75th              FLOAT,
            @y_axis_75th            FLOAT,
            @x_axis_75th            FLOAT,
            @x_axis_min_75th    FLOAT,
            @y_axis_min_75th    FLOAT,
            @angle_avg               FLOAT,
            @y_axis_avg             FLOAT,
            @x_axis_avg             FLOAT,
            @x_axis_min_avg     FLOAT,
            @y_axis_min_avg     FLOAT,
            @angle_25th              FLOAT,
            @y_axis_25th            FLOAT,
            @x_axis_25th            FLOAT,
            @x_axis_min_25th    FLOAT,
            @y_axis_min_25th    FLOAT,
            @counts                     INT

    --==================================================
   --This density is used to create 45 degree line on the chart
   SET @axis_density = (@x_axis_max/@x_axis_interval)/1.0/(@y_axis_max/@y_axis_interval)
   --==================================================
   --75th Bound, Average Bound and 25th Bound
   --For 75th Bound
   SELECT    @angle_75th = MIN(angle)
   FROM
   (
       SELECT    TOP 25 PERCENT Y_Axis_Field / X_Axis_Field [angle]
       FROM    @tmp_scatter_chart
       WHERE    [group] = 'DB_Data'
               AND Y_Axis_Field <= @y_axis_max
               AND X_Axis_Field <= @x_axis_max
               AND Y_Axis_Field >= @y_axis_interval / 10 --Filter out bad records which it's meters_drilled is colse to 0 on the chart, and also prevent lower bound line overlapp with x-axis
       ORDER BY    Y_Axis_Field / X_Axis_Field DESC
   ) AS A
   
   SELECT    @x_axis_75th = X_Axis_Field,
           @y_axis_75th = Y_Axis_Field
   FROM    @tmp_scatter_chart
   WHERE    Y_Axis_Field / X_Axis_Field = @angle_75th
   
   --For Average Bound
   SELECT    @angle_avg = MAX(angle)
   FROM
   (
       SELECT    TOP 50 PERCENT Y_Axis_Field / X_Axis_Field [angle]
       FROM    @tmp_scatter_chart
       WHERE    [group] = 'DB_Data'
               AND Y_Axis_Field <= @y_axis_max
               AND X_Axis_Field <= @x_axis_max
               AND Y_Axis_Field >= @y_axis_interval / 10
       ORDER BY    Y_Axis_Field / X_Axis_Field
   ) AS A
   
   SELECT    @x_axis_avg = X_Axis_Field,
           @y_axis_avg = Y_Axis_Field
   FROM    @tmp_scatter_chart
   WHERE    Y_Axis_Field / X_Axis_Field = @angle_avg
   
   --For 25th Bound
   SELECT    @angle_25th = MAX(angle)
   FROM
   (
       SELECT    TOP 25 PERCENT Y_Axis_Field / X_Axis_Field [angle]
       FROM    @tmp_scatter_chart
       WHERE    [group] = 'DB_Data'
               AND Y_Axis_Field <= @y_axis_max
               AND X_Axis_Field <= @x_axis_max
               AND Y_Axis_Field >= @y_axis_interval / 10
       ORDER BY    Y_Axis_Field / X_Axis_Field
   ) AS A
   
   SELECT    @x_axis_25th = X_Axis_Field,
           @y_axis_25th = Y_Axis_Field
   FROM    @tmp_scatter_chart
   WHERE    Y_Axis_Field / X_Axis_Field = @angle_25th
   
   /*
   SELECT @axis_density
   SELECT @x_axis_75th, @y_axis_75th, @angle_75th
   SELECT @x_axis_avg, @y_axis_avg, @angle_avg
   SELECT @x_axis_25th, @y_axis_25th, @angle_25th
   */
   
   SET @x_axis_min_75th    = @x_axis_75th - @x_axis_interval * 5
   SET @y_axis_min_75th    = @y_axis_75th - @y_axis_interval * 5 * @axis_density
   SET @x_axis_min_avg     = @x_axis_avg - @x_axis_interval * 5
   SET @y_axis_min_avg     = @y_axis_avg - @y_axis_interval * 5 * @axis_density
   SET @x_axis_min_25th    = @x_axis_25th - @x_axis_interval * 5
   SET @y_axis_min_25th    = @y_axis_25th - @y_axis_interval * 5 * @axis_density
   
   SET @counts = 0
   WHILE @counts < 11
   BEGIN
       INSERT INTO @tmp_scatter_chart
                   (
                       [group],
                       Y_Axis_Field_DB_Data,
                       Y_Axis_Field_Client_Data,
                       X_Axis_Field
                   )
       SELECT    '75th_Bound',        
               @y_axis_min_75th + @y_axis_interval * @axis_density * @counts,
               @y_axis_min_75th + @y_axis_interval * @axis_density * @counts,
               @x_axis_min_75th + @x_axis_interval * @counts
       
       INSERT INTO @tmp_scatter_chart
                   (
                       [group],
                       Y_Axis_Field_DB_Data,
                       Y_Axis_Field_Client_Data,
                       X_Axis_Field
                   )
       SELECT    'Avg_Bound',        
               @y_axis_min_avg + @y_axis_interval * @axis_density * @counts,
               @y_axis_min_avg + @y_axis_interval * @axis_density * @counts,
               @x_axis_min_avg + @x_axis_interval * @counts
               
       INSERT INTO @tmp_scatter_chart
                   (
                       [group],
                       Y_Axis_Field_DB_Data,
                       Y_Axis_Field_Client_Data,
                       X_Axis_Field
                   )
       SELECT    '25th_Bound',        
               @y_axis_min_25th + @y_axis_interval * @axis_density * @counts,
               @y_axis_min_25th + @y_axis_interval * @axis_density * @counts,
               @x_axis_min_25th + @x_axis_interval * @counts
                           
       SET @counts = @counts + 1
   END
Note: Try to image those lines extend outside the chart area.

After I get the ideal dataset from the query, I start to illustrate it in the report, see the steps:
1. Click chart
→ Click Add field button in Values section of Chart Data control panel
→ Choose Y_Axis_Field_Client_Data or Y_Axis_DB_Data
→ Right-click new added field, click Series Properties
→ In Series Data\Value field, click expression button and type:
=IIF(Fields!group.Value="75th_Bound", Fields!Y_Axis_Field.Value, NOTHING)
→ In Series Data\Category field, choose the X_Axis_Field
→ Rename this field to Bound_75th in the properties window
2. Add two more fields for Avg_Bound and 25th_Bound, repeat the steps above, and change group value in the expression.
3. Right-click 75th_Bound field.
→ Click Add Calculated Series
→ In Calculated Series Properties window, select "Moving Average" in Formula drop down list, set up Period field value to 1, tick Start from first point
4. Add another two calculated series for Avg_Bound and 25th_Bound.

You can put more bound lines in the chart, e.g. top 10 percent bound line, it depends on your cases and situations.

Thanks to SQL Server Reporting Services, because it's more complicated than Crystal Reports, on the other hand, it also provide more flexible features to develop and design reports. Instead of manually adjusting settings when you re-run reports, using dynamic design of axis value could save you a lot of time. Another case is bubble chart with label that it's value come from another field, this situation can still can be realized in Crystal Reports, which using two different chart overlapped each other, switch axis value in one of them.

The final scatter chart looks as follows:

No comments:

Post a Comment