locked
e-mail subscription + variables RRS feed

  • Question

  • hi,

     i have created a report in ssrs 2008. i have to create subscription for that report.

    but i have to change the subject line in this report. default subject line is "@ReportName was executed @ExecutionDate" , this has to be changed to "@ReportName - MonthName - Year" (this values should be dynamic (according to the system date and time)) .

    here i tried to change the subject line using functions like monthname and year .

    some one help me in fixing this please .

    thanks.

    Monday, July 18, 2011 7:27 AM

Answers

  • Hello Sudeep,

    The only variables you can use in regular subscriptions are the @ReportName and @ExecutionTime. 

    You can create a "data driven subscription" based off a query that returns the dynamic subject line text along with any other settings for the subscription (From, To, CC, etc), and choose the query's field name with the "Get the value from the database" option.

    Please ref, below link to create report specific Data Driven Subscription:-
    http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx#Y1557

    I hope that helps you out.

    Thanks

    Kumar


    KG, MCTS
    • Marked as answer by Elvis Long Tuesday, July 26, 2011 10:37 AM
    Monday, July 18, 2011 9:14 PM
  • Hi sudeep,

    Thanks for your question and _Kumar’s reply. Based on my research and past experiences, there are three suggestions I would like to share with you.

    1. As _Kumar said, there are two variables can be used in the subject: @ReportName and @ExecutionTime. We can stitch these two variables to other strings directly, however, it not allows us using function on them, it means that we can’t get the MonthName or Year from @ExecutionTime by any functions.

    2. Create one Data-driven subscription is a good way, we can specify one column as the report’s subject. It also has one problem: although we can get the MonthName and Year, we can’t get the ReportName from database directly.

    There is one similar thread about data-driven, please refer to: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/02cbb3df-c208-4132-b53c-db05e151a8e7.

    3. The workaround: since the report stored in the database, maybe we can get the ReportName, MonthName and Year from the database indirectly, there is one article about Dynamically name report/file export in SSRS, please reference it: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69546/.

    Hope it helps you, if your issue still exists, please feel free to let me know.


    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Elvis Long Tuesday, July 26, 2011 10:38 AM
    Tuesday, July 19, 2011 11:17 AM

All replies

  • Hello Sudeep,

    The only variables you can use in regular subscriptions are the @ReportName and @ExecutionTime. 

    You can create a "data driven subscription" based off a query that returns the dynamic subject line text along with any other settings for the subscription (From, To, CC, etc), and choose the query's field name with the "Get the value from the database" option.

    Please ref, below link to create report specific Data Driven Subscription:-
    http://msdn.microsoft.com/en-us/library/microsoft.wssux.reportingserviceswebservice.rsmanagementservice2005.reportingservice2005.createdatadrivensubscription.aspx#Y1557

    I hope that helps you out.

    Thanks

    Kumar


    KG, MCTS
    • Marked as answer by Elvis Long Tuesday, July 26, 2011 10:37 AM
    Monday, July 18, 2011 9:14 PM
  • Hi sudeep,

    Thanks for your question and _Kumar’s reply. Based on my research and past experiences, there are three suggestions I would like to share with you.

    1. As _Kumar said, there are two variables can be used in the subject: @ReportName and @ExecutionTime. We can stitch these two variables to other strings directly, however, it not allows us using function on them, it means that we can’t get the MonthName or Year from @ExecutionTime by any functions.

    2. Create one Data-driven subscription is a good way, we can specify one column as the report’s subject. It also has one problem: although we can get the MonthName and Year, we can’t get the ReportName from database directly.

    There is one similar thread about data-driven, please refer to: http://social.technet.microsoft.com/Forums/en-US/sqlreportingservices/thread/02cbb3df-c208-4132-b53c-db05e151a8e7.

    3. The workaround: since the report stored in the database, maybe we can get the ReportName, MonthName and Year from the database indirectly, there is one article about Dynamically name report/file export in SSRS, please reference it: http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69546/.

    Hope it helps you, if your issue still exists, please feel free to let me know.


    Thanks,
    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    • Marked as answer by Elvis Long Tuesday, July 26, 2011 10:38 AM
    Tuesday, July 19, 2011 11:17 AM
  • Sharp, I agree with your solution mentioned in option 2, which is using Data Driven Subscription.

    But I believe Sudeep wants to create the subscription which will be report specific (which I suppose is always the case) and hence the report name will be static. So the report name can be hard coded in the query section (step 1 of data driven subscription) and can be retrieved in later steps.

    For example: Select 'Your report name' as Report_Name

    And this hard coded value would be available in form of fields in later steps.

    Hope I am clear enough and it is useful information.


    Thanks,

    Pulkit Ojha

    Microsoft Business Intelligence Developer

    Tuesday, July 19, 2011 1:01 PM
  • Hi Pulkit,

    Thanks for your reply and helping me understand Sudeep’s requirement clearly.

    Actually, the similar thread in my second option, it is just one sample of using data-driven to create one dynamic subject based on static ReportName, I am apology for didn’t explain it accurately.

    @Sudeep, if your requirement just like I discussed with Pulkit, please refer to below T-SQL query for your subscription.

    select 'ReportName-'+cast(Month(GETDATE())as varchar(10))+'-'cast(year(GETDATE()) as varchar(10)) as subject

     

    Hope it help you.

    @Pulkit, thanks for your reply again.

    If there is anything unclear, please don’t hesitate let me know.

    Sharp Wang


    Please remember to mark the replies as answers if they help you and unmark them if they provide no help.
    Wednesday, July 20, 2011 1:06 AM
  • Everyone is welcome....lets help each other...


    Thanks, Pulkit Ojha Microsoft Business Intelligence Developer
    Wednesday, July 20, 2011 8:37 AM