locked
ssrs subscriptions RRS feed

  • Question

  • I did a report,Subscription Name, Last Run Time, Report Name and Location columns. Please add a parameter to determine what kinds of subscriptions are viewed – Data Driven versus Standard.pls tell me anyone how can i do these
    Friday, January 23, 2015 2:01 PM

Answers

  • Hi MS SQLSERVER,

    Because Data Driven subscription have an relationship table to specify the mail address and the parameter, so in the subscription table, the column "Data Setting" will stored this information.

    I have tested on my local environment that you can query the table Subscriptions in the Report Server DB, if this field is null it will be the standard subscription, if it is not null is will be the Data Driven Subscription.

    So you can add an calculated field using this expression to get the subscription type value:
     =IIF(Fields!DataSettings.Value is nothing,"Standard Subscription","Data Driven Subscription")

    If you still have any problem, please feel free to ask.

    Regards
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Vicky_Liu Thursday, January 29, 2015 9:41 AM
    • Marked as answer by Vicky_Liu Friday, January 30, 2015 1:02 AM
    Monday, January 26, 2015 10:37 AM
  • Hi MS SQLSERVER,

    By adding the calculated field, you can right click the dataset to select the "Add Calculated field" as below:

     Specify an name of the new calculated field and add the expression in the field source as below:

    If you still have any problem, please feel free to ask.

    Regards,
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Marked as answer by Vicky_Liu Friday, January 30, 2015 1:02 AM
    Wednesday, January 28, 2015 1:02 AM

All replies

  • Hi MS SQLSERVER,

    Because Data Driven subscription have an relationship table to specify the mail address and the parameter, so in the subscription table, the column "Data Setting" will stored this information.

    I have tested on my local environment that you can query the table Subscriptions in the Report Server DB, if this field is null it will be the standard subscription, if it is not null is will be the Data Driven Subscription.

    So you can add an calculated field using this expression to get the subscription type value:
     =IIF(Fields!DataSettings.Value is nothing,"Standard Subscription","Data Driven Subscription")

    If you still have any problem, please feel free to ask.

    Regards
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Proposed as answer by Vicky_Liu Thursday, January 29, 2015 9:41 AM
    • Marked as answer by Vicky_Liu Friday, January 30, 2015 1:02 AM
    Monday, January 26, 2015 10:37 AM
  • Hi vicky,

    where i can give this expression?If am giving this expression in parameter iam getting error.Pls tell me clearly

    Tuesday, January 27, 2015 12:59 PM
  • Hi MS SQLSERVER,

    By adding the calculated field, you can right click the dataset to select the "Add Calculated field" as below:

     Specify an name of the new calculated field and add the expression in the field source as below:

    If you still have any problem, please feel free to ask.

    Regards,
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    • Marked as answer by Vicky_Liu Friday, January 30, 2015 1:02 AM
    Wednesday, January 28, 2015 1:02 AM
  • costcenter (string) the parameter for the report

    Cost Center is a group value as parameter in dropdown form

    Ex: query to retrieve the cost center values

     select distinct groupvalue 
      from accountprofilegroup 
      where groupname = 'cost center'
      and accountid = @accountId
      order by groupvalue


    iam using this query for Cost Center parameter but i didnt get any values from the dropdown list.

    Pls suggest me how to solve this

    Wednesday, January 28, 2015 7:26 AM
  • Hi MS SQLSERVER,

    Your issue can be cause by the parameter @accountId add in the where clause. The two parameters are cascading parameters, so the order of the parameter is very important.

    Please make sure you have put  @accountId at first order and @CostCenter as the second order:

    Please make sure you have specify the "Available values" and "Default Values" for the @CostCenter

    If the query execute with no error, may be there is no data based on this condition  in the DB actually.

    Please create an new thread for this question, because it is a different question with the preview one and also help to mark it as answer if your problem resolved.

    Thanks a lot!

    Regards
    Vicky Liu


    Vicky Liu
    TechNet Community Support


    Wednesday, January 28, 2015 7:49 AM
  • actually problem is when am passing costcenter parameter i didnt get results but when i set costcenter value in dataset iam getting results
    Thursday, January 29, 2015 9:38 AM