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.
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.