I got one requirement wherein I need to set a data subscription to one my report. This is an easy piece for me but thing just got complicated when I found strange error when I the last section of setting-up the data subscription for my report. Error: ….report or shared dataset has user profile dependencies and cannot be run unattended. (rsHasUserProfileDependencies). I realized my report 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.
One solution I’ve done which I got an idea from one blog then added little bit of pieces to make it work for my requirement.
- 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 Mater 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.
Sample script below for data subscription. I have placed this code for my reference but if you have some question with regards to the solution above please send inquiry.
WITH [cteSubscription]
AS
(
SELECT DISTINCT CONVERT(VARCHAR(100), ru.[Email]) AS [UserEmail]
,CONVERT(VARCHAR(100), rs.[ReportUser_Code]) AS [ReportUser]
,CONVERT(VARCHAR(2000), rs.[Parameter2]) AS [Division]
,CONVERT(VARCHAR(MAX), rs.[Parameter4]) + rs.[Parameter2] + '%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.[Parameter2]) + ' ' + CONVERT(VARCHAR(11), DATEADD(DD, -1, GETDATE()), 113) + ' was executed at ' + CONVERT(VARCHAR(20), GETDATE(), 113)) AS [Subject]
,CONVERT(INT, rs.[Parameter3]) AS [KPI]
FROM ServerName.mdmDB.mdm.ReportsSubscription rs
INNER JOIN ServerName.mdmDB.mdm.ReportsSubcriptionR ru
ON rs.ReportUser_Code = ru.Code
WHERE rs.[Report_Code] = 'ReportName value in MDM Entity'
)
SELECT REPLACE(REPLACE((SELECT REPLACE([UserEmail], ' ', '%20') AS 'data()'
FROM [cteSubscription] c2
WHERE c2.[Division] = c1.[Division]
FOR XML PATH('')), ' ', ';'), '%20', ' ') AS [UserEmail]
,REPLACE(REPLACE((SELECT REPLACE([ReportUser], ' ', '%20') AS 'data()'
FROM [cteSubscription] c2
WHERE c2.[Division] = c1.[Division]
FOR XML PATH('')), ' ', ';'), '%20', ' ') AS [ReportUser]
,[Division]
,[ReportURL]
,[DateKey]
,[Subject]
,[KPI]
FROM [cteSubscription] c1
GROUP BY [Division]
,[ReportURL]
,[DateKey]
,[Subject]
,[KPI]