DataTable.Compute method is lovely…


For some reasons I do like to use DataSets (and Datatables) rather than DataReaders or other sort of IList collections to hold a list of records. Especially, typed DataSets are very helpful and am intended to write some articles about exploiting them later in this weblog. For example we can fill DataTables’s data/metadata by retrieving it from a database or directly through some code. Anyway the other day I was trying to create a GridView with subtotals (will explain about how to create such a gridview later) and I used DataTable.Compute method to sum up the values of a column of my GridView. Compute method can calculate Sum, Average, Count and … of a specific column. It’s syntax is like this:

DataTable.Compute (string expression, string filter)

expression contains an aggregate function like Sum, Average, Count and etc. For example to calculate the total cost you must use SUM(COST) as your expression.

If you must perform an operation on two or more columns, you should create a DataColumn, set its Expression property to an appropriate expression, and use an aggregate expression on the resulting column. In that case, given a DataColumn with the name “total,” and the Expression property set to:

"Quantity * UnitPrice"

The expression argument for the Compute method would then be:

Sum(total)

The second parameter filter determines which rows are used in the expression. For example if your DataTable contains a column named Age, you can set filter expression to “age >= 30”.

Some of availuible aggregate functions are:

SUM, AVG , COUNT, MIN and MAX

P.S.1 : SUM of an Int32 column is long, not int!

P.S.2 : The result type of Computer method is Object so you must explicitly cast it to your desired type.

Example Code:

Example of DataTable.Compute Method

Advertisements

