{"id":175,"date":"2007-09-20T19:49:00","date_gmt":"2007-09-20T19:49:00","guid":{"rendered":"\/lisa\/post\/2007\/09\/20\/Another-bit-for-dynamic-ReportViewer-control-handling-Parameters-in-more-depth.aspx"},"modified":"2007-09-20T19:49:00","modified_gmt":"2007-09-20T19:49:00","slug":"another-bit-for-dynamic-reportviewer-control-handling-parameters-in-more-depth","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2007\/09\/20\/another-bit-for-dynamic-reportviewer-control-handling-parameters-in-more-depth\/","title":{"rendered":"Another bit for dynamic ReportViewer control-handling: Parameters in more depth"},"content":{"rendered":"<p>\nFWIW I dearly love <a href=\"http:\/\/msdn2.microsoft.com\/en-us\/library\/Aa256621(SQL.80)\/\" target=\"_blank\" title=\"URL Access for RS in the docs\" rel=\"noopener\">URL Access<\/a> in SQL Server Reporting Services.&nbsp; Well, to be exact, I love <a href=\"http:\/\/ajaxpatterns.org\/RESTful_Service\" target=\"_blank\" title=\"RESTful adventures\" rel=\"noopener\">REST interfaces in general<\/a>.&nbsp;(I don&#39;t&nbsp;know why MS couldn&#39;t use the perfectly good name already available for this feature.)&nbsp;\n<\/p>\n<p>\nI didn&#39;t discuss this in much detail in my <a href=\"\/lisa\/2007\/09\/19\/Dynamically-loading-reports-in-ReportViewers\/\" title=\"Dynamically loading stuff to reportviewer controls\">last post on dynamic use of ReportViewer controls<\/a>, although it contains a quick parameterization of the control as part of the general dynamic process that the RDLDocumenter TestHarness goes through. But it seems that this is <a href=\"http:\/\/forums.microsoft.com\/MSDN\/ShowPost.aspx?PostID=2146036&amp;SiteID=1\" target=\"_blank\" title=\"How do I pass parameters\" rel=\"noopener\">something people need<\/a>, so here goes&#8230;\n<\/p>\n<p>\nThis time, just for fun, we&#39;ll be using server mode reports (RDLs) instead of local mode (RDLCs), and the webform ReportViewer control instead of the winform version of <a href=\"\/articles\/xmlrsdocs\/\" title=\"XMLRSDocs article-protodocs\">XMLRSDocs<\/a>&#39; TestHarness form uses.&nbsp; Same process, though.\n<\/p>\n<p>\n<a href=\"http:\/\/www.databaseanswers.org\/\" target=\"_blank\" title=\"Barry Williams' site\" rel=\"noopener\">Barry Williams<\/a> would like his users to use a REST-style API to call up reports in a webform. An optimal solution will (a) take little to no programming &#8212; the reports are server-based, not local-mode &#8212; and (b) not take any adjustment when the parameters available for his report(s) change.&nbsp; Let&#39;s put something together to handle that.\n<\/p>\n<p>\nBarry is a data guy, so I will expect him to drive this solution from a database with more smarts than I&#39;m showing in this example.&nbsp; Here, I mostly&nbsp;want&nbsp;to&nbsp;show how you pull lthe parameters out of the URL request and apply them to the report and, rather than show a full data-driven solution, I&#39;ll use the following simple strategy to make the solution generic:\n<\/p>\n<ul>\n<li>iterate through <em>all<\/em> the parameters sent in the URL<\/li>\n<li>determine which ones are &quot;mine&quot; by looking for a naming convention &#8212; here, I&#39;ll use a prefix of <strong>r:<\/strong> for &quot;my&quot; example parameters<\/li>\n<li>apply those values to my report<\/li>\n<\/ul>\n<p>\nAs an example, in the following URL, &quot;test&quot; will be ignored and the other two parameters will be applied:\n<\/p>\n<div class=\"code\">\n<br \/>\nhttp:\/\/myServer\/myApp\/ReportViewerParametersDemo.aspx?<br \/>\n&nbsp;&nbsp; <strong>r:Status=Cancelled&amp;r:ExcludeInternal=false<\/strong>&amp;test=Something <\/p>\n<\/div>\n<p>\n<br \/>\nReady?&nbsp;\n<\/p>\n<p>\nIf you want to play along at home, or use this code, all&nbsp;you need to do&nbsp;in Visual Studio&nbsp;to reproduce&nbsp;my ASPX page is this:\n<\/p>\n<ul>\n<li>Create a new ASPX page (&quot;New web form&quot;)\n<\/li>\n<li>In the Code view, blow away the code-behind information (<font color=\"#ff0000\">CodeFile<\/font><font color=\"#0000ff\">=&quot;Default2.aspx.vb&quot;<\/font> <font color=\"#ff0000\">Inherits<\/font><font color=\"#0000ff\">=&quot;Default2&quot;<\/font>) in the page directive, so we&#39;re left with\n<p>\n\t&lt;%<font color=\"#0000ff\">@<\/font> <font color=\"#a31515\">Page<\/font> <font color=\"#ff0000\">Language<\/font><font color=\"#0000ff\">=&quot;VB&quot;<\/font> <font color=\"#ff0000\">AutoEventWireup<\/font><font color=\"#0000ff\">=&quot;false&quot;<\/font> %&gt;<\/p>\n<p>\n\t&#8230; I honestly see no reason to use a code-behind page rather than a real class built into a DLL if you have serious code to put into an ASPX page.&nbsp; IAC, we don&#39;t have serious code here.\n\t<\/p>\n<\/li>\n<li>\n<p>\n\tIn the Design view, drag and drop a ReportViewer control and a Label control (for some debugging and eventual validation feedback) onto the page surface.\n\t<\/p>\n<\/li>\n<li>\n<p>\n\t&quot;Point&quot; the ReportViewer control at your server report, using the ReportViewer.ServerReport ReportPath and ReportServerURL properties.\n\t<\/p>\n<\/li>\n<\/ul>\n<p>\nNow go back to the Code view and add the server-side block you see below into the page, to handle the page load event.\n<\/p>\n<p>\nThere are only a couple of tricky things about this, so I&#39;ll just note them quickly for you here:\n<\/p>\n<ul>\n<li>For those of you who are not used to doing Asp.NET work, we&#39;re using <strong>Request.QueryString<\/strong>.&nbsp; For more extensive requirements beyond simple URLAccess GET syntax, you would use <strong>Request.Params<\/strong>.\n\t<\/li>\n<li>You may be surprised, or confused, by the use of the <strong>Split<\/strong> function on the value of being passed through the querystring.&nbsp; A list of values that all have the same parameter name &#8212; for example, the choices in a multi-select HTML listbox &#8212; are passed by browser-clients as a comma-delimited string to the server.&nbsp; Essentially, multi-value report parameters model this behavior; they hold collections of values as an array.&nbsp; If there is no comma, there&#39;s only one value, but if there are more, you need to split them out into the array that the report &quot;expects&quot; to see for this parameter.&nbsp; \n\t<\/li>\n<\/ul>\n<p>\nHere is&nbsp;a parameterized URL example showing three values for the &quot;Status&quot; multi-valued parameter:\n<\/p>\n<div class=\"code\">\n<br \/>\nhttp:\/\/myServer\/myApp\/ReportViewerParametersDemo.aspx?<br \/>\n&nbsp;&nbsp; <strong>r:Status=Cancelled,Pending,Tentative<\/strong>&amp;r:ExcludeInternal=false<br \/>\n&nbsp;\n<\/div>\n<p><\/p>\n<p>\n.. the Split function will take care of passing Cancelled, Pending, and Tentative as the separate values to this parameter.\n<\/p>\n<p>\nEnjoy!\n<\/p>\n<div class=\"code\">\n<br \/>\n&lt;<span style=\"color: #a31515\">script<\/span> <span style=\"color: #ff0000\">runat<\/span>=&quot;server&quot;&gt;<\/p>\n<p><span style=\"color: #0000ff\">Protected Sub Page_Load( _<br \/>\n&nbsp;&nbsp; <\/span><span style=\"color: #0000ff\">ByVal<\/span> sender <span style=\"color: #0000ff\">As<\/span> <span style=\"color: #0000ff\">Object<\/span>, <span style=\"color: #0000ff\">ByVal<\/span> e <span style=\"color: #0000ff\">As<\/span> System.EventArgs)&nbsp;<span style=\"color: #0000ff\">Handles <\/span><span style=\"color: #0000ff\">Me<\/span>.Load<\/p>\n<p>&nbsp;&nbsp; Dim sb As New System.Text.StringBuilder()<br \/>\n&nbsp;&nbsp; Dim p(0) As Microsoft.Reporting.WebForms.ReportParameter<br \/>\n&nbsp;&nbsp; Dim i As Integer = 0<br \/>\n<span style=\"color: #008000\">&nbsp;&nbsp; &#39; If we were truly datadriving this we would validate&nbsp;values by type<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp; &#39; as well as&nbsp;the actual param names<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; For<\/span> <span style=\"color: #0000ff\">Each<\/span> s <span style=\"color: #0000ff\">As<\/span> <span style=\"color: #0000ff\">String<\/span> <span style=\"color: #0000ff\">In<\/span> Request.QueryString.Keys<br \/>\n<span style=\"color: #008000\"><br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; feedback to put in Label:<br \/>\n<\/span>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; sb.Append(s &amp; <span style=\"color: #a31515\">&quot;=&quot;<\/span> &amp; Request.QueryString.Item(s) &amp; <span style=\"color: #a31515\">&quot; &quot;<\/span>)&nbsp;<\/p>\n<p><span style=\"color: #0000ff\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If<\/span> Left(s, 2) = <span style=\"color: #a31515\">&quot;r:&quot;<\/span> <span style=\"color: #0000ff\">Then<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; this querystring name value pair fits our naming convention.<br \/>\n<\/span>&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; i += 1<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; Array.Resize(p, i)<br \/>\n&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p.SetValue(&nbsp;<span style=\"color: #0000ff\">New<\/span> <br \/>\n&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Microsoft.Reporting.WebForms.ReportParameter(s.Substring(2), _<br \/>\n&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Request.QueryString.Item(s).Split(<span style=\"color: #a31515\">&quot;,&quot;<\/span>)), i &#8211; 1)<br \/>\n<span style=\"color: #0000ff\">&nbsp;&nbsp; &nbsp;&nbsp; Else<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp; &#39; Don&#39;t send &#8212; the querystring contains<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; a name value pair that does not belong to us.<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End<\/span> <span style=\"color: #0000ff\">If<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; Next<\/span> <br \/>\n<span style=\"color: #0000ff\"><br \/>\n&nbsp;&nbsp; If<\/span> sb.Length = 0 <span style=\"color: #0000ff\">Then<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; Me<\/span>.Label1.Text = <span style=\"color: #a31515\">&quot;You didn&#39;t send a query string.&quot;<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; Else<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; &nbsp;&nbsp; Me<\/span>.Label1.Text = <span style=\"color: #a31515\">&quot;You sent: &quot;<\/span> &amp; sb.ToString()<br \/>\n<span style=\"color: #0000ff\">&nbsp;&nbsp; &nbsp;&nbsp; If<\/span> i &gt; 0 <span style=\"color: #0000ff\">Then<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; this should have a try-catch around it<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Me<\/span>.ReportViewer1.ServerReport.SetParameters(p)<br \/>\n<span style=\"color: #008000\">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; Your catch could put some feedback into the page,<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; just as I&#39;ve put the user&#39;s choices into<br \/>\n<\/span><span style=\"color: #008000\">&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &#39; the label which is the only other control on this page&#8230;<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; &nbsp;&nbsp; End<\/span> <span style=\"color: #0000ff\">If<br \/>\n<\/span><span style=\"color: #0000ff\">&nbsp;&nbsp; End<\/span> <span style=\"color: #0000ff\">If<br \/>\n<\/span>&nbsp;&nbsp; sb = <span style=\"color: #0000ff\">Nothing<\/span> <br \/>\n<span style=\"color: #0000ff\"><br \/>\nEnd <\/span><span style=\"color: #0000ff\">Sub <\/p>\n<p><\/span><span style=\"color: #0000ff\"><\/span><span style=\"color: #0000ff\">&lt;\/<span style=\"color: #a31515\">script<\/span>&gt; <\/span>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>FWIW I dearly love URL Access in SQL Server Reporting Services.&nbsp; Well, to be exact, I love REST interfaces in general.&nbsp;(I don&#39;t&nbsp;know why MS couldn&#39;t use the perfectly good name already available for this feature.)&nbsp; I didn&#39;t discuss this in much detail in my last post on dynamic use of ReportViewer controls, although it contains<a class=\"more-link\" href=\"https:\/\/spacefold.com\/lisa\/2007\/09\/20\/another-bit-for-dynamic-reportviewer-control-handling-parameters-in-more-depth\/\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-175","post","type-post","status-publish","format-standard","hentry","category-asp-net","category-reporting"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/175","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/comments?post=175"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/175\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=175"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=175"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=175"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}