Example T352441
Visible to All Users

Reporting for WinForms - Implement a Custom Function for Use in a Query Expression

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.

Custom Function in Query Builder

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

Documentation

More Examples

Does this example address your development requirements/objectives?

(you will be redirected to DevExpress.com to submit your response)

Example Code

SelectQueryWindowsFormsApplication/Form1.cs(vb)
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(); } } }
SelectQueryWindowsFormsApplication/StDevOperator.cs(vb)
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 } }

Disclaimer: The information provided on DevExpress.com and affiliated web properties (including the DevExpress Support Center) is provided "as is" without warranty of any kind. Developer Express Inc disclaims all warranties, either express or implied, including the warranties of merchantability and fitness for a particular purpose. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.

Confidential Information: Developer Express Inc does not wish to receive, will not act to procure, nor will it solicit, confidential or proprietary materials and information from you through the DevExpress Support Center or its web properties. Any and all materials or information divulged during chats, email communications, online discussions, Support Center tickets, or made available to Developer Express Inc in any manner will be deemed NOT to be confidential by Developer Express Inc. Please refer to the DevExpress.com Website Terms of Use for more information in this regard.