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",→ In Series Data\Category field, choose the field I want to display in the X axis
'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 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:
Note: Try to image those lines extend outside the chart area.
--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
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