The One With

OData Provider for XPO - Summary Extensions (!summary)

OData + !summary Extensions

The Open Data Protocol allows us to address resources a number of different ways. We can filter using the $filter operator, sort with $orderby, page via the $top & $skip or with the help of server supplied continuation tokens.

These operators, for the most part, are enough when dealing with straightforward feeds. But when working with enterprise level data, we need to be able to access more than just data segments. We need aggregates! The OData Provider for XPO implements support for them using a custom bang extension !summary.

!summary=Count()

Assuming we have a resource set “Items” with the following structure:

<EntityType Name="Item">
  <Key>
    <PropertyRef Name="ID" />
  </Key>
  <Property Name="ID" Type="Edm.Int32" Nullable="false" />
  <Property Name="Content" Type="Edm.String" Nullable="true" />
  <Property Name="Amount" Type="Edm.Decimal" Nullable="false" />
  <Property Name="Created" Type="Edm.DateTime" Nullable="false" />
  <Property Name="Author" Type="Edm.String" Nullable="true" />
  <Property Name="Type" Type="Edm.Int32" Nullable="false" />
</EntityType>

Count all items where Author = ‘Steve’:

http://localhost:54691/Service.svc/Items?$filter=Author eq 'Steve'&!summary=Count()

<feed 
  xml:base="http://localhost:54691/Service.svc/" 
  xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
  xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
  xmlns="http://www.w3.org/2005/Atom">
  <entry>
    <content type="application/xml">
       <m:properties>
        <d:Key>*</d:Key>
        <d:Summary>Count()=2386</d:Summary>
      </m:properties>
    </content>
  </entry>
</feed>
Executed Server Side SQL:
SELECT COUNT(*) FROM "dbo"."Items" N0 
WHERE (N0."Author" = @p0)

!summary=Max(),Min()

http://localhost:54691/Service.svc/Items?!summary=Max(Created),Min(Created)
<feed 
  xml:base="http://localhost:54691/Service.svc/" 
  xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
  xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
  xmlns="http://www.w3.org/2005/Atom">
  <entry>
    <content type="application/xml">
      <m:properties>
        <d:Key>*</d:Key>
        <d:Summary>Max(Created)=2010-09-17T00:00:00,Min(Created)=2010-07-01T00:00:00</d:Summary>
      </m:properties>
    </content>
  </entry>
</feed>

!summary=Sum(),Avg()

Get the amount Avg and Total for items with type 3:

http://localhost:54691/Service.svc/Items?!summary=Sum(Amount),Avg(Amount)&$filter=Type eq 3

<feed 
  xml:base="http://localhost:54691/Service.svc/" 
  xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices" 
  xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata" 
  xmlns="http://www.w3.org/2005/Atom">
  <entry>
    <content type="application/xml">
      <m:properties>
        <d:Key>*</d:Key>
        <d:Summary>Sum(Amount)=4975139.6643,Avg(Amount)=497.5139</d:Summary>
      </m:properties>
    </content>
  </entry>
</feed>
Executed Server Side SQL:
SELECT SUM(N0."Amount"), AVG(N0."Amount") 
FROM "dbo"."Items" N0 
WHERE (N0."Type" = @p0)

!summary=Author,Sum(Amount)

!summary extension supports single-level grouping as well! If a field name is specified, grouping is assumed on that field.

In this example we get the total amount grouped by Author.

http://localhost:54691/Service.svc/Items?!summary=Author,Sum(Amount) 

<feed
 
xml:base="http://localhost:54691/Service.svc/"
  xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
  xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"
  xmlns="http://www.w3.org/2005/Atom">
  <
entry>
    <
contenttype="application/xml">
      <
m:properties>
        <
d:Key>Bill</d:Key>
        <
d:Summary>Sum(Amount)=1249351.4500</d:Summary>
      </
m:properties>
    </
content>
  </
entry>
  <
entry>
    <
contenttype="application/xml">
      <
m:properties>
        <
d:Key>Steve</d:Key>
        <
d:Summary>Sum(Amount)=1187387.2850</d:Summary>
      </
m:properties>
    </
content>
  </
entry>
  <
entry>
    <
contenttype="application/xml">
      <
m:properties>
        <
d:Key>John</d:Key>
        <
d:Summary>Sum(Amount)=1278919.2063</d:Summary>
      </
m:properties>
    </
content>
  </
entry>
  <
entry>
    <
contenttype="application/xml">
      <
m:properties>
        <
d:Key>Paul</d:Key>
        <
d:Summary>Sum(Amount)=1259481.7230</d:Summary>
      </
m:properties>
    </
content>
  </
entry>
</
feed>

Executed Server Side SQL:

SELECT N0."Author",SUM(N0."Amount") 
FROM "dbo"."Items" N0 
GROUP BY N0."Author"

Cheers

Azret

Published Aug 09 2010, 04:38 PM by
Filed under: , ,
Bookmark and Share

Comments

No Comments
LIVE CHAT

Chat is one of the many ways you can contact members of the DevExpress Team.
We are available Monday-Friday between 7:30am and 4:30pm Pacific Time.

If you need additional product information, write to us at info@devexpress.com or call us at +1 (818) 844-3383

FOLLOW US

DevExpress engineers feature-complete Presentation Controls, IDE Productivity Tools, Business Application Frameworks, and Reporting Systems for Visual Studio, along with high-performance HTML JS Mobile Frameworks for developers targeting iOS, Android and Windows Phone. Whether using WPF, ASP.NET, WinForms, HTML5 or Windows 10, DevExpress tools help you build and deliver your best in the shortest time possible.

Copyright © 1998-2017 Developer Express Inc.
All trademarks or registered trademarks are property of their respective owners