Monday, 19 March 2018

SSRS: Dynamic Parameters and Subject


Sample:

update EmailSubscription
set Parameters = '<ParameterValues><ParameterValue><Name>ReportDate</Name><Field>ReportDate</Field></ParameterValue><ParameterValue><Name>ReportType</Name><Field>ReportType</Field></ParameterValue></ParameterValues>',
DataSettingParameters = '<Field><Alias>ReportDate</Alias><Name>ReportDate</Name></Field><Field><Alias>ReportType</Alias><Name>ReportType</Name></Field>',
CommandTextParameters = ',ReportDate = CONVERT(VARCHAR, CAST(dateadd (day, -1,GETDATE()) AS DATE), 121),ReportType = case EOMONTH(dateadd (day, -1, getdate())) when dateadd (day, -1, getdate()) then 3 else 1 end',
SubscriptionName = 'KPIFinancialReportDailyAndMonthlyPDF',
Subject = '[case EOMONTH(dateadd (day, -1, getdate())) when dateadd (day, -1, getdate()) then ''Monthly Financial KPI Report'' else ''Daily Financial KPI Report '' end + CONVERT(VARCHAR, CAST(dateadd (day, -1,GETDATE()) AS DATE), 121)]'
where EmailSubscriptionID = 409

No comments:

Post a Comment