{"id":4658,"date":"2023-07-11T21:42:39","date_gmt":"2023-07-11T20:42:39","guid":{"rendered":"https:\/\/gdksoftware.com\/knowledgebase\/firedac-dataset-aggregations"},"modified":"2023-07-11T21:49:04","modified_gmt":"2023-07-11T20:49:04","slug":"firedac-dataset-aggregaties","status":"publish","type":"knowledge","link":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties","title":{"rendered":"Firedac dataset aggregaties"},"content":{"rendered":"<p>Wist je dat je in een <strong>Firedac<\/strong> query of table kunt werken met runtime <strong>aggregraties<\/strong> op dataset niveau? Een totaal bedrag van alle geladen orders weergeven, of een totaal van alle orders die nog verzonden moeten worden kan heel eenvoudig zonder dat je een losse query nodig hebt.<\/p>\n<p>Misschien was je al bekend met een aggregrated field, maar dat beperkt zich tot rijniveau. Met de property <code class=\"\" data-line=\"\">Aggregrates<\/code> van een <code class=\"\" data-line=\"\">TFDDataSet<\/code> kun je echter ook totaliseren op dataset niveau.<\/p>\n<p><img decoding=\"async\" class=\"alignnone wp-image-4645 size-full\" src=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png\" alt=\"\" width=\"505\" height=\"394\" srcset=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png 505w, https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector-300x234.png 300w\" sizes=\"(max-width: 505px) 100vw, 505px\" \/><\/p>\n<p>Als voorbeeld heb ik een demo programma waarin ik alle orders uit een database ophaal in een tabel. Die kan ik filteren op store id. Rechtsboven in beeld zie je het aantal nog te verzenden orders. Dit getal wordt berekend door een aggregrated field van de query.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-4648 alignnone\" src=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/demo-program.png\" alt=\"\" width=\"789\" height=\"283\" srcset=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/demo-program.png 789w, https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/demo-program-300x108.png 300w, https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/demo-program-768x275.png 768w\" sizes=\"(max-width: 789px) 100vw, 789px\" \/><\/p>\n<p>Om te beginnen definieer ik een aggregratie field in de aggregrations property van de query. Het belangrijkste veld hier is <code class=\"\" data-line=\"\">Expression<\/code>. Hier wordt de aggregratie gedefinieerd die we willen uitvoeren. Eventueel met een voorwaarde er in. Hier wordt de standaard <a href=\"https:\/\/docwiki.embarcadero.com\/Libraries\/Alexandria\/en\/FireDAC.Comp.DataSet.TFDAggregate.Expression\" target=\"_blank\" rel=\"noopener\">Firedac expression syntax<\/a> voor gebruikt.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-4651 alignnone\" src=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector-2.png\" alt=\"\" width=\"651\" height=\"392\" srcset=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector-2.png 651w, https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector-2-300x181.png 300w\" sizes=\"(max-width: 651px) 100vw, 651px\" \/><\/p>\n<p>In bovenstaand voorbeeld tel ik het aantal records (<code class=\"\" data-line=\"\">COUNT<\/code>) van het veld <code class=\"\" data-line=\"\">Shipped_Date<\/code> met als voorwaarde dat dit veld alleen geteld wordt als de waarde leeg is. Daarvoor kan de functie <code class=\"\" data-line=\"\">IIF<\/code> gebruikt worden. Andere eenvoudige voorbeelden die je hier kunt gebruiken zijn:<\/p>\n<p><code class=\"\" data-line=\"\">SUM(order_amount)<\/code><\/p>\n<p><code class=\"\" data-line=\"\">MIN(order_date)<\/code><\/p>\n<p>Zoals je kunt zien in de properties kun je een field op active zetten. Doe je dat niet, dan wordt het uiteraard niet berekend. Een naam kan ook handig zijn, om in de code de aggegratie terug te kunnen vinden.<\/p>\n<p>Helaas kun je een aggegratie niet als een database veld linken aan een data-aware control. Het weergeven moet dus in code geregeld worden. Hiervoor heb ik een aparte procedure gemaakt, zodat ik die eenvoudig kan aanroepen als een refresh nodig is. Deze procedure roep ik aan in het <code class=\"\" data-line=\"\">AfterOpen<\/code> event van de query en als er gefilterd wordt op een store.<\/p>\n<div style=\"background: #ffffff; overflow: auto; width: auto; border: solid gray; border-width: .1em .1em .1em .8em; padding: .2em .6em;\">\n<pre style=\"margin: 0; line-height: 125%;\"><span style=\"color: #008800; font-weight: bold;\">procedure<\/span> <span style=\"color: #bb0066; font-weight: bold;\">TfrmDemoApp<\/span><span style=\"color: #333333;\">.<\/span><span style=\"color: #0066bb; font-weight: bold;\">qryOrdersAfterOpen<\/span>(DataSet<span style=\"color: #333333;\">:<\/span> TDataSet)<span style=\"color: #333333;\">;<\/span>\r\n<span style=\"color: #008800; font-weight: bold;\">begin<\/span>\r\n  ShowNotShippedAggregation<span style=\"color: #333333;\">;<\/span>\r\n<span style=\"color: #008800; font-weight: bold;\">end<\/span><span style=\"color: #333333;\">;<\/span>\r\n\r\n<span style=\"color: #008800; font-weight: bold;\">procedure<\/span> <span style=\"color: #bb0066; font-weight: bold;\">TfrmDemoApp<\/span><span style=\"color: #333333;\">.<\/span><span style=\"color: #0066bb; font-weight: bold;\">ShowNotShippedAggregation<\/span><span style=\"color: #333333;\">;<\/span>\r\n<span style=\"color: #008800; font-weight: bold;\">begin<\/span>\r\n  <span style=\"color: #008800; font-weight: bold;\">var<\/span> NotShipped <span style=\"color: #333333;\">:=<\/span> qryOrders<span style=\"color: #333333;\">.<\/span>Aggregates<span style=\"color: #333333;\">.<\/span>Items[<span style=\"color: #0000dd; font-weight: bold;\">0<\/span>]<span style=\"color: #333333;\">.<\/span>Value<span style=\"color: #333333;\">;<\/span>\r\n\r\n  <span style=\"color: #008800; font-weight: bold;\">if<\/span> NotShipped <span style=\"color: #333333;\">=<\/span> Null <span style=\"color: #008800; font-weight: bold;\">then<\/span>\r\n     lblNotShipped<span style=\"color: #333333;\">.<\/span>Caption <span style=\"color: #333333;\">:=<\/span> <span style=\"background-color: #fff0f0;\">'0'<\/span>\r\n  <span style=\"color: #008800; font-weight: bold;\">else<\/span>\r\n    lblNotShipped<span style=\"color: #333333;\">.<\/span>Caption <span style=\"color: #333333;\">:=<\/span> NotShipped<span style=\"color: #333333;\">;<\/span>\r\n<span style=\"color: #008800; font-weight: bold;\">end<\/span><span style=\"color: #333333;\">;<\/span>\r\n<\/pre>\n<\/div>\n<p>Vergeet niet op de query de property <code class=\"\" data-line=\"\">AggegratesActive<\/code> op <code class=\"\" data-line=\"\">True<\/code> te zetten, anders worden de aggegraties niet uitgerekend. Een ander punt om rekening mee te houden: de aggegraties werken op de data die opgehaald is in de dataset. Standaard haalt Firedac 50 records op. Wil je een totaal wat gebaseerd is op alle records ga dan naar de <code class=\"\" data-line=\"\">FetchOptions<\/code> in de query properties en zet de <code class=\"\" data-line=\"\">Mode<\/code> op <code class=\"\" data-line=\"\">fmAll<\/code>.<\/p>\n<p>Zie hieronder het resultaat in de demo applicatie.<\/p>\n<p><img decoding=\"async\" class=\"size-full wp-image-4654 alignnone\" src=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Aggegrated_fields.gif\" alt=\"\" width=\"786\" height=\"592\" \/><\/p>\n","protected":false},"featured_media":0,"parent":0,"template":"","class_list":["post-4658","knowledge","type-knowledge","status-publish","hentry","knowledge-category-delphi"],"acf":{"author":1384,"type_hero":"compact","hero_image":4645,"hero_image_position":"","hero_title":"Firedac dataset aggregaties","hero_content":"","hero_link":null,"hero_show_h1":false},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v26.8 (Yoast SEO v27.6) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Firedac dataset aggregaties - GDK Software<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties\" \/>\n<meta property=\"og:locale\" content=\"nl_NL\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Firedac dataset aggregaties\" \/>\n<meta property=\"og:description\" content=\"Wist je dat je in een Firedac query of table kunt werken met runtime aggregraties op dataset niveau? Een totaal bedrag van alle geladen orders weergeven, of een totaal van alle orders die nog verzonden moeten worden kan heel eenvoudig zonder dat je een losse query nodig hebt. Misschien was je al bekend met een [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties\" \/>\n<meta property=\"og:site_name\" content=\"GDK Software\" \/>\n<meta property=\"article:modified_time\" content=\"2023-07-11T20:49:04+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Geschatte leestijd\" \/>\n\t<meta name=\"twitter:data1\" content=\"3 minuten\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties\",\"url\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties\",\"name\":\"Firedac dataset aggregaties - GDK Software\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/gdksoftware.com\\\/wp-content\\\/uploads\\\/2023\\\/07\\\/Object-Inspector.png\",\"datePublished\":\"2023-07-11T20:42:39+00:00\",\"dateModified\":\"2023-07-11T20:49:04+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties#breadcrumb\"},\"inLanguage\":\"nl-NL\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"nl-NL\",\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties#primaryimage\",\"url\":\"https:\\\/\\\/gdksoftware.com\\\/wp-content\\\/uploads\\\/2023\\\/07\\\/Object-Inspector.png\",\"contentUrl\":\"https:\\\/\\\/gdksoftware.com\\\/wp-content\\\/uploads\\\/2023\\\/07\\\/Object-Inspector.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\\\/firedac-dataset-aggregaties#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/gdksoftware.com\\\/nl\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Knowledgebase\",\"item\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"Delphi\",\"item\":\"https:\\\/\\\/gdksoftware.com\\\/nl\\\/kennisbank-categorie\\\/delphi\"},{\"@type\":\"ListItem\",\"position\":4,\"name\":\"Firedac dataset aggregaties\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/gdksoftware.com\\\/nl#website\",\"url\":\"https:\\\/\\\/gdksoftware.com\\\/nl\",\"name\":\"GDK Software\",\"description\":\"Zet de stip op je horizon\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/gdksoftware.com\\\/nl?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"nl-NL\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Firedac dataset aggregaties - GDK Software","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties","og_locale":"nl_NL","og_type":"article","og_title":"Firedac dataset aggregaties","og_description":"Wist je dat je in een Firedac query of table kunt werken met runtime aggregraties op dataset niveau? Een totaal bedrag van alle geladen orders weergeven, of een totaal van alle orders die nog verzonden moeten worden kan heel eenvoudig zonder dat je een losse query nodig hebt. Misschien was je al bekend met een [&hellip;]","og_url":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties","og_site_name":"GDK Software","article_modified_time":"2023-07-11T20:49:04+00:00","og_image":[{"url":"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png","type":"","width":"","height":""}],"twitter_card":"summary_large_image","twitter_misc":{"Geschatte leestijd":"3 minuten"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties","url":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties","name":"Firedac dataset aggregaties - GDK Software","isPartOf":{"@id":"https:\/\/gdksoftware.com\/nl#website"},"primaryImageOfPage":{"@id":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties#primaryimage"},"image":{"@id":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties#primaryimage"},"thumbnailUrl":"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png","datePublished":"2023-07-11T20:42:39+00:00","dateModified":"2023-07-11T20:49:04+00:00","breadcrumb":{"@id":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties#breadcrumb"},"inLanguage":"nl-NL","potentialAction":[{"@type":"ReadAction","target":["https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties"]}]},{"@type":"ImageObject","inLanguage":"nl-NL","@id":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties#primaryimage","url":"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png","contentUrl":"https:\/\/gdksoftware.com\/wp-content\/uploads\/2023\/07\/Object-Inspector.png"},{"@type":"BreadcrumbList","@id":"https:\/\/gdksoftware.com\/nl\/kennisbank\/firedac-dataset-aggregaties#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/gdksoftware.com\/nl"},{"@type":"ListItem","position":2,"name":"Knowledgebase","item":"https:\/\/gdksoftware.com\/nl\/kennisbank"},{"@type":"ListItem","position":3,"name":"Delphi","item":"https:\/\/gdksoftware.com\/nl\/kennisbank-categorie\/delphi"},{"@type":"ListItem","position":4,"name":"Firedac dataset aggregaties"}]},{"@type":"WebSite","@id":"https:\/\/gdksoftware.com\/nl#website","url":"https:\/\/gdksoftware.com\/nl","name":"GDK Software","description":"Zet de stip op je horizon","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/gdksoftware.com\/nl?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"nl-NL"}]}},"_links":{"self":[{"href":"https:\/\/gdksoftware.com\/nl\/wp-json\/wp\/v2\/knowledge\/4658","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gdksoftware.com\/nl\/wp-json\/wp\/v2\/knowledge"}],"about":[{"href":"https:\/\/gdksoftware.com\/nl\/wp-json\/wp\/v2\/types\/knowledge"}],"acf:post":[{"embeddable":true,"href":"https:\/\/gdksoftware.com\/nl\/wp-json\/wp\/v2\/team\/1384"}],"wp:attachment":[{"href":"https:\/\/gdksoftware.com\/nl\/wp-json\/wp\/v2\/media?parent=4658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}