{"id":8415,"date":"2022-07-04T07:30:44","date_gmt":"2022-07-04T05:30:44","guid":{"rendered":"https:\/\/expert-only.com\/?p=8415"},"modified":"2023-02-02T18:46:18","modified_gmt":"2023-02-02T17:46:18","slug":"dynamic-sql-query-with-ssrs","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/","title":{"rendered":"Dynamic SQL query with SSRS"},"content":{"rendered":"\n<h4 class=\"has-text-align-center wp-block-heading\"><strong><em>How to build a dynamic query in a SSRS report that use an input parameter in a where clause for example?<\/em><\/strong><\/h4>\n\n\n\n<p>How do I create a dynamic SQL query with SSRS, in datasets ? With Reporting Services, the fields used in a table object for example are linked to the database. This is done through a dataset which is itself the result of an SQL query. This dataset is linked to a data source. In fact, this data source contains a connection. For example to a database or an OLAP cube used as a source. The restitution of the data makes it possible to create static reports with fixed queries. Or dynamic reports with dynamic queries.<\/p>\n\n\n\n<p>Use filters to make the most of SQL Server queries and display a relevant selection of data. In other words, without filters all data is displayed. Or the data is restricted by, for example, the current day, month or quarter.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Examples of dynamic T-SQL code in SSRS report<\/h2>\n\n\n\n<p>Filters allow the user to select a particular year, product or customer. It is recommended to implement as many dynamic functions as possible in SSRS reports. In addition to this, the use of several parameters together allows for a more refined selection of data.<\/p>\n\n\n\n<p>Reporting Services is the <a href=\"https:\/\/docs.microsoft.com\/en-us\/sql\/reporting-services\/create-deploy-and-manage-mobile-and-paginated-reports?view=sql-server-ver15\" target=\"_blank\" rel=\"noreferrer noopener\">Reporting<\/a> and Dashboarding tool of the Microsoft BI suite. It is fully Responsive since the SQL Server 2016 version. This means that the reports can be adapted to different screen formats. This means: desktop, laptop, tablets and smartphones. There are several approaches to handling filters, here are two simple options.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Use SQL script and a SSRS expression<\/h2>\n\n\n\n<p>The easiest solution to maintain is the following, with a CITY field to select the city. First, the system evaluates the PARAMETERS!PARAMETER2.VALUE parameter against a first visible parameter. Parameter selected from a list of choices by the user, when the report is run.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">SELECT *\nFROM TABLE1\nWHERE CITY = PARAMETERS!PARAMETER2.VALUE<\/pre>\n\n\n\n<p>To do this, in the Expression properties of parameter PARAMETER2, insert a code with a function, like this one:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"visualbasic\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">=IIF(Parameters!Test.value = \"Value\", \"CONDITION1\", \"CONDITION2\")<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Example with conditions in the SSRS query<\/h2>\n\n\n\n<p>Here is the second solution which is to write the two parameters in the query expression. It is less elegant and more complex to develop with several filters:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">IF PARAMETERS!PARAMETER.VALUE = AVALUE\nSELECT * FROM TABLE1 WHERE FIELDS = CONDITION1\nELSE\nSELECT * FROM TABLE1 WHERE FIELDS = CONDITION2<\/pre>\n\n\n\n<p>It is therefore preferable to use the first solution. This article presents how to create a dynamic SQL query in an SSRS dataset to manage filters. To go further, here is how to <a href=\"https:\/\/expert-only.com\/en\/t-sql\/display-the-full-month-name-with-sql-server\/\">manage the month name with SQL Server code<\/a>.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-sql-and-it-tutorials wp-block-embed-sql-and-it-tutorials\"><div class=\"wp-block-embed__wrapper\">\nhttps:\/\/expert-only.com\/en\/t-sql\/display-month-name-sql-server\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to build a dynamic query in a SSRS report that use an input parameter in a where clause for example? How do I create a dynamic SQL query with SSRS, in datasets ? With Reporting Services, the fields <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\" title=\"Dynamic SQL query with SSRS\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":6520,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[574],"tags":[],"class_list":{"0":"post-8415","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssrs"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Dynamic SQL query with SSRS - Reporting Services<\/title>\n<meta name=\"description\" content=\"To create a dynamic SQL query with SSRS datasets, use this sample script evaluated at the Reporting Services report runtime.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Dynamic SQL query with SSRS\" \/>\n<meta property=\"og:description\" content=\"To create a dynamic SQL query with SSRS datasets, use this sample script evaluated at the Reporting Services report runtime.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-04T05:30:44+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-02-02T17:46:18+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\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:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Dynamic SQL query with SSRS\",\"datePublished\":\"2022-07-04T05:30:44+00:00\",\"dateModified\":\"2023-02-02T17:46:18+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\"},\"wordCount\":418,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg\",\"articleSection\":[\"SSRS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\",\"name\":\"Dynamic SQL query with SSRS - Reporting Services\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg\",\"datePublished\":\"2022-07-04T05:30:44+00:00\",\"dateModified\":\"2023-02-02T17:46:18+00:00\",\"description\":\"To create a dynamic SQL query with SSRS datasets, use this sample script evaluated at the Reporting Services report runtime.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Dynamic SQL query with SSRS\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Dynamic SQL query with SSRS - Reporting Services","description":"To create a dynamic SQL query with SSRS datasets, use this sample script evaluated at the Reporting Services report runtime.","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:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/","og_locale":"en_US","og_type":"article","og_title":"Dynamic SQL query with SSRS","og_description":"To create a dynamic SQL query with SSRS datasets, use this sample script evaluated at the Reporting Services report runtime.","og_url":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-07-04T05:30:44+00:00","article_modified_time":"2023-02-02T17:46:18+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Dynamic SQL query with SSRS","datePublished":"2022-07-04T05:30:44+00:00","dateModified":"2023-02-02T17:46:18+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/"},"wordCount":418,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg","articleSection":["SSRS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/","url":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/","name":"Dynamic SQL query with SSRS - Reporting Services","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg","datePublished":"2022-07-04T05:30:44+00:00","dateModified":"2023-02-02T17:46:18+00:00","description":"To create a dynamic SQL query with SSRS datasets, use this sample script evaluated at the Reporting Services report runtime.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/04\/code-1839406_1920.jpg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssrs\/dynamic-sql-query-with-ssrs\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Dynamic SQL query with SSRS"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/8415","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=8415"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/8415\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/6520"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=8415"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=8415"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=8415"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}