- \n
- \n\n\t\ta standard landscape-orientation page size for rendered output (such as printing, Excel, PDF)\n\t\t<\/div>\n<\/li>\n
- \n
\n\t\ta standard expectation on users' parts to see the HTML content, hosted in the ReportViewer control, un-paginated, but<\/strong>\n\t\t<\/div>\n<\/li>\n- \n
\n\t\ta rule that the unpaginated content must be forced to paginated output if the dataset has > a certain number of rows, and<\/strong>\n\t\t<\/div>\n<\/li>\n- \n
\n\t\tan inability to extrapolate a probable number of output rows from the user-chosen parameter values. \n\t\t<\/div>\n<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\nThe last condition is really the killer. Presumably, she has a large number of parameters, and they may be quite different for each report. She is not allowed to do a preprocess call to the db-backend from her web tier in order to decide whether the html-rendered content should paginate or not. She won't know until she's actually in the report.\n<\/p>\n
\nSo, previous to talking with me, she had settled on a methodology something like this:\n<\/p>\n
- \n
- \n\n\tUse a parameter called ShowAll for the user to indicate their preference for browser-hosted display \n\t<\/div>\n<\/li>\n
- \n
\n\tSet the Interactive Page dimensions the same way as the export\/standard page dimensions \n\t<\/div>\n<\/li>\n- \n
\n\tCreate two overlaid tablix layout controls, one with "Keep Together" set true and one with the same property set False\n\t<\/div>\n<\/li>\n- \n
\n\tBring the data into the report, and, at that point, evaluate ShowAll and also whether there are too many rows to allow un-paginated display in the browser,\n\t<\/div>\n<\/li>\n- Hide\/show the two tablix controls based on this evaluation.<\/li>\n<\/ul>\n
\nThis technique works, but causes a couple of problems:\n<\/p>\n
- \n
- \n\n\tTwo tablix controls to maintain, for each report, is kind of a PITA.\n\t<\/div>\n<\/li>\n
- \n
\n\tThere are two textbox controls following the table-format content, and these get forced to the next "page", which is not optimal.\n\t<\/div>\n<\/li>\n<\/ul>\n\nWhat Karen would really like to do is set either "Keep Together" or Interactive Page Height dynamically. She knows that the right way to get un-paginated content in the HTML display is to set Interactive Page Height to 0 — and I hope you do too.\n<\/p>\n
\nUnfortunately, neither KeepTogether nor InteractivePageHeight can take an expression. And, remember: she can't adjust the RDL dynamically in this case, because she doesn't know how many rows will end up in the report before actually gathering the data into<\/em> the report.\n<\/p>\n
\nSo… what to do?\n<\/p>\n
Stand the problem on its head.<\/h2>\n
\nSince the right way to get "flowing", un-paginated content in the HTML display is to set Interactive Page Height to 0… let's do that.\n<\/p>\n
\nBut wait. What happens when the user doesn't want ShowAll to be on (the user prefers pagination)? And, more significantly, what happens if the number of rows is greater than the allowed maximum?\n<\/p>\n
\nWe can use dynamic page breaks for that.\n<\/p>\n
\nBut, wait. Won't dynamic page breaks interfere with the correct pagination for standard export content? (Karen's rows aren't all the same height, so we shouldn't try to figure out a particular number of rows per PDF or printed page.) Well… they might. But we can control the export content ourselves and make sure that the export content always behaves as if ShowAll is False, so our explicit page breaks don't interfere with the "natural" ones in each renderer. \n<\/p>\n
\nAnd how are we going to do that? Aye, there's the rub. Karen thinks it's easy to mess around with parameters at exactly the right moment. It turns out, it's not easy at all. That's why this walkthrough has two parts.\n<\/p>\n
\nAt least the textbox problem completely goes away without any work at all. Small comfort.\n<\/p>\n
\nSo, bear with me, and we'll start our walkthrough now.\n<\/p>\n
Mise en place<\/h2>\n
\nKaren's a C# programmer, so I'll use C#. I'm not great at it, don't laugh. I'm sure you'll do better. But I'm going to fully work through this problem, no matter how silly the code I use, because I think you will miss some niceties if I don't.\n<\/p>\n
\nI'll also use the RS SOAP API here, for no good reason except that I rarely do, but I figure Karen will probably be happier with it than REST.\n<\/p>\n
\nMy simple report of course has only one tablix in it, not two, followed by a couple of text boxes with aggregated values to match Karen's scenario. It has four parameters, as follows:\n<\/p>\n
\n\n
\n ShowAll<\/strong><\/td>\n <\/td>\n \n \n\t\t\tThis one is Karen's. You know what it does.\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n
\n RowLimit<\/strong><\/td>\n <\/td>\n \n \n\t\t\tThis one represents all Karen's other parameters. It governs how many rows are returned in my test report.\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n
\n PerPageLimit<\/strong><\/td>\n <\/td>\n \n \n\t\t\tThis one is the largest number of rows that should be displayed on an interactive page, no matter what the value of ShowAll. Karen probably would not display this one.\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n
\n Test<\/strong><\/td>\n <\/td>\n \n \n\t\t\tThis is a multi-valued parameter, which does nothing in the report except make sure my parameter-handling code handles all situations, including error trapping for a missing or nullvalue, and the special object-handling that a parameter list requires.\n\t\t\t<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
\n
![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/1\/DIP1.PNG\")
\n<\/p>\n\nI'll use my usual simple SQLWorld database borrowed from the mySQL community, because the data really isn't the point in this walkthrough. Here's the query I used; you can see how RowLimit stands in for Karen's query requirements when I test:\n<\/p>\n
\n SELECT TOP <\/span>(<\/span>@RowLimit) *<\/span> FROM <\/span>SQLWorld.<\/span>dbo.<\/span>City\n<\/p>\n
Ça marche… <\/h2>\n
\nNow the fun begins. The sample report with the parameters above is simplicity itself. This screen shot is from ReportBuilder 2.0; there is really nothing in this that would be easier to do in Report Designer than the Builder.\n<\/p>\n
\n
![\"\"](\"\/lisa\/wp-non\/migrated\/2010\/1\/DIP2.PNG\")
\n<\/p>\n\nThe only weird thing you'll see in the report — and it isn't really pertinent to Karen's scenario in particular — is that I have replicated the tablix header row in the page header. These two extra textbox controls each have the hidden\/Visibility expression =Globals!PageNumber=1<\/strong>, so they stand in for the tablix header on subsequent pages. \n<\/p>\n
\nWhat are they for? Frankly, sometimes I get tired of trying to figure out how various renderers will "react" to the various layouts and attributes that tell a header whether it should re-appear on additional pages, and when I'm in a hurry I just use this dodge instead of testing all the possibilities.\n<\/p>\n
\nAlso frankly, I tend not to use KeepTogether; I generally don't understand some of the logic behind some of these options. I'm impressed to see that Karen was comfortable with its behavior and is using it successfully. I'm not sure how each renderer will treat it, what small variations in page size will do, and rarely rely on it if I don't have time to test exhaustively. As you'll see, it's not relevant to my version of the solution.\n<\/p>\n
\nThe "regular" page layout size for my solution matches Karen's. We plan not to mess with exported renditions, which follow those dimensions as appropriate to each format, remember? My Interactive Page Width is the same as Karen's too. (Don't be fooled because the layout you see in the layout window is much narrower than a landscape report; as long as your laid out width is narrower<\/em> than your intended result, with a comfortable buffer for margins, you should always be fine. But the Interactive Page Height is 0.\n<\/p>\n
A soupçon of saffron… <\/h2>\n
\nYou also see — and the "tip" textbox in the page header calls your attention to it — that I've added a group to this report. Here's where we control the pagination.\n<\/p>\n
\nI've deleted visible columns from this group, and set it to page break "between each instance of the group". I've used the following group break expressinon:\n<\/p>\n
\n=IIF(Parameters!PerPageLimit.Value =-1 OrElse Parameters!ShowAll.Value,
\n Nothing<\/span>,
\n Code.BreakGroup<\/strong>(RowNumber("Tablix1") Mod Parameters!PerPageLimit.Value =0)<\/span>)\n<\/p>\n\nOkay, let's parse this instruction: if our current number-of-rows per page has not been explicitly limited or the user has indicated a preference for un-paginated content, never break<\/span>. Otherwise, decide when to break based on a simple code function<\/span>, which I've embedded in the report. (It's completely generic, so there's no reason you couldn't put it into an external DLL for use by all your reports if you prefer.) Here's all the code:\n<\/p>\n
\nDim<\/span> MyGroup As Integer<\/span> = 0
\n
\nFunction<\/font> BreakGroup(ByVal TimeToBreak As Boolean) As Integer <\/font>
\n If<\/span> TimeToBreakThen<\/span>
\n MyGroup = MyGroup + 1
\n End<\/span> If<\/span>
\n Return<\/span> MyGroup <\/p>\nEndFunction <\/font>\n<\/p>\n
\nThe simple logic above isn't 100% perfect. For one thing, it's based on a number of rows per page, and we already know that Karen's rows-per-page could vary based on stretching row heights. For another thing, I haven't bothered accounting for the fact that the Mod break expression will probably have one less rows-per-page on the first page than others; you could add Globals!PageNumber.Value into the mix here, and correct as needed, if you want. But it really doesn't matter.\n<\/p>\n
\nThis logic is only there for interactive<\/strong> page breaks in the browsed HTML version and, as such, the exact height of the page isn't important. We just want to limit it. If the limit is, say, 250, but the first page only has 249, or if the height of 250 rows is a couple of pixels more or less on different pages, we don't really care.\n<\/p>\n
\nFor rendered pages in other formats, we're simply going to make sure that the value of PerPageLimit is always -1<\/strong>, so the renderer's natural behavior for the page size will "take over" and control the page breaks.\n<\/p>\n
\nHow are we going to do that? Aha. This is where life gets really interesting.\n<\/p>\n
Define interesting.<\/h2>\n
\nOK, I will<\/a>. But it's going to take a whole 'nuther walkthrough (I write long posts, but this would be ridiculous) and a lot more code. \n<\/p>\n
\nIt's still the right way, and the code is in the right place for Karen to use her own tier (and her type of skills) to take care of business, for all reports at once.<\/p>\n","protected":false},"excerpt":{"rendered":"
It's hard to believe how many questions come my way involving dynamic page breaks and parameters. (Including yours, Philippe. You're next. And yours was 'way easy compared to this one… <g>) Karen Grube came up with an interesting scenario and some specific constraints, requiring some special handling. I'm going to paraphrase a bit, to try toRead 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-90","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\/90","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=90"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/90\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=90"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=90"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=90"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
- \n
- \n
- \n