passing an expression for a datetime from standard subscription

Question

hi we run 2014 enterprise. We will be on 2019 in the coming months.

We have a report that has 4 datetime parameters whose default will be null, a drop down select that for now i'll call TYPE and a sort parameter that defaults to a certain value.

our change mgt (cm) group is charged with running a subscription on this report each morning that renders the report to PDF and distributes via email to about 20 recipients.

as i recall there are 2 different subscription types in the world. More of a vanilla one (standard) and a data driven one.

if cm decides to go with the more vanilla type, can one of the 4 dates be passed as expression that is today - 30 days?

if not and for some reason they are hooked on using standard instead of data driven, and they say we have to make that one date's default today - 30 (so subscription can run with default) but uncheck it in rm so user experiences it as defaulting to null, might a work around be that we create a hidden param called "who is running me" and make the default of the date dependent on that?

Answer 1

Hi @db042190
Sorry I don't quite understand your meaning. Do you want to set one of the date parameters to "today - 30 days" in the report, or do you want to set one of the parameter dates to "today - 30 days" during the standard subscription process? The former problem is easy to solve, just set the value of the parameter to the expression "=DateAdd(DateInterval.day, -30, today())". In the standard subscription, the parameter settings seem to only have the option to use a default value or an input value.

Best regards,
Aniya

Answer 2

Thx Aniya. The default when manually run has to be null. The value used when from subscription has to be "today - 30 days".

here is what i did. I added a hidden param to the report called pWhoIsCallingMe. Available values are M (this is for running manually) and S (for from subscription). M is the default. S is passed by the subscription. Rather than putting the logic in ssrs, I passed this new param to the proc. And changed it to recognize that one of the from dates is today - 30 if that param is S.

Answer 3

Hi @db042190
OK, I see what you mean. I simply did a test.
First I created a hidden parameter "call" with the available values set to "M" and "S" as you meant.

Next a date parameter "date" is created whose default value depends on the parameter "call". I used the IIF function, when the value of "call" is "S", the date of "date" is set to "today-30 days", otherwise it is empty.

=iif(Parameters!call.Value="S",DateAdd(DateInterval.day, -30, today()),"")  

After deploying the report, create a standard subscription. Then we can see that when we select the hidden parameter "call" as "S", the value of "date" is automatically set to "today-30 days".

Hope this can help you.
Best regards,
Aniya