Conditional display of Subreport

  • Article

My current reporting project includes a requirement for daily / weekly / monthly view of distribution and recurrence of user visits to Microsoft.com. The displayed data will differ based on the date period used, so I decided to use multiple subreports within the main report, showing a different one based on the date period.

 

My initial idea here was to use an expression for the report name, something along the lines of:

 

="Recurrence" & Parameters!Period.Value

 

Unfortunately, Subreport/ReportName only supports literal strings, so I can't use an expression here.

 

The other option I had considered was including all the subreports into the parent, and toggling visibility based on the date period value, as above. My concern with this approach was that all the subreports would execute even if they would never be displayed. One of the fine devs on the SQL Reporting Services team was kind enough to inform me that "If subreports are hidden and cannot be made visible via a toggle item, they are not executed." This is great news, and will allow me to do exactly what I want -- render the parent report (which will be the same across period) and *only* the appropriate subreport.

 

One trick to setting the appropriate expression for this, if doing so in the Report Deisgner UI: the value entered via the Visibility tab on the Subreport Properties dialog ends up as content of the Subreport/Visibility/Hidden element. If you want the element to display, you need to make sure your expression evaluates to false. This tripped me up when doing it through the UI the first few times; once I looked at the underlying RDL I quickly figured out the problem.

 

In the UI, the expression for the RecurrenceDaily subreport looks like this:

 

=Parameters!Period.Value <> "Day"

 

Which translates to the following in RDL (non-essential elements elided for brevity):

 

<Subreport Name="subrptDaily">
<ReportName>RecurrenceDaily</ReportName>
<Visibility>
<Hidden>=Parameters!Period.Value &lt;&gt; "Day"</Hidden>
</Visibility>
</Subreport>

 

Again, I hope this proves useful to someone out there.

 

cheers,

~stuart