passing an expression for a datetime from standard subscription

db042190 1,516 Reputation points
2022-10-06T12:07:33.333+00:00

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?

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,827 questions
{count} votes

3 answers

Sort by: Most helpful
  1. AniyaTang-MSFT 12,331 Reputation points Microsoft Vendor
    2022-10-07T05:36:44.013+00:00

    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.
    248326-1.png
    Best regards,
    Aniya

    0 comments No comments

  2. db042190 1,516 Reputation points
    2022-10-07T11:30:38.963+00:00

    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.

    0 comments No comments

  3. AniyaTang-MSFT 12,331 Reputation points Microsoft Vendor
    2022-10-10T05:36:14.977+00:00

    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.
    248884-1.png
    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()),"")  
    

    248809-2.png
    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".
    248885-3.png
    Hope this can help you.
    Best regards,
    Aniya

    0 comments No comments