9 thoughts on “DataTable.Compute method is lovely…

  1. Subin Verghese says:

    Hi, I read your following post “DataTable.Compute method is lovely…” at the given link :

    https://aspguy.wordpress.com/2008/06/08/datatablecompute-method-is-lovely/

    But the one thing I want to know is that whether can I use SELECT stattement in a Compute method. Bcoz my DataSet has got a newly created DateField column in it and on the basis of that DateField column I need to SELECT my records (i.e Records which falls within a particular Data Range).

    Please mail me on : mexicangreen@gmail.com

    It’s very URGENT………..hope u’ll help me out.

    Thanking you in anticipation.


    Hi 🙂
    Thanks for the comment. Unfortunatly you can not use any SELECT statement in Compute method or better say in DataSet because ADO.NET DataSet is an in-memory storage and is disconnected from data base. I think you have not chosen a good solution for your requirement. May you please write here what do you exactly want to do? We might find a better way.

  2. Subin Verghese says:

    The database I’m using is of AS400 mainframes. And in Mainframes, the date fields are always splitted in different columns like DD, MM, CC, HH.

    e.g. 4th August 2008 will be shown as : 08, 04, 20, 08.

    I have to generate a report based on these date fields (i.e. within a StartDate and EndDate range selection). Since in the database table they are all in different columns, what I did was that, I just created a DataSet and added a new Column in DataSet and concatenated all date columns and passed this concatenated value to the newly added column. In my crystal report, the newly added date column shows up and is in the format of “MM-DD-HHYY”. And I’m perfectly fine till here.

    Now the problem is that how can I use the SELECT statement or pass the StartEnd & EndDate so that my report shows records betweeen the date range.

    Another problem is that I have kept the field type as “STRING” for the newly added column. If I keep the DataSet field type as DateTime for the newly added column, its shows me the the time also (like this : 08/04/2008 12:00:00), and I’m unable to remove the time part.

    I’m totally confused, pls help me. Let me know if you can see a bright light in this tunnel.
    ————–
    Hi Subin ,
    Of course there is a light int this tunnel! For filtering your report you may do several things. Unfortunately I do not know AS400 so I am not sure, but it’s Query language may have Year, Month or Day functions, lie MS SQL Server, and you may use them for filtering. This is a sample, which is in T-SQL:

    Declare @ToDateParam SmallDateTime
    Set @ToDateParam = GetDate()
    Select * from TABLE Where Year(@ToDateParam) = YEARFIELD and Month(@ToDateParam) = MONTHFIELD …

    this solution is the 1st choice and is suitable to filter records on database. If it doesn’t work, add a DateTime field to your DataSet, and fill with a date-time value made of your Day, Month, Year fields. for example you can fill it like this:

    MyDataTable[0].MyDateField = new DateTime(year, month, day)

    Afterwards, filter your DataTable by it’s .DefaultView property :
    MyDataTable.DefaultView.RowFitler = “MyDateField >= ” + ToDateParam.ToString();
    then use MyDataTable.DefaultView as your Crystal Report’s data source. It will work.

    For showing the date part only in your CR report, the best way is to put your DateTime field onto the report, and just change it’s formatting not to show the time part. To do this, inside crystal report designer, right-click the DateTime field, then choose Format Object item.

  3. Subin Verghese says:

    Thanks for the reply.

    I’m able to display the date without the time thing in Crystal Report if I change the data type to String. But if I make it DateTime, it displays the time also alongwith the Date like “08/04/2008 12:00:00”.

    Hi again,
    As I mentioned before, since the problem is about formatting, you must solve it in crystal reports. Open your report in crystal report designer, right-click the date-time field, choose Format Object, then in Format Object window, click on “customize” button and specify your desired date format.
    Any changes to your code will not help.

  4. Subin Verghese says:

    My dear friend, I really appreciate your help. It worked perfectly.

    I comitted a mistake while creating a datacolumn in dataset. I mapped the datatype as String using typeof. But it didnt catch my attention since the dataset was showing DateTime in the XML part. That’s why when you mentioned to right click the datefield in Crystal Report, the cutomize button didn’t appeared. Then I looked why its not showing up the customize button with various other date formats. That’s how I changed the code.

    I was posting on various sites for help for the last 2 weeks and none of the comments posted helped me except yours.

    Great job……….THANKS A LOT ONCE AGAIN………you made my DAY…………!!!

  5. sabdhagiri says:

    Hi,

    I want to add a new unbound field which is going to be a custom calculated field in DevExpress xtrapivotgrid, ie., it must perform the calculations based upon user specified formula involving the fields already present in the grid are there any ways of doing this??

    please help me out..

    Thanks:
    sabdhagiri

    • Hi There,
      you can make a C# or VB.NET code at run time based on the user input (for example create Math.Round(6.5) ) and compile it using CSharpCodeProvider (or the one for VB.NET) class at runtime. Please be advised that executing a user-entered code at run time can be extremely unsafe so you would better provide a user interface to the end-user (like several buttons letting the user enter numbers and select operations) and create the code yourself. please see the following link about CSharpCodeProvider class:

      http://msdn.microsoft.com/en-us/library/microsoft.csharp.csharpcodeprovider.aspx

  6. sabdhagiri says:

    Hi friend,

    Thanks for your suggestion actually I am just a beginner and a newbie to VS..
    I want to know how to allow the user to specify the formula of his own, say for example:

    Field1+Field2/0.25-4*Field3

    where, Field1,2,3 are the fields in the grid..
    what i have done is, I provided the user with a textbox to enter his formula where each entry is separated by a delimiter like ‘,’ and I am splitting the string where ever the delimiters are encountered and storing it in a string array to get which are the fields that the user is going to use for applying the formula.. now the problem is having these fields how do I read the data from the pivotgrid and perform calculation??

    the user will enter his formula in textbox as follows:

    Field1,+,Field2,/,0.25,-,4,*,Field3

    I am using the split method to split the formula string

    please help me..

    Thanks:
    sabdhagiri

  7. Entre los diferentes tipos de lavadoras de la firma que reparamos se ecuentran las que disponen de doble función de secadora, las extra silence y las integrables. En estos casos, desaconsejamos que intente extraer el material de obstrucción usted mismo, ya que podría dañar el conducto, siendo necesario el recambio de la pieza y que contacte con nuestro servicio técnico antes de que se produzcan filtraciones. A mí me estafaron 30€ (me tuvieron que devolver 70 de los 100 que había dado de señal para comprar los repuestos que 4 meses después no los recibían”).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s