{"id":3397,"date":"2023-04-12T15:32:59","date_gmt":"2023-04-12T15:32:59","guid":{"rendered":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/"},"modified":"2026-01-20T15:14:15","modified_gmt":"2026-01-20T15:14:15","slug":"creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator","status":"publish","type":"post","link":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/","title":{"rendered":"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process?"},"content":{"rendered":"<p>When using an ODBC data source for a Planning Analytics Turbo Integrator (TI) process, native TI parameters can be utilized within the SQL query.\u00a0 Planning Analytics users have the power to be more specific and efficient with a query, potentially increasing performance by having less unnecessary data that needs to be read throughout the process, as well as more efficient logic to filter data in the Metadata and Data tabs, and less long-term model maintenance.<\/p>\n<p>Before incorporating a parameter into the query, the variables which are expected of the query must be established either manually or with a non-dynamic version of the final query (recommended).<\/p>\n<p>In the TI\u2019s Prolog, the parameter can still be updated and manipulated, if necessary. The syntax for the dynamic parameter can be added to the query on the Data Source tab, or the <a href=\"https:\/\/www.ibm.com\/support\/knowledgecenter\/en\/SSD29G_2.0.0\/com.ibm.swg.ba.cognos.tm1_ref.2.0.0.doc\/r_tm1_ref_datasourcequery.html\">DataSourceQuery<\/a> function can be used in the Prolog to overwrite the query on the Data Source tab.<\/p>\n<p>One common use of adding a parameter to a query is to specify a particular period (or range of periods) in the SQL statement\u2019s <em>WHERE<\/em> clause to restrict data coming from the source to only the desired periods. \u00a0In this example, developed using <strong>Planning Analytics Workspace (PAW),<\/strong> we will use \u00a0the manually created parameter, <em>pYear,<\/em> to represent the input parameter that will be used to make this query dynamic.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-811\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL1-min.jpg\" alt=\"\" width=\"742\" height=\"376\" \/><\/p>\n<p>The syntax for adding any string parameter to a SQL statement is: question marks around the full name of the parameter, enclosed with single quotes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-812\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL2-min.jpg\" alt=\"\" width=\"748\" height=\"341\" \/><\/p>\n<p>This example would then prompt the PA user at run-time, providing the ability to dynamically query the source for the year specified, instead of hard-coding the year, requiring long-term maintenance, or pulling too much data and having to filter it later in the process.<\/p>\n<p>A numeric parameter has slightly different syntax by not enclosing the parameter in single quotes.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-813\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL3-min.jpg\" alt=\"\" width=\"743\" height=\"377\" \/><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-814\" src=\"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL4-min.jpg\" alt=\"\" width=\"746\" height=\"375\" \/><\/p>\n<p>If you have additional questions or need any assistance, please contact QueBIT at <a href=\"mailto:info@quebit.com\">info@quebit.com<\/a>.<\/p>\n<p>See more Planning Analytics technical tips at <a href=\"https:\/\/quebit.com\/askquebit\/\">Ask QueBIT<\/a>!<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When using an ODBC data source for a Planning Analytics Turbo Integrator (TI) process, native TI parameters can be utilized within the SQL query.\u00a0 Planning Analytics users have the power to be more specific and efficient with a query, potentially increasing performance by having less unnecessary data that needs to be read throughout the process,&hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[24],"tags":[35,36,52],"class_list":["post-3397","post","type-post","status-publish","format-standard","hentry","category-ibm","tag-development","tag-planning-analytics","tag-planning-analytics-development"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.3 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process? - QueBIT<\/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:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process? - QueBIT\" \/>\n<meta property=\"og:description\" content=\"When using an ODBC data source for a Planning Analytics Turbo Integrator (TI) process, native TI parameters can be utilized within the SQL query.\u00a0 Planning Analytics users have the power to be more specific and efficient with a query, potentially increasing performance by having less unnecessary data that needs to be read throughout the process,&hellip;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/\" \/>\n<meta property=\"og:site_name\" content=\"QueBIT\" \/>\n<meta property=\"article:published_time\" content=\"2023-04-12T15:32:59+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2026-01-20T15:14:15+00:00\" \/>\n<meta name=\"author\" content=\"agoddard\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"agoddard\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/\"},\"author\":{\"name\":\"agoddard\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"headline\":\"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process?\",\"datePublished\":\"2023-04-12T15:32:59+00:00\",\"dateModified\":\"2026-01-20T15:14:15+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/\"},\"wordCount\":352,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/DynamicSQL1-min.jpg\",\"keywords\":[\"Development\",\"Planning Analytics\",\"Planning Analytics Development\"],\"articleSection\":[\"IBM\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/\",\"name\":\"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process? - QueBIT\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/DynamicSQL1-min.jpg\",\"datePublished\":\"2023-04-12T15:32:59+00:00\",\"dateModified\":\"2026-01-20T15:14:15+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#primaryimage\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/DynamicSQL1-min.jpg\",\"contentUrl\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/wp-content\\\/uploads\\\/2019\\\/10\\\/DynamicSQL1-min.jpg\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#website\",\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/\",\"name\":\"QueBIT\",\"description\":\"QueBIT\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/#\\\/schema\\\/person\\\/e52d72da0fd2f5f70d189343fe4f5084\",\"name\":\"agoddard\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g\",\"caption\":\"agoddard\"},\"sameAs\":[\"https:\\\/\\\/quebit.com\\\/askquebit\"],\"url\":\"https:\\\/\\\/quebit.com\\\/askquebit\\\/author\\\/agoddard\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process? - QueBIT","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:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/","og_locale":"en_US","og_type":"article","og_title":"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process? - QueBIT","og_description":"When using an ODBC data source for a Planning Analytics Turbo Integrator (TI) process, native TI parameters can be utilized within the SQL query.\u00a0 Planning Analytics users have the power to be more specific and efficient with a query, potentially increasing performance by having less unnecessary data that needs to be read throughout the process,&hellip;","og_url":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/","og_site_name":"QueBIT","article_published_time":"2023-04-12T15:32:59+00:00","article_modified_time":"2026-01-20T15:14:15+00:00","author":"agoddard","twitter_card":"summary_large_image","twitter_misc":{"Written by":"agoddard","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#article","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/"},"author":{"name":"agoddard","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"headline":"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process?","datePublished":"2023-04-12T15:32:59+00:00","dateModified":"2026-01-20T15:14:15+00:00","mainEntityOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/"},"wordCount":352,"commentCount":0,"image":{"@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL1-min.jpg","keywords":["Development","Planning Analytics","Planning Analytics Development"],"articleSection":["IBM"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/","url":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/","name":"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process? - QueBIT","isPartOf":{"@id":"https:\/\/quebit.com\/askquebit\/#website"},"primaryImageOfPage":{"@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#primaryimage"},"image":{"@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#primaryimage"},"thumbnailUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL1-min.jpg","datePublished":"2023-04-12T15:32:59+00:00","dateModified":"2026-01-20T15:14:15+00:00","author":{"@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084"},"breadcrumb":{"@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#primaryimage","url":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL1-min.jpg","contentUrl":"https:\/\/quebit.com\/askquebit\/wp-content\/uploads\/2019\/10\/DynamicSQL1-min.jpg"},{"@type":"BreadcrumbList","@id":"https:\/\/quebit.com\/askquebit\/creating-a-dynamic-sql-statement-in-planning-analytics-turbo-integrator\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/quebit.com\/askquebit\/"},{"@type":"ListItem","position":2,"name":"How Do I Build and Use a Dynamic SQL Statement in a Planning Analytics TurboIntegrator Process?"}]},{"@type":"WebSite","@id":"https:\/\/quebit.com\/askquebit\/#website","url":"https:\/\/quebit.com\/askquebit\/","name":"QueBIT","description":"QueBIT","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/quebit.com\/askquebit\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/quebit.com\/askquebit\/#\/schema\/person\/e52d72da0fd2f5f70d189343fe4f5084","name":"agoddard","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/d817b364cff1d66116debde8d1c85e5e76eeece9c5ae731b19276a6040231455?s=96&d=mm&r=g","caption":"agoddard"},"sameAs":["https:\/\/quebit.com\/askquebit"],"url":"https:\/\/quebit.com\/askquebit\/author\/agoddard\/"}]}},"_links":{"self":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3397","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/comments?post=3397"}],"version-history":[{"count":2,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3397\/revisions"}],"predecessor-version":[{"id":5034,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/posts\/3397\/revisions\/5034"}],"wp:attachment":[{"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/media?parent=3397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/categories?post=3397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/quebit.com\/askquebit\/wp-json\/wp\/v2\/tags?post=3397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}