\nNo matter how much we love to see our designed report brimming with data like a cornocopia, there are times when it's a little too much of a good thing.\n<\/p>\n
\nLinda<\/a> writes with the following question (using SQL 2005 and RDLs):\n<\/p>\n \n\t1. I have a report that all parameters have a default so the report runs when it is opened before you click View Report. <\/p>\n \t2. I don't want the report to run because it is pulling way too much data. I want the user to fill in one of the five parameters to limit the data (I don't care which one). <\/p>\n \t3. I wrote custom code that checks to see if all parameters are nothing and returns a boolean. <\/p>\n \t4. I use the Hidden property, with an expression that calls the custom code to either show a textbox saying "Please fill in one parameter" or the table that is the actual report. <\/p>\n \t5. The problem is, that even when my table (actual report) is hidden (because they did not enter a parameter), the dataset is still running (and pulling way too much data). <\/p>\n \t6. How can I get the dataset to NOT run unless they have entered one of the parameters. I have tried several things in my custom code but have not been successfull.\n\t<\/p>\n<\/blockquote>\n \nWell, sure. Hiding a data region in the layout doesn't affect the data-loading. From the report's point of view, hiding a data region doesn't mean that some custom code or some other data region isn't using the data in this same dataset. You have to get "back", closer to the data-pull, and further from the layout, to do this right.\n<\/p>\n \nLinda was really<\/strong> close to getting this right without help, as you may be able to tell from the steps she's taken. She just needs a little boost. And this is a problem that besets us all — so I wanted to get it out there, in at least one version, as quickly as possible. (These days, "as quickly as possible" isn't as quickly as I'd like… apologies to EVERYBODY who has been waiting in the queue.)\n<\/p>\n \nThere are a lot of ways to do this. Some involve dynamic sql for the actual report query, and some don't. I'm going to show some basic ingredients for the ones that don't need a dynamic query, which are surprisingly simple.\n<\/p>\n \nFor this walkthrough I'll use a very boring table with very boring data. \n<\/p>\n \nThe table lists event notices that are triggered in a system.\n<\/p>\n \nIt doesn't matter what's in it; all you have to know, really, is that the Designer preview in VS 2008 throws an "Out of Memory" exception if I run the report with no filters.\n<\/p>\n \nIn this walkthrough I've used "start" and "end" dates for one filter, and a picklist of event notice types for another one. These are just a couple of common filter types.\n<\/p>\n \nBut these two params won't fit every case and every data set. You need to look at each parameter in your<\/strong> report, and decide how to apply what I'm doing in the example.\n<\/p>\n \nThe picklist parameter gets its available valules from a second data set. The entries come mostly from a lookup table, but I've added a UNION statement, like this:\n<\/p>\n SELECT<\/font> Notice_Type,<\/font>2 AS<\/font> Ord FROM<\/font> S2S_NoticeTypes \nFor the picklist's default value, I"ve set the value 'Please select'. \n<\/p>\n \nSo far, I've set a "known impossible" value as the default value. I now use this "impossible" default value in the actual SQL query, like this:\n<\/p>\n \nSELECT<\/font> *<\/font> FROM<\/font> S2S_Notices \nNotice the all-important parens. This is a simple trick, but one I use a lot, to say "evaluate this parameter only if it has a real value". \n<\/p>\n \nMeanwhile, you can see that the date parameters in the query are<\/strong> always evaluated. What gives?\n<\/p>\n \nThe date parameter defaults are set a little differently:\n<\/p>\n \n=DateAdd("d"<\/font>,1,Today())\n<\/p>\n \n=DateAdd("d"<\/font>,2,Today())\n<\/p>\n \nThese are still "impossible" defaults for this particular table, because I know for sure the event notice queue has no future dates in it. \n<\/p>\n \nLook: Dates are particularly tricky as parameters in Reporting Services, especially when created as empties. I don't even know if Linda has any dates in her parameter set, but I wanted to use at least one example. She may<\/strong> be using nulls or empties for date defaults, and we could easily handle this on the SQL end if necessary; it's on the RS end that it's tricky. Read on. I think you'll see how it will work.\n<\/p>\n \nYou notice I used Today() instead of Now() above? I didn't want times in this case, you might in yours. There are a ton of other silly date hang-ups.\n<\/p>\n \nMaybe in Linda's case, a different strategy than "impossible" defaults shown to the user would be better. \n<\/p>\n \nFor example, it sounds to me like she's using explicit nulls as defaults in at least one parameter's case, as an indication that this parameter is optional. \n<\/p>\n \nShe can use default nulls, if she wants, for all the parameters! Or, she can mix and match with "Select a value" style defaults for some, and null defaults for others.\n<\/p>\n \nWhen all defaults are provided but all parameters are actually optional<\/strong>, as in Linda's case, I usually end up with a query that has a particularly weird-looking WHERE clause. Let's adapt my example to show exactly this case.\n<\/p>\n \nNotice the first part of the WHERE clause here, which will come into play if none of the parameters are chosen:\n<\/p>\n \nSELECT<\/font> *<\/font> FROM<\/font> S2S_Notices \nUpdate (10\/7\/2009) after talking with Linda:<\/p>\n The above query, written in haste with the second part (after the OR) copied verbatim from the earlier example, is probably really<\/strong> confusing, and I'm sorry about that.<\/p>\n The second part doesn't match the first part (on the top half we're using a null test for @NoticeType, and on the bottom half we're testing for 'Please select', same way we did it in the earlier example.<\/p>\n What's more, I left out an all-important test on the second part, to ensure that at least one parameter had been edited by the user. <\/p>\n A more appropriate example would look something like this:\n<\/p>\n \n SELECT<\/font> *<\/font> FROM<\/font> S2S_Notices \n… or the two @NoticeType <\/strong>IS NULL<\/strong> <\/font> clauses above could both be changed to <\/font>@NoticeType =<\/font> 'Please select'<\/font><\/strong>, instead. \n<\/p>\n \nOh, definitely.\n<\/p>\n \nThere's a good chance Linda will just take the ball up and run with it if this is enough to get her started. \n<\/p>\n \nShe's probably also see that she can make her life a whole lot easier if she passes this stuff to a sproc. Then she can basically say SELECT TOP (@TopN) PERCENT where she evaluates @TopN to be 0 in certain conditions and 100 in others. \n<\/p>\n \nYou do remember that MS SQL 2005 and above have variable Top (N) syntax<\/a>, right? It works with the Percent keyword.\n<\/p>\n \nSo, now comes the fun part: showing this in the RDL itself. Instead of Linda's conditionally-hidden textbox, I decided to use a textbox with this expression:\n<\/p>\n \n=IIF(CountRows("dsMain"<\/font>)= 0, \n… no particular reason, I just thought it would look nice. Of course the "false" part of the IIF could have been an empty string, too. In Linda's case, if she's using nulls for defaults, she might want to say "Please edit at least one filter value" or something like that.\n<\/p>\n \nI could also have chosen to use the NoRowsMessage property of the Table layout control, either instead of or alongside the specialized message textbox. This property is pretty convenient (I think all the data region controls have it), although not as flexible in the formatting department.\n<\/p>\n \nObviously, if I'm talking about using NoRowsMessage property of the Table layout control, I didn't choose to hide the table when there wasn't any data. I thought the headers looked nice.\n<\/p>\n \nYou can definitely hide the table if you want to — whether on the basis of the parameters values or CountRows("dataset scope") is up to you. Just remember that there may be times that the parameters are edited by the user but CountRows() is still 0. Do you want to show the table, or not?\n<\/p>\n \nAnd also remember that, as Linda found, hiding something in the layout is just not the end of the story if you're looking to optimize the report.\n<\/p>\n \nThis <\/em> is the end of the story. Or at least the end of my walkthrough. It's a bit rushed (Linda's apparently on a deadline), so I might have missed something important or not been explicit enough somewhere.\n<\/p>\n \nThat's okay. In my experience, you guys know how to find me <g>.<\/p>\n","protected":false},"excerpt":{"rendered":" No matter how much we love to see our designed report brimming with data like a cornocopia, there are times when it's a little too much of a good thing. Linda writes with the following question (using SQL 2005 and RDLs): 1. I have a report that all parameters have a default so the reportRead 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":[5,6],"tags":[],"class_list":["post-95","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/95","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=95"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/95\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=95"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=95"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=95"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}\n
1. Setup: Not important<\/h3>\n
2. Consider the needs of each individual parameter<\/h3>\n
3. To dream the impossible dream<\/h3>\n
\nUNION<\/font>
\nSELECT 'Please select'<\/font> AS<\/font> Notice_Type,<\/font> 1 AS<\/font> Ord
\nORDER BY Ord,<\/font> Notice_Type <\/font><\/p>\n
\nWHERE<\/font>
\n Notice_Requested ><\/font> @NoticeRequestedAfter AND<\/font>
\n Notice_Requested <<\/font> @NoticeRequestedUntil AND<\/font>
\n (<\/font>@NoticeType =<\/font> 'Please select'<\/font> OR<\/font> Notice_Type =<\/font> @NoticeType)<\/font> <\/span>\n<\/p>\n4. Pick your defaults wisely!<\/h4>\n
\nWHERE<\/font>
\n(<\/font>@NoticeRequestedAfter IS<\/font> NULL<\/font> AND<\/font>
\n @NoticeRequestedUntil IS<\/font> NULL <\/font>AND<\/font> — just add one "impossible" condition <\/font>
\n @NoticeType IS<\/font> NULL<\/font> AND<\/font> Notice_Type =<\/font> 'XXXX' <\/font><\/span> — at the end of the all-null params
\n)<\/font><\/font> <\/font>
\n OR<\/strong><\/font> <\/strong> — your real WHERE clause is here, whatever it happens to be:<\/font>
\n( ( @NoticeRequestedAfter<\/font> IS NULL OR <\/font>Notice_Requested ><\/font> @NoticeRequestedAfter) AND<\/font>
\n ( @NoticeRequestedUntil<\/font> IS NULL OR<\/font> Notice_Requested <<\/font> @NoticeRequestedUntil) AND<\/font>
\n ( <\/font>@NoticeType =<\/font> 'Please select'<\/font> OR<\/font> Notice_Type =<\/font> @NoticeType)<\/font>
\n)<\/font>\n<\/p>\n
\n WHERE<\/font>
\n (<\/font>@NoticeRequestedAfter IS<\/font> NULL<\/font> AND<\/font>
\n @NoticeRequestedUntil IS<\/font> NULL <\/font>AND<\/font> — just add one "impossible" condition <\/font>
\n @NoticeType IS<\/font> NULL<\/font> AND<\/font> Notice_Type =<\/font> 'XXXX' <\/font><\/span> — at the end of the all-null params
\n )<\/font><\/font> <\/font>
\n OR<\/strong><\/font> <\/strong> — your real WHERE clause is here, whatever it happens to be:<\/font>
\n ( (@NoticeRequestedAfter<\/font> IS NOT NULL <\/font>OR
\n <\/font> @NoticeRequestedUntil<\/font> IS NOT NULL OR
\n <\/font> @NoticeType <\/font>IS <\/font>NOT NULL )<\/font> <\/font>AND<\/font> <\/font><\/font><\/font><\/font><\/span>
\n ( @NoticeRequestedAfter<\/font> IS NULL OR <\/font>Notice_Requested ><\/font> @NoticeRequestedAfter)<\/font> AND<\/font>
\n ( @NoticeRequestedUntil<\/font> IS NULL OR<\/font> Notice_Requested <<\/font> @NoticeRequestedUntil)<\/font> AND<\/font>
\n ( <\/font>@NoticeType IS NULL<\/font><\/font> <\/font><\/font>OR<\/font> Notice_Type =<\/font> @NoticeType)<\/font>
\n )<\/font>\n<\/p>\n5. Can it be easier?<\/h3>\n
6. The "reveal"<\/h3>\n
\n "Please edit the filter values to return data"<\/font>,
\n "You have selected "<\/font> & CountRows("dsMain"<\/font>).ToString()
\n ) & " rows."<\/font>\n<\/p>\n7. Take your bows<\/h3>\n