{"id":1875,"date":"2012-05-15T05:32:18","date_gmt":"2012-05-15T05:32:18","guid":{"rendered":"https:\/\/poiseddevelopers.com\/reality-tech\/?p=1875"},"modified":"2024-05-13T10:35:02","modified_gmt":"2024-05-13T10:35:02","slug":"smart-filtering-in-bcs","status":"publish","type":"post","link":"https:\/\/poiseddevelopers.com\/reality-tech\/smart-filtering-in-bcs\/","title":{"rendered":"Smart Filtering in BCS"},"content":{"rendered":"<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_65 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title \" >Table of Contents<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1 eztoc-toggle-hide-by-default' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/smart-filtering-in-bcs\/#Smart_Filtering_in_Business_Connectivity_Services\" title=\"Smart Filtering in Business Connectivity Services\">Smart Filtering in Business Connectivity Services<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/poiseddevelopers.com\/reality-tech\/smart-filtering-in-bcs\/#Smart_MyFilterParm_Filtering_in_BCS\" title=\"Smart MyFilterParm Filtering in BCS\">Smart MyFilterParm Filtering in BCS<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Smart_Filtering_in_Business_Connectivity_Services\"><\/span>Smart Filtering in Business Connectivity Services<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Business Connectivity Services allows the rapid creation of reference connections to live legacy data such as tables or views in SQL Server. The wildcard filtering is great, but what if you want to customize it?<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Smart_MyFilterParm_Filtering_in_BCS\"><\/span>Smart MyFilterParm Filtering in BCS<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Using a stored procedure requires deviating from the easy out-of-box dynamic SQL and defining the input parameter(s).<\/p>\n<p>Here\u2019s the pseudocode T-SQL for the smart MyFilterParm filtering.<\/p>\n<ul>\n<li>Note that first up to 10 MyFilterParms are listed, based on matching on a specific field, then 50 more generic wildcard matches are matched.<\/li>\n<\/ul>\n<p>This has the advantage of a high-speed response, even if a user enters the letter \u201ca\u201d for search, making long-distance (inter-farm) lookups more responsive.<\/p>\n<p>For the Union, note the fields from each Select need to match precisely in sequence, name and type.<\/p>\n<p>Best is if the exact same fields and names are returned that we are using today.<\/p>\n<pre lang=\"php\"> CREATE procedure dbo.sp_MySmartSearch\r\n@MyFilterParmSmart nvarchar(255) = null\r\nAS\r\nSELECT 10 FROM [MyDataBase].[dbo].[CompanyView]\r\nWHERE MyFilterParm LIKE @MyFilterParmSmart + '%'\r\nUNION\r\nSELECT 50 FROM [MyDataBase].[dbo].[CompanyView]\r\nWHERE CompanyNM LIKE '%' + @MyFilterParmSmart + '%'<\/pre>\n<p>Once this Stored Procedure is written, export the BDCM (using SPD) and edit the XML to provide hard-coded reference to the above Stored procedure, MyFilterParm filter parameter, and fields returned. The BDCM import is not done in SPD, but is instead done in Central Admin in the BCS service app config. Here\u2019s the XML Pseudocode to replace within the Methods XML group in the BDCM (important parts highlighted in larger font):<\/p>\n<pre lang=\"php\">  &lt;Property Name=\"BackEndObject\" Type=\"System.String\"&gt;sp_MySmartSearch\r\n&lt;Property Name=\"BackEndObjectType\" Type=\"System.String\"&gt;SqlServerRoutine\r\n&lt;Property Name=\"RdbCommandText\" Type=\"System.String\"&gt;[dbo].[sp_MySmartSearch]\r\n&lt;Property Name=\"RdbCommandType\" Type=\"System.Data.CommandType, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089\"&gt;StoredProcedure\r\n&lt;Property Name=\"Schema\" Type=\"System.String\"&gt;dbo\r\n&lt;Parameter Direction=\"In\" Name=\"@MyFilterParmSmart\"&gt;\r\n  &lt;TypeDescriptor TypeName=\"System.String\" AssociatedFilter=\"Wildcard\" Name=\"@MyFilterParmSmart\"&gt;<\/pre>\n<p>You\u2019ll find the XML much easier to edit in Visual Studio (any version) as the nesting is a bit much to handle in Notepad.<\/p>\n<p>MSDN offers a similar example of a stored procedure, in this case, designed to return precisely one row:<\/p>\n<p><a style=\"color: #1f6799;\" href=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ee558376.aspx\" target=\"_blank\" rel=\"noopener\" aria-label=\"https:\/\/msdn.microsoft.com\/en-us\/library\/ee558376.aspx - open in a new tab\" data-uw-rm-ext-link=\"\">https:\/\/msdn.microsoft.com\/en-us\/library\/ee558376.aspx<\/a><\/p>\n<p>Scot Hillier\u2019s BCS book is also an excellent reference:<\/p>\n<p><a style=\"color: #1f6799;\" href=\"http:\/\/www.wrox.com\/WileyCDA\/WroxTitle\/Professional-Business-Connectivity-Services-in-SharePoint-2010.productCd-047061790X,descCd-DOWNLOAD.html\" target=\"_blank\" rel=\"noopener\" aria-label=\"http:\/\/www.wrox.com\/WileyCDA\/WroxTitle\/Professional-Business-Connectivity-Services-in-SharePoint-2010.productCd-047061790X,descCd-DOWNLOAD.html - open in a new tab\" data-uw-rm-ext-link=\"\">http:\/\/www.wrox.com\/WileyCDA\/WroxTitle\/Professional-Business-Connectivity-Services-in-SharePoint-2010.productCd-047061790X,descCd-DOWNLOAD.html<\/a><\/p>\n<pre lang=\"php\"><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Smart Filtering in Business Connectivity Services Business Connectivity Services allows the rapid creation of reference connections to live legacy data such as tables or views in SQL Server. The wildcard filtering is great, but what if you want to customize it? Smart MyFilterParm Filtering in BCS Using a stored procedure requires deviating from the easy [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":1877,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[61],"tags":[],"class_list":["post-1875","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-connectivity-services"],"acf":[],"_links":{"self":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/comments?post=1875"}],"version-history":[{"count":5,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1875\/revisions"}],"predecessor-version":[{"id":3943,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/posts\/1875\/revisions\/3943"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media\/1877"}],"wp:attachment":[{"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/media?parent=1875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/categories?post=1875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/poiseddevelopers.com\/reality-tech\/wp-json\/wp\/v2\/tags?post=1875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}