Reporting Services: Overriding a default parameter with an expression in a linked report

Question

So I've got a "daily dashboard" report in SSRS 2005. It has a parameter, @pDate, which defaults to "=Now".

I'd like to use this same report in a linked report to show yesterday's final dashboard (which would then be mailed out via subscription), and override the parameter default with another expression, "=dateadd(d,-1,Now)." But when I change the default parameter, I get a data mismatch error (natch).

I'm assuming that's the end of the line and I just need to deploy a copy of the daily dashboard report with a default @pDate of yesterday, but I thought I'd post here and see if anybody had some nifty shortcut to accomplish this without having to maintain two RDLs.

UPDATE: I found this on MSDN:

If Default Value accepts a value, you can type a constant or syntax that is valid for the data processing extension used with the report.

"Data processing extension" apparently translates to SQL server, so I tried valid singleton selects that weren't constants (GETDATE(), "04/27/"+YEAR(GETDATE()), etc...) and still nothing.

Answer 1

I haven't tested this, but you could try adding a second parameter to the report (without a default - call it pDateOverride for the sake of argument), and then change the default value for pDate to be an expression using the new value if it's present, otherwise Now(). Something like:

=Iif(IsEmpty(Parameters!pDateOverride.Value),Now(),Parameters!pDateOverride.Value)

You can then set pDateOverride when calling the report to generate yesterday's final dashboard.

Answer 2

So I found out my issue: if you have subreports within your main report that are using passthrough parameters from the main report, make sure that all of your parameters in your subreports are using the same data type. Two of my subreports were using a String datatype for the @pDate field instead of DateTime, so the subscription was failing (although for some reason the live version of the report tolerates this inconsistency.)

I am now using a data-driven subscription to set @pDate dynamically and everything seems to be working fine.