This example shows a custom function that evaluates the standard deviation (similar to the “stdev” function available in Microsoft SQL) for use in a SELECT query.
Implementation
Interfaces
A custom function implements the following interfaces:
Registration
The static CriteriaOperator.RegisterCustomFunction method registers a custom function in this example.
For convenience, the custom function implements a static Register
method that registers the function. Call this method at application startup.
Use
After registration, you can use the function as part of an SQL statement. The custom function appears in the Query Editor function list among other functions.
See also: Expression Editor - How to implement a custom New Line and Format functions.
Files to Review
- Form1.cs (VB: Form1.vb)
- StDevOperator.cs (VB: StDevOperator.vb)
Documentation
More Examples
Does this example address your development requirements/objectives?
(you will be redirected to DevExpress.com to submit your response)
Example Code
C#using DevExpress.DataAccess.ConnectionParameters;
using DevExpress.DataAccess.Sql;
using DevExpress.XtraReports.UI;
using System;
using System.Windows.Forms;
// ...
namespace SelectQueryWindowsFormsApplication {
public partial class Form1 : Form {
public Form1() {
InitializeComponent();
// Register the custom function.
StDevFunction.Register();
}
private void button1_Click(object sender, EventArgs e) {
// Create a data source connection.
Access97ConnectionParameters connectionParameters = new Access97ConnectionParameters("../../nwind.mdb", "", "");
SqlDataSource ds = new SqlDataSource(connectionParameters);
// Create a query and specify its SELECT expression.
SelectQuery query = SelectQueryFluentBuilder
.AddTable("Products")
.SelectColumn("CategoryID")
.GroupBy("CategoryID")
.SelectExpression("StDev([Products].[UnitPrice])", "PriceDeviation")
.Build("Products");
//Add the query to the data source.
ds.Queries.Add(query);
// Fill the data source.
ds.Fill();
// Create a new report and bind it to the data source.
XtraReport report = new XtraReport();
report.DataSource = ds;
report.DataMember = "Products";
// Create a report layout.
DetailBand detailBand = new DetailBand();
detailBand.Height = 100;
report.Bands.Add(detailBand);
XRLabel labelCategory = new XRLabel();
labelCategory.ExpressionBindings.Add(
new DevExpress.XtraReports.UI.ExpressionBinding("BeforePrint", "Text", "[CategoryID]"));
labelCategory.TextFormatString = "Category ID: {0}";
labelCategory.TopF = 15;
detailBand.Controls.Add(labelCategory);
XRLabel labelDeviation = new XRLabel();
labelDeviation.ExpressionBindings.Add(
new DevExpress.XtraReports.UI.ExpressionBinding("BeforePrint", "Text", "[PriceDeviation]"));
labelDeviation.TopF = 50;
labelDeviation.WidthF = 500;
labelDeviation.TextFormatString = "Price Deviation: {0}";
detailBand.Controls.Add(labelDeviation);
// Show the report.
report.ShowDesigner();
}
}
}
C#using System;
using System.Collections.Generic;
using DevExpress.Data.Filtering;
namespace SelectQueryWindowsFormsApplication {
public sealed class StDevFunction : ICustomFunctionOperatorBrowsable, ICustomFunctionOperatorFormattable {
const string name = "StDev";
public static void Register() {
CriteriaOperator.RegisterCustomFunction(new StDevFunction());
}
public static void Unregister() {
CriteriaOperator.UnregisterCustomFunction(name);
}
public static readonly HashSet<Type> ValidOperandTypes = new HashSet<Type> {
typeof(sbyte),
typeof(byte),
typeof(short),
typeof(ushort),
typeof(int),
typeof(uint),
typeof(long),
typeof(ulong),
typeof(decimal),
typeof(double),
typeof(float)
};
#region ICustomFunctionOperator
public Type ResultType(params Type[] operands) {
return typeof(double);
}
public object Evaluate(params object[] operands) {
throw new NotSupportedException();
}
public string Name { get { return name; } }
#endregion
#region ICustomFunctionOperatorBrowsable
public FunctionCategory Category { get { return FunctionCategory.Math; } }
public string Description { get { return "Standard deviation function"; } }
public bool IsValidOperandCount(int count) {
return count == 1;
}
public bool IsValidOperandType(int operandIndex, int operandCount, Type type) {
return ValidOperandTypes.Contains(type);
}
public int MaxOperandCount { get { return 1; } }
public int MinOperandCount { get { return 1; } }
#endregion
#region ICustomFunctionOperatorFormattable
public string Format(Type providerType, params string[] operands) {
return string.Format("stdev({0})", operands[0]);
}
#endregion
}
}