{"id":71,"date":"2010-12-19T10:58:00","date_gmt":"2010-12-19T10:58:00","guid":{"rendered":"\/lisa\/post\/2010\/12\/19\/Blunted-points.aspx"},"modified":"2010-12-19T10:58:00","modified_gmt":"2010-12-19T10:58:00","slug":"blunted-points","status":"publish","type":"post","link":"https:\/\/spacefold.com\/lisa\/2010\/12\/19\/blunted-points\/","title":{"rendered":"Blunted points"},"content":{"rendered":"<p>I need to add a couple of postscripts to <a title=\"blog post about using Word renderer or XSLT to resolve an Excel renderer issue.\" href=\"\/lisa\/2010\/12\/12\/Everything-has-a-point-Part-Deux\/\">last week&#8217;s post<\/a>.<\/p>\n<p>I was talking about resolving an issue with the SSRS default Excel renderer, which shows up with collapsed groups, by either:<\/p>\n<ul>\n<li>exporting using the Word renderer first and the going into Excel from there,\n<p>or<\/p>\n<\/li>\n<li>(what I prefer) exporting to XML and writing your own Excel with XSLT.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2>Postscript 1<\/h2>\n<p>The actual report has a toggle very much like the one I showed in my example: an inner group needs to collapse and expand.&nbsp; It turns out, believe it or not, that if I move the toggle to the outermost group, the Excel rendition is much better behaved.&nbsp;<\/p>\n<p>You still have the outline behavior but you can sort and filter when you format the table in Excel, despite the outlining.<\/p>\n<p>So, this was kind of interesting, and good news, especially given what I&#8217;m about to tell you below: if I eliminated the outer groups (in the sample report I posted, this would be the group on Continent), and simply treated them as a value column in the toggled group (in the sample report, this would be Country), users can go straight to Excel.&nbsp; We can use &#8220;Hide Duplicates&#8221; and other formatting tricks to give a similar layout result as the outer group was doing for me.<\/p>\n<p>It won&#8217;t always work, for example, you may want totals on that outer group.&nbsp; But it&#8217;s a nice plus that outlining doesn&#8217;t automatically spell analytical doom for SSRS&#8217;s default Excel renderer.&nbsp; Try it yourself, because it&#8217;s a little difficult to believe, but it actually works fine.<\/p>\n<p>&nbsp;<\/p>\n<h2>Postscript 2<\/h2>\n<p>In the actual report, there is a lot of code, accumulating values both across and down, before the subtotals are shown for each toggled group.&nbsp; Never mind why; just take my word for it that these are not &#8220;natural&#8221; aggregates that are easy to do in SQL when you create the dataset, or by invoking a standard SUM() formula expression in the RDL; there&#8217;s just too much business logic determining which items may and may not be included in each total (again, both across and down).<\/p>\n<p>Well, it turns out&#8230; that with this actual report, the XML renderer just doesn&#8217;t cut it.<\/p>\n<p><strong>The subtotals that show for the Details collection, as generated by my code function, are wrong in the XML renderer, although they are right in all the other ones.&nbsp; <\/strong>And the issue has nothing to do with visibility; I can un-hide all the detail rows, remove the toggle, and I still see the same wrong values in the XML.<\/p>\n<p>It also doesn&#8217;t seem to matter <strong>where<\/strong> I do the calculations. and accumulate the arrays in the code.&nbsp; I originally had them in the first textbox that required them, invoking a function that RETURNs &#8220;&#8221; and concatenating this function with the &#8220;real&#8221; expression for the textbox.&nbsp; This is something I often do.<\/p>\n<p>When I saw the problem,&nbsp; I moved the accumulating function to a fake group filter, which always returned true. And I tried group variables, and I tried different positions in the actual text box exprssions.&nbsp; Nothing doing.&nbsp; No matter what I tried, all the other renderers showed the correct values but this function wasn&#8217;t working.&nbsp;<\/p>\n<p>So, in this case, it&#8217;s not a question of sequence of evaluation being different in the XML renderer, it&#8217;s as if there&#8217;s an optimization in the XML renderer that prevents the function from being invoked at all.<\/p>\n<h2>Once more, with feeling:<\/h2>\n<p><strong>If there&#8217;s a renderer required for a particular report to succeed, don&#8217;t forget to test that renderer separately.&nbsp;<\/strong> Usually the differences are limited to formatting (with pagination being the typical, major issue), but occasionally, as you see here, even the actual values may differ.<\/p>\n<p>IMHO, this shouldn&#8217;t be. Physical formatting details between renditions are bound to differ, and I&#8217;ll defend to the death the necessity for this when folks whine about it and expect anything else. But<strong> values should not<\/strong> <strong>differ<\/strong>&#8230; or at least with no explicable reason.<\/p>\n<p>If I can get it down to simple steps to repro, I&#8217;ll post it here.&nbsp; And you know I&#8217;ll post it as a bug with MS.<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I need to add a couple of postscripts to last week&#8217;s post. I was talking about resolving an issue with the SSRS default Excel renderer, which shows up with collapsed groups, by either: exporting using the Word renderer first and the going into Excel from there, or (what I prefer) exporting to XML and writing<a class=\"more-link\" href=\"https:\/\/spacefold.com\/lisa\/2010\/12\/19\/blunted-points\/\">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":[5,6,10],"tags":[],"class_list":["post-71","post","type-post","status-publish","format-standard","hentry","category-reporting","category-sql-server","category-xml-xslt"],"_links":{"self":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/71","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=71"}],"version-history":[{"count":0,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/posts\/71\/revisions"}],"wp:attachment":[{"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/media?parent=71"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/categories?post=71"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/spacefold.com\/lisa\/wp-json\/wp\/v2\/tags?post=71"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}