Error when setting a data driven subscription: ….report or shared dataset has user profile dependencies and cannot be run unattended. (rsHasUserProfileDependencies). Most of the report I've created utilize the User!UserID global user object in a query to return user-specific data used for permission.
This is currently the way the data subscription is designed as it needs to run unattended and this warning should provide a go signal to innovate a way to full-fill the requirements.
Solution to make it work:
· Create a simple report containing only a textbox to use as a link. Set a URL action on this textbox to open the other report. You can place a default value, using the full path to the report on the server or in my way I have not default any value and drive the parameter value using the Master Data Services. I have utilized the free Master Data Services from SQL 2008 R2 to maintain my data driven parameters. This is when you have several recipients have different set of parameters value.
· Schedule a subscription for this small report and use the option to embed it into the email.
When the user receives the subscription email, they will just see a link, which as actually a report drill-through action that they will use to open the second report. Since that report doesn’t use stored credentials, it will use their own credentials to authenticate and the security matrix in your report will still work.
Sample Data driven Subscription to be set and tested in Sharepoint
Query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient.
WITH [cteSubscription]
AS
(
SELECT DISTINCT CONVERT(VARCHAR(100), ru.[Email]) AS [UserEmail]
,CONVERT(VARCHAR(2000), rs.[Parameter2]) AS [Grouping]
,CONVERT(VARCHAR(MAX), rs.[Parameter4]) + rs.[Parameter2] + '%5d' + rs.[Parameter5] + CONVERT(CHAR(8), DATEADD(DD, -1, GETDATE()), 112) + '%5d' AS [ReportURL]
,CONVERT(INT, CONVERT(CHAR(8), DATEADD(DD, -1, GETDATE()), 112)) AS [DateKey]
,CONVERT(VARCHAR(2000), rs.[Parameter1] + ' for ' + CONVERT(VARCHAR(2000), rs.[Parameter3]) + ' ' + CONVERT(VARCHAR(11), DATEADD(DD, -1, GETDATE()), 113) + ' was executed at ' + CONVERT(VARCHAR(20), GETDATE(), 113)) AS [Subject]
FROM DBINSTANCE.mdmDB.mdm.ReportsSubscription rs
INNER JOIN DBINSTANCE.mdmDB.mdm.ReportsSubcriptionReceipients ru
ON rs.ReportUser_Code = ru.Code
WHERE rs.[Report_Code] = 'SampleCode0001'
)
SELECT REPLACE(REPLACE((SELECT REPLACE([UserEmail], ' ', '%20') AS 'data()'
FROM [cteSubscription] c2
WHERE c2.[Division] = c1.[Grouping]
FOR XML PATH('')), ' ', ';'), '%20', ' ') AS [UserEmail]
,[Division]
,[ReportURL]
,[DateKey]
,[Subject]
FROM [cteSubscription] c1
GROUP BY [Grouping]
,[ReportURL]
,[DateKey]
,[Subject]
This is currently the way the data subscription is designed as it needs to run unattended and this warning should provide a go signal to innovate a way to full-fill the requirements.
Solution to make it work:
· Create a simple report containing only a textbox to use as a link. Set a URL action on this textbox to open the other report. You can place a default value, using the full path to the report on the server or in my way I have not default any value and drive the parameter value using the Master Data Services. I have utilized the free Master Data Services from SQL 2008 R2 to maintain my data driven parameters. This is when you have several recipients have different set of parameters value.
· Schedule a subscription for this small report and use the option to embed it into the email.
When the user receives the subscription email, they will just see a link, which as actually a report drill-through action that they will use to open the second report. Since that report doesn’t use stored credentials, it will use their own credentials to authenticate and the security matrix in your report will still work.
Sample Data driven Subscription to be set and tested in Sharepoint
Query that returns a list of recipients and optionally returns fields used to vary delivery settings and report parameter values for each recipient.
WITH [cteSubscription]
AS
(
SELECT DISTINCT CONVERT(VARCHAR(100), ru.[Email]) AS [UserEmail]
,CONVERT(VARCHAR(2000), rs.[Parameter2]) AS [Grouping]
,CONVERT(VARCHAR(MAX), rs.[Parameter4]) + rs.[Parameter2] + '%5d' + rs.[Parameter5] + CONVERT(CHAR(8), DATEADD(DD, -1, GETDATE()), 112) + '%5d' AS [ReportURL]
,CONVERT(INT, CONVERT(CHAR(8), DATEADD(DD, -1, GETDATE()), 112)) AS [DateKey]
,CONVERT(VARCHAR(2000), rs.[Parameter1] + ' for ' + CONVERT(VARCHAR(2000), rs.[Parameter3]) + ' ' + CONVERT(VARCHAR(11), DATEADD(DD, -1, GETDATE()), 113) + ' was executed at ' + CONVERT(VARCHAR(20), GETDATE(), 113)) AS [Subject]
FROM DBINSTANCE.mdmDB.mdm.ReportsSubscription rs
INNER JOIN DBINSTANCE.mdmDB.mdm.ReportsSubcriptionReceipients ru
ON rs.ReportUser_Code = ru.Code
WHERE rs.[Report_Code] = 'SampleCode0001'
)
SELECT REPLACE(REPLACE((SELECT REPLACE([UserEmail], ' ', '%20') AS 'data()'
FROM [cteSubscription] c2
WHERE c2.[Division] = c1.[Grouping]
FOR XML PATH('')), ' ', ';'), '%20', ' ') AS [UserEmail]
,[Division]
,[ReportURL]
,[DateKey]
,[Subject]
FROM [cteSubscription] c1
GROUP BY [Grouping]
,[ReportURL]
,[DateKey]
,[Subject